Making a smart list

Make a Simple Smart List First.

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. Back there was called Visicalc – short for Visual calculator. Today it comes in various forms but the most common version is Microsoft Excel. Apple has a free one that comes on every Mac called “Numbers.” They are both similar but the Apple one is somewhat limited if you are doing fancy mathematical things.  If you are doing simple things, both are easy to use.

Start Here 

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 4000. Again I’ve never used that many rows.  The first square on the top left is called cell A1. Beside it, on the right is B1, then C1 etc. The cells below A1 is A2, then A3, A4 etc as you go down the sheet. You need to know what they are called because you will use them in calculations (more of that later).  Each cell can contain text, numbers or formulas for calculations.

Add The Title.

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 of it.

Add The Items.

If you move to A3 (down 2 squares to give us a space after the heading) you can then put the items in your list. Typing a return after you’ve finished writing shifts the cursor down one cell. It assumes you will add  anew item. Below my list of groceries for this week. Type this list and the numbers (except B10) into your blank spreadsheet so you can do the assignment below.  If you put the Dollar sign before the numbers it automatically changes that cell to be a currency format so everything you put into that cell after that point in time will have a dollar sign.  If you forgot the $ sign, hold your mouse over the top of the row (where it says B) and you get a down arrow.  Then, by clicking on that cell, Excel will select the whole column for you. Next you go to the menu right at the top 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, 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 without wearing out your fingers by typing the $ sign on each cell.

Adding a Calculation.

Next move to the cell at the bottom of the numbers. In my example it is cell B10. Then type all of these characters…  =Sum(B3:B9) This tells the spreadsheet to add all the numbers between cell B3 and cell B9 and put the result into B10. 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. If you used the word AVG in place of the word Sum in the formula above, it will give you the average of all the items. See if you can work out what the average is.

Assignment 1

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. 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. You hover your cursor over the dividing line between the two columns at the top where the letters are and then dragging the column divider to the size you need it to be.

Extending your list.

Now this simple example may not be world changing, but you can extend it and make it useful. I use exactly this system to produce a budget for myself.  I put headings down the left of items I expect to spend money on in the next 12 months. Then I put the months across the top.  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. 

The document consists of a list of income sources and a list of things I spend money on each month. Using your expense numbers for each month from last year, put in all the amounts you spent. Include things like 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 Totals

The column totals give me an overview of each month. Positive numbers mean I can save – negative numbers are highlighted in red and they mean I will overspend. Some months in my budget planner have big payments like rates and insurance and sometimes both in the same month. In that month I will spend more than my income. But armed with that knowledge, I can make sure I don’t spend my “excess” cash in the months before. So I should 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 finding where you can get a job!

Make it Work For You

I hope this is helpful for you – especially if you are entering retirement or worried about your finances. Just make sure you spend time making it as accurate as possible and include all income and expenses. Things like interest on bank accounts and pension amounts can easily get overlooked. Try not to miss anything or guess expenses. Thinks like car repairs will always be a guess because there is always something else to add – like wiper blades and fluids. Even included service costs won’t cover these items. At least at the end you should see which months you will be behind and which months you will be ahead. Hopefully there will be enough where you are ahead to pay for the ones in which you are behind.

Share this:

Leave a Reply

Your email address will not be published.