EXERCISE: Budget and Forecast Example 1 Step 2

Please sign up for the course before starting the lesson.

Example 1

Question

Open the Budget and Forecast v1 Step 2 and do the following:

1.     Note that we now have three divisions set up with an aggregation sheet set up for you. We also have the skeleton of a balance sheet and cash flow statement.

2.     We have realized that there is a slight problem with the divisional spreadsheets as we wanted to have rows 28 and 30 (I28 till end and I30 till end) coloured in another colour to indicate to the user that these are purposefully left blank. Using the techniques taught in changing all sheets, change these areas to another colour in one go.

3.     You are also worried that people may overwrite the inflation factor in E38. Change all the divisional sheets at the same time so that this cell will be protected when you eventually put protection on (don’t need to protect sheet yet, just correct the format of the cells).

4.     Have you remembered to switch off the Grouping feature?

5.     Link the balance sheet and cash flow statement to the aggregation sheet by

a.     On the Balance Sheet Mthly sheet

i.    Link the I12 row to the Cash Flow Mthly sheet I35 row to show the bank balance

ii.    Link the I22 row to income Statement Mthly TOTAL cell I58 to pull through debtors

iii.    In I46 row build a check to see that the balance sheet balances (compare the sum of TOTAL ASSETS versus the sum of TOTAL LIABILITIES and EQUITY). Don’t worry if it doesn’t balance now, as you still have items to complete.

b.     On the Cash Flow Mthly sheet

i.    Determine the working capital movement by referring to the balance sheet. For example in I23 you would refer to Balance sheet H23 minus Balance sheet I23.

c.     Go back and check if the Balance  Sheet is balancing (row 46 on the Balance Sheet Mnthly sheet)

d.     On the Annual Comparison sheet, create a formula that adds up year 1, 2 and 3’s monthly numbers so that we can compare the annual budget versus actuals.

i.    In F11 row, use a SUMIF and the headers in F8 row to pull through the information from the Income Statement Mthly TOTAL.

ii.    In A11, A13, A24, A27, A29 and A31, use Sparklines if you have Excel 2010 or higher to create a mini graph of relevant row so that we can see the likelihood of the budget being met.

6.     There is a new division (division 4). Include it in the appropriate area and check that the aggregations incorporate this and that the balance sheet still balances.

To see the full course contents click here.

Back to: Online Financial Modelling, Budgeting and Forecasting Course > Summary example to this point