Have you ever had the need for a list of something but needed more than you could get if you wrote it down on paper? Like a shopping list that adds up all the items? Well there is a very versatile computer program that enables you to have a list that adds up the cost of all the items (or averages them or any number of other alternatives). That software is called a spreadsheet program. It was invented in the days of the Apple II computer and back there was called Visicalc – short for Visual calculator. Today it comes in various forms but the most versatile and common version is Microsoft Excel. Apple has a free one that comes on every Mac called “Numbers.” They are both similar if you are doing simple things and both are easy to use.
When you open a new document (Excel Workbook) you are presented with a page of blank rows and columns. The columns are labeled A – Z then AA- ZZ or something. I’ve never used that many columns! The rows are labeled 1 – something over 2000. Again I’ve never used that many rows. So the first square on the top left is called cell A1. Beside it, on the right is B1 and below A1 is A2. You need to know what they are called because you will use them for calculations (more of that later). Each cell can contain text, numbers or calculations.
So if you were to make a shopping list, you might put the title “Shopping List” in A1. You can change the font to Bold and the size to suit yourself and make that cell stand out. Don’t worry if the words overfill the cell because if the next cell is blank it will simply appear over the top.
If you move to A3 (down 2 squares) you can then put the items in your list. A return character at the end of what you are writing will shift the cursor down one cell to enable you to add the next item. Below my list of groceries for this week. Copy this list and the numbers (except B10) to your blank spreadsheet so you can do the assignment below. You can see that I’ve also put the price for each item in column B. When you first add numbers to this column they appear as simple numbers but by holding your mouse over the top of the row (where it says B) you will get a down arrow then by clicking on that cell, Excel will select the whole column for you. Next you go to the menu where it says Format then select Format Cells and click on that. That gives you a window of different cell types and if you choose currency from the list, all the numbers will change, by magic, to have a Dollar sign and two decimal points. So you end up with a list of items and their costs.
Next move to the cell at the bottom of the numbers. In my example it is cell B10. If you then type =Sum(B3:B9), the spreadsheet will add all the numbers between cell B3 and cell B9. The equals sign tells the spreadsheet this is a formula. The word “Sum” means find the total and the parts in brackets are the cells to add together. In my case the cost of groceries this week will be $50.10.
So here is your assignment. In Australia we have a 10% goods and services tax on everything that’s not fresh food. By law all prices must include the tax so how much is the GST component of the list, given this rate of taxation? Use your spreadsheet column C to work it out and here’s a clue – you need to divide taxable items price by 11. The answer, by the way, is not $4.55 ($50.10/11) but I won’t tell you why – you’ll have to work that out.
You can change the width of any columns if you need to by hovering your cursor over the dividing line between the two columns then dragging the column divider to the size you need it to be.
Extending your list.
Now this simple example may not be world changin,g but you can extend it and make it useful. I use exactly this system to produce a budget for myself. I have attached a spreadsheet with blanks cells for income and expenditure items in it – you can download it here. The document is in Excel format but Numbers will open it, if you have an Apple device. If you are using an iPad or iPhone in Safari, there is a message that appear at the right hand, top of this document when you open it, that says, “Open in Numbers”. Press that message to see the spreadsheet document and ignore any font warnings.
It consists of a list of income sources and a list of things I spend money on each month. Using the numbers from the last year, put in all the amounts you spent on car servicing, rates, insurance, food, utilities etc. The spreadsheet add up the amounts in each section of the column and give you a total for each column (month) at the bottom. So you can see where you spend your money and where your income comes from.
The column totals give me an overview of each month so positive numbers mean I can save – negative numbers are highlighted in red and they mean I’ll overspend. Some months in my budget planner have big payments like rates and insurance and sometimes both in the same month, so I spend more than my income for that month. But armed with that knowledge, I can make sure I don’t spend my “excess” cash in the months before and so have enough money left to cover the shortfall.
Each row is also totalled to give me an overview of each category I spend money on or receive money from. So I can see in a year, how much I will spend on rates, or Utilities or whatever. This column is also totalled and so when you have a red number in the total of the totals column, you need to figure out how are you going to make ends meet by cutting down on a category of expenses or maybe where you can get a job!