Project Four - A Budget spreadsheet

OK, let’s put all our arithmetical operator knowledge together and construct a spreadsheet.

The spreadsheet you’re going to construct is a Budget spreadsheet. Here’s the scenario.

There is a small town called Evercrease. Last year the council had a budget of half a million pounds to spend. They overspent by 69 thousand pounds, which the citizens of Evercrease objected to most strongly. So strongly in fact that they got rid of the entire council. Now they need to appoint somebody else to manage the budget. They have asked you to do the job. Have a look at last year’s budget:

Budget Spreadsheet Image (opens in a new window - 165K).

The final budget figure is in cell B33 and reads minus £69 000. Your job is to make a new budget spreadsheet, ensuring that you do not have a minus figure in cell B33.

Your first job is to recreate the budget spreadsheet exactly as it is in the picture. Once you have a spreadsheet exactly like the one in the image, along with the formulas to get the same figures, you can start to enter your own numbers. For example, the budget shows that there are 5 teachers. The teachers are paid 16 thousand pounds a year. These 5 cost 80 thousand pounds a year to keep. Perhaps you don’t need 5? Change the number to 4. When you change it to 4, the 80 thousand total should adjust. That’s because you will have entered a formula in the total column.

So once all your formulas are place, you can adjust the figures in the Number column, column D in the spreadsheet. What you change the numbers to is entirely your decision. After all, you are in charge of the budget. It’s your job on the line!

But there are some things you can try. These are:

You can put the combination formulas in cells B37 and B38 (New Final Budget and New Monthly Spending). Your combination formulas will be the ones with brackets. Something like =A1 - (A2 + A3) or =(A1 + A2) / 12.

Concentrate first on reproducing the same spreadsheet as in the image, and getting the same totals as that one. When your spreadsheet is the same as that one, then you can play around with the numbers.

There is a lot of work to do in the spreadsheet, but it will give you good practice entering text and formulas on a large spreadsheet.

Remember, though: the people of Evercrease are looking for a budget that is less than half a million! If your budget is over that figure, they’ll fire you!

Good luck.

NOTE: If you would like a version of the budget with all the formulas already entered, click here: The Budget as an Excel Spreadsheet.

<-- Back One Page Move on to the Next Part -->

<--Back to the Excel Contents Page

View all our Home Study Computer Courses