Exel Help
ITBFDR
excel_frans.xlsxInstructions
| Instructions | |
| 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. |
Data
| 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 | |||||||||
353.
Stationary Telephone Printing Travel Office Sundry Training Refreshments 1349.18 1150 230 353 201.8 0 35.799999999999997 February Stationary Telephone Printing Travel Office Sundry Training Refreshments 245.67 1034.9000000000001 1584 458.9 1024 0 506.8 March Stationary Telephone Printing Travel Office Sundry Training Refreshments 135.80000000000001 1237 1623.3 568 268.7 6700 381.54 April Stationary Telephone Printing Travel Office Sundry Training Refreshments 3050 853.2 135 226 352 0 145.9 May Stationary Telephone Printing Travel Office Sundry Training Refreshments 854.3 954.7 1350 648 411 6300 532 June Stationary Telephone Printing Travel Office Sundry Training Refreshments 62.9 638.54 1693.2 587.29999999999995 824 0 600 Stationary Telephone Printing Travel Office Sundry Training Refreshments 11697.85 13068.34 11415.5 14841.199999999999 5581.5 25000 5202.0400000000009 Stationary Telephone Printing Travel Office Sundry Training Refreshments 6697.85 5868.34 6615.5 2841.1 3081.5 13000 2202.04Overspending in Training
