EXERCISE: Budget vs Actual vs Forecast Example 1

Please purchase the course before starting the lesson.

Question

Open the Budget vs Forecast vs Actual v1 Step 1 file and do the following:

1.     You will notice that now we have a sheet with the budget, a sheet with a revised forecast and a sheet with 2 months’ worth of actuals. We want to create a COMBINED sheet that shows the latest actual information and then includes the revised forecast so that we can see what the whole year will look like.

2.     On the Income Statement Mthly COMBINED sheet do the following:

a.     In I3 row, create a formula using IF or CHOOSE that checks whether there are numbers in the Income Statement Mthly Actual (use the turnover in row 11 as the sample data) and says ACTUAL if there is and FORECAST if there isn’t.

b.     In the rows starting with I11, I12, create a formula that either pulls through the Actual numbers from the appropriate sheet of the forecast numbers depending on whether that month has an actual or not.

c.     In V11 and V12, create a SUM of the total for the year.

3.     You will notice that we have built a balance sheet and cash flow statement already that links to the combined income statement.

To see the full course contents click here.

Back to: Online Financial Modelling, Budgeting and Forecasting Course > Comparing Budgets and Forecasts to Actuals