Budgets
By analysing her spending habits, MT Pockets has taken her first steps in securing her financial future. Our first task is to assist MT to prepare a budget.
A budget is a plan for managing your income and expenditure wisely. When designing a budget, it is important to identify all your expenses and to set aside money for them.
MT Pockets has a net salary of $58 000. She has identified the following as her major expenses for last year. This information will assist you in creating a weekly budget for MT.
Expense / AmountRent / $295 per week
Groceries / $105 per week
Entertainment / $98 per week
Lunches and minor expenses / $77 per week
Fares / $64.52 per week
Clothes and other purchases / $97 per week
Home telephone / $49.95 per month
Mobile phone / $59.99 per month
Pay TV and internet / $149.95 per month
Gas and electricity / $216.75 per quarter
Superannuation / $4655 per year
Home contents insurance / $485.90 per year
Private health insurance / $552.50 per year
Medical expenses / $744.15 per year
Car registration and insurance / $2650 per year
Petrol and other running costs / $1790.95 per year
Gym membership / $799.50 per year
Gifts and donations / $1500 per year
Go to budgets.xls (Excel 80kB)
MT Pockets’ weekly budget: Questions
The function of a budget is to monitor expenses. An effective budget will ensure that there is sufficient income to cover expenses and it will cater for future expenses. Complete column 'B' of MT Pockets' budget using the information given above then answer the questions below.
1. Using the information given above, complete column ‘B’ of the spreadsheet.
a. What was MT Pockets’ weekly take-home pay?
b. What was the total amount that MT spent per week?
2. Based on MT Pockets’ spending habits last year, how much would she have been able to save each week?
3. What was MT Pockets’ biggest expense for the year? Calculate the percentage of MT’s take-home pay that this represents. Give your answer to one decimal place.
4. In preparing her budget for the next twelve months, MT Pockets allows for increases in the cost of goods and services and assumes an ‘across the board’ increase of 2.5%. To complete the ‘Allocation per week’ column follow these steps:
· In cell E7, enter the formula =D7*1.025
· Click on cell E7 and hold the mouse button down.
· Drag the mouse pointer down until the cells E7 to E24 are highlighted.
· Go to the Edit menu and click on the Fill Down command.
[If you have opened this spreadsheet with Excel 2007, follow these steps:
· In cell E7, enter the formula =D7*1.025
· Click on cell E7 then drag the fill handle (in the bottom-right of the cell) down until the cells E7 to E24 are highlighted.]
What is the total weekly amount allocated for expenses in MT’s budget?
5. How much would MT Pockets be able to save each week? Calculate how many weeks it would take for MT to save for an overseas holiday costing $5000.
6. MT Pockets’ landlord informs her that the rent on her home will increase by 10%. Click on cell E7 and change the formula to account for this increase. What would be the overall effect of this increase on MT’s budget?
7. The ‘consumer price index’ is a measure of the average annual increase in the price of goods and services. Go to the Reserve Bank of Australia website www.rba.gov.au and investigate how an ‘across the board’ increase of 2.5% compares with the present rate of inflation. Why do many financial institutions recommend that when preparing a budget, you should calculate all of your expenses then add 10%?
8. By following the steps in question (4), change the spreadsheet so that the weekly allocation includes an ‘across the board’ increase of 10% for all expenses. What is the overall effect on MT Pockets’ budget?
9. Calculate what MT Pockets’ annual net salary would have to increase to for her budget to absorb the ‘across the board’ increase in expenditure described in question (8) without changing her spending habits.
10. Assuming that MT Pockets’ annual salary does not change, by how much would she have to decrease her weekly spending to absorb the increase described in question (8) and save $20 per week? Give an example of how MT might achieve this. Show your calculations.
MT Pockets’ weekly budget: Answers
1. a) $1115.38 b) $1066.59
2. MT would have been able to save $48.79 per week.
3. Rent ($295.00 per week). Rent represents of MT’s take-home pay.
4. Total weekly expenses =$1093.26
5. MT’s weekly savings would be$22.13. It would take MT 5000÷22.13@226 weeks to save for her overseas holiday. (Of course, this assumes that MT does not invest her weekly savings in an account that earns her interest. In ‘Investing money’ we will investigate ways to maximise your savings).
6. The rental increase would result in a ‘balanced budget’ with zero savings. In other words, the total income would be equal to the total expenditure.
7. Accounting for an ‘across the board’ increase of 10% would be a safer strategy. Any unforseen expenses would be more easily absorbed by a safety net of 10%.
8. A 10% increase in expenditure would put MT Pockets’ budget ‘into the red’. In other words, her expenditure would exceed her income by $57.87 per week.
9. MT Pockets’ annual net salary would have to rise to a minimum of $58000+52x$57.87=$61009.24
10. MT Pockets would need to reduce her weekly spending by $77.87. This would require MT to assess her lifestyle and to decide what items she could reduce her spending on, or do without completely. She could achieve this by committing to a reduction in her ‘variable expenses’, such as: groceries; entertainment; lunches and minor expenses; clothes and other purchases; and gifts and donations. If MT was reluctant to sacrifice her lifestyle she could also ‘shop around’ for a cheaper deal on any or all of her ‘fixed expenses’, including such items as: mobile phone, pay TV and internet plans; insurance and health cover costs; and gym membership. A simple solution might be to reduce the amount of her superannuation contributions, though many people would believe that it makes good financial sense to keep-pace with the rate of inflation when considering how much to contribute to superannuation.
Your budget: Questions
Before you can advise others about their finances, we must first make sure that your own finances are in order. Create your budget, my young apprentice, by first answering the questions below. Go to the ‘Your budget’ spreadsheet.
1. In the 'Income' column, enter how you earned money last week and in the 'Amount last week' column enter the amount.
2. Study the formulae in cells C7, D7 and E7. In cell B7, enter a formula to calculate your total income last week. What was the total of your earnings for last week?
3. In the 'Expenses' column, list everything that you spent money on in the last week and in the 'Amount last week' column, enter the amount.
4. Click on cell D10. Explain how the spreadsheet calculates values that appear in this cell. Why would the formula ‘=C10*4’ be inappropriate?
5. Go to cell E10. Enter a formula to calculate the amount allocated per year for this expense. Highlight the cells E10 to E25, then go to the ‘Edit’ menu and click on the ‘Fill Down’ command. [If you have opened this spreadsheet with Excel 2007, click on the cell E10 then drag the fill handle down until the cells E10 to E25 are highlighted.]
6. Prepare your weekly budget by first considering your income and expenses from last week then allocating your funds accordingly. Enter these amounts in the ‘Allocation per week’ column.
7. How much would you like to save each week? Adjust your expenditure to achieve your savings goal.
8. In cell E29, write a formula to calculate the total amount saved in one year.
9. - Highlight the cells listing your expenses then, holding down the ‘Ctrl’ key, highlight the corresponding cells in the ‘Allocation per year’ column.
- Go to the ‘Insert’ menu and click on ‘Chart’. Choose a Pie chart then click ‘Next’ then ‘Next’ again.
- In Step 3, click on the ‘Titles’ tab and in ‘Chart title’ enter ‘Weekly Budget’. Go to the ‘Data labels’ tab then choose ‘Percentage’.
- Click ‘Next’ then ‘Finish’.
[If you have opened this spreadsheet with Excel 2007, follow these steps:
- Highlight the cells listing your expenses then, holding down the ‘Ctrl’ key, highlight the corresponding cells in the ‘Allocation per year’ column.
- Go to the ‘Insert’ tab and in ‘Charts’ click on ‘Pie’ then 2D ‘Pie’.
- Click ‘Chart Layouts’ then select ‘Layout 1’.
- On your graph, double click on ‘Chart Title’ then enter ‘Weekly Budget’.]
What percentage of your total expenses is your largest expense?
10. Suppose an unforseen expense arises which takes 10% of your income. Adjust your budget to account for the extra expense.
Your budget: Answers
2. =SUM(B4:B6)
4. The cell calculates the monthly amount by first changing the weekly amount to a yearly amount (by multiplying by 52) and then dividing by 12. The formula would not be appropriate since there are more than 4 weeks in a month. (Expect of course in February in a non-leap-year).
5. =D10*12
8. =E7-E26