Example 1
Question
Open the Budget and Forecast v1 Step 1 and do the following:
1. On the Income Statement Mthly Div 1 sheet do the following:
a. In the row from I4, create a header that goes from 1 to 44 in increments of 1
b. In the row from I5, create a header that identifies the first 12 months as belonging to period 1 , next to period 2 etc. It should have twelve 1’s, then twelve 2’s etc.
c. Create a month end date that increases each month from I6 but always has the last day of the month.
d. In J46 and the rest of the row, increase the sales by the 2% in cell F46 every month
e. In the I47 row, determine the cost of sales based on the percentage in F47. These numbers should be shown as a negative.
f. In F50 (input cell) use Data Validation to only allow a list of 0,1,2,3 as an option to avoid decimals and slow cash collections being chosen.
g. For I50 row create a formula that calculates the cash collections based on the collections mentioned in F50. The function to consider is the OFFSET function but there are others that you can use.
h. In rows 55 to 58 you are going to create a ‘bucket’ to determine the debtors balance.
i. In I56 row link to the sales amount in I46
ii. In I57 row link to the cash collections amount in I50. Remember that you need to change the sign to be a negative as a collection reduces the debtors balance
iii. In I58 add up the amounts to give you the debtors closing balance
iv. In J55 row link to the previous months closing debtors
i. In the I70 row calculate the annualised Cost of Sales number OF THE FOLLOWING MONTH (Cost of sales in the J47 row multiplied by 12) to base the inventory holding on. You will need to change the sign on the cost of sales in this case
j. In I63 row to I66 row, calculate the various inventory based on the number of days shown in rows 74 to 77.
k. If I97 row have a look at the formula (you don’t need to do anything with it). It is one of the more complex formula using OFFSET which allows you to model depreciation in one line. We cover this in more detail in the longer courses available on www.AuditExcel.co.za .
l. Go back up to row 11 and link up the income statement by
i. Link row 11 to sales in row 46
ii. Link Cost of sales in row 12 to row 47
iii. In row 16 create a formula that calculates the transport costs as a percentage of turnover in row 11. Remember to get the signs correct (it should be negative)
iv. In row 19 link the cells that for the first twelve months the cost is constant per cell I19 and after that it goes up with inflation as per row 38.
v. Link the depreciation in row 26 to row 99
m. Now that the first division is done, create a second division by making an exact copy of the sheet and placing it between the BEG>>> and <<
n. You will now have a new division but it still has the old inputs in from the previous division.
o. Delete the cells in I46 to M46 and make cell N46 equal to 5000 (this division is small and only starting in mid-year). Colour these cells light blue as they are now input cells.
p. Change the growth % in F46 to be 5% and the Cost of Sales % in F47 to be 65%. Make the following changes as well:
i. I15 becomes -500
ii. E16 becomes 5%
iii. E17 becomes 1%
iv. E18 becomes 1%
v. I20 becomes -250
q. Now we need to create an aggregation page. For consistency, create an exact copy of one of the sheets and place it to the right of the <<
r. Now in the new sheet, use the SUMMING THROUGH SHEETS method to aggregate all sheets from BEG>>> to <<
s. You discover that a new division needs to be included. Right click on any of the current sheets and click UNHIDE and you will find the new division. Place it between the BEG and END sheets to update your Income Statement. Check that your aggregation sheet changes. Play with the divisions by moving them in and out of the defined area and see how they affect the aggregation.
t. You can delete the unnecessary cells e.g. E16 etc. as these don’t apply to the aggregation sheet.
To see the full course contents click here.