Peter is a carpenter. He makes
kitchen cabinets and also does general building work. He is planning
to introduce a new product – oak gazebos which are like permanent
wooden tents people can have in their garden.
1. Entering months
drag the little black box at the corner of the January
cell across and the months will just appear
2. Add a formula in cells G5 to G7 which will show the profit
=B5E5 will work for Oak gazebo profit. That formula
can be copied down with the same little black box for the other
two.
3. Add the number of jobs he expects to get each month from
this table.
Just type in the right cells
4. Enter a formula to show the total number of jobs each month
=SUM(B11:B13) will add the right figures for January.
Again, copy across for other months
5. Enter a formula to enter the income for January ..
You need to make sure the formula always uses the same Price for
Oak gazebos. So use =$B$5*B11 (with those $ signs).
That can then be copied across. It’s 0 for some months.
=$B$6*B12 will work for kitchens
=$B$7*B13 will work for building
6. Show the total income figures in row 20, the sum of the
three figures above
=SUM(B16:B19) will add the income for January and
can be copied across
7. In the Expenditure section, the cost of the Oak timber will
be the number of Oak gazebo jobs x the Oak gazebo cost. Similarly,
you can enter formulae to show Kitchen cabinet materials and General
building costs.
=$E$5*B11 (with the dollar signs) is needed for
January and can then be copied
=$E$6*B12 for kitchens
=$E$7*B13 for building
8. Wages are for someone to help with the building jobs. He
pays £100 per building job
=B13*100 will work and can be copied
9. The other expenses, drawings to sundry expenses, can all
be assumed to be the same each month. Use either a formula or other
method to copy these across.
10. Show the total expenditure figures in row 38.
Another Sum formula =SUM(B23:B37) will work and
can be copied
11. The monthly profit will be his total income – total
expenditure
=B20B38 will work and can be copied
12. The bank balance for January is entered already. From February
it will be January’s balance + February’s profit. That
formula can be copied for the other months.
Put =B40 in the January balance
column.
=B42+C40 will then work for February
and can be copied across
13. Create a chart that shows Peter’s total jobs each
month
Highlight from A9 to M9 (the months row) then hold
the Ctrl key and highlight A14 to M14
Then try Insert>Chart and choose a column
type
14. Create a chart that shows his profit or loss for each month.
Change the colours displayed so that the months with a loss stand
out (eg profit in green, loss in red)
again, start with A9 to M9, hold Ctrl and highlight
A40 to M40
Then Insert >Chart and choose a column
type
To change any bar colour, click any bar once and then click again
so just one is selected
You can use the Fill or Shading
button on the home toolbar to pick a colour. Repeat for others to
be changed.
15. Create a chart that shows how his bank balance will change
over the year.
Select A9 to M9, hold Ctrl and
then select A42 to M42
Insert>Chart and select a line
type
16. Create three charts which indicate the cost and profit
components of each type of job, expressing each as a percentage.
Select A5, E5 and G5 using the
Ctr method to skip other cells.
Insert>Chart and choose a pie
chart
17. Copy the spreadsheet and display the formulae used.
To copy the sheet, point to its tab with the mouse and then hold
the Ctrl key down while you drag the tab to the right, release the
mouse, then the Ctrl key. That should make a second copy with (1)
after the tab name.
To show formulae use Ctr+` (the key to left of 1 on most keyboards).
Don’t worry if the sheet looks odd!
