The data in the table on the data sheet contains the operational expenditure and the budget for a small department. Use the data set on the Data-sheet and do the following:
(1) Format the title by using the Font and Alignment groups on the ribbon.
(2) Change the column widths so that all the data is visisble.
(3) Change the heading of Row 9 to Office Sundry.
(4) Calculate the total expenditure (in column I) for each of the categories.
(5) Calculate the balance left/overspent in column J.
(6) Repeat (4) and (5) for all the categories.
(7) Change the formatting of the amounts so that two decimal digits show.
(8) Calculate the total budget in row 11.
(9) Use this formula to calculate the total expenditure for each month.
(10) Enhance the spreadsheet by adding borders.
(11) Use 'Conditional formatting' to indicate overspending.
(12) Insert a staked bar chart to indicate the monthly expenditure for each of the categories.
(13) Place the legend at the bottom of the graph.
(14) Add data labels.
(15) Do not display the decimals in the data labels
(16) Remove those data labels where the amount is 0.
(17) Add a chart title.
(18) Insert a callout to highlight the overspending in Training.
(19) Change the colour of the callout to Red.
(20) Copy the callout to Printing.
Submit your completed assignment on eFundi.
Department A- Operational Costs
| |
Budget |
January |
February |
March |
April |
May |
June |
|
|
| Stationary |
6000.00 |
1349.18 |
245.67 |
135.80 |
3050.00 |
854.30 |
62.90 |
11697.85 |
6697.85 |
| Telephone |
7200.00 |
1150.00 |
1034.90 |
1237.00 |
853.20 |
954.70 |
638.54 |
13068.34 |
5868.34 |
| Printing |
4800.00 |
230.00 |
1584.00 |
1623.30 |
135.00 |
1350.00 |
1693.20 |
11415.50 |
6615.50 |
| Travel |
12000.00 |
353.00 |
458.90 |
568.00 |
226.00 |
648.00 |
587.30 |
14841.20 |
2841.10 |
| Office Sundry |
2500.00 |
201.80 |
1024.00 |
268.70 |
352.00 |
411.00 |
824.00 |
5581.50 |
3081.50 |
| Training |
12000.00 |
0.00 |
0.00 |
6700.00 |
0.00 |
6300.00 |
0.00 |
25000.00 |
13000.00 |
| Refreshments |
3000.00 |
35.80 |
506.80 |
381.54 |
145.90 |
532.00 |
600.00 |
5202.04 |
2202.04 |
| |
47500.00 |
|
|
|
|
|
|
|
|
OPERATIONAL COSTS DATA TABLE