In forecasting it is often necessary to modify one or more assumptions and see the effect of change on the financial results of the company. Scenario Analysis tool enables the user to create different scenarios of Profit & Loss Statement, Balance Sheet and Cash Flow statement within the model.
Let’s assume that our initial forecast of Corporation Tax Rate for the year starting on April 1st,2017 is 20%:
Corresponding Profit After Tax shown in P&L is the following:
During forecasting exercise user may want to see what the profit for the year will be if the Corporation Tax Rate is 15% instead of 20%.
For this purpose, it is possible to extract current output reports into Scenario 1. Afterwards, change the corporate tax rate to 15% for the year starting on April 1st, 2017 & export the results into Scenario 2. (For instructions on how to manage corporation tax rates, please review Section 6.3: Corporation tax)
In order to extract current outputs into a scenario, user must go to Reports Budgets > Create Scenario:
As a result, three additional worksheets are added to the model (P&L_Scn_1, BS_Scn_1 & CF_Scn_1).
They show the financial results of the company under the assumption that corporation tax rate for the current year is 20%.
For creating Scenario 2, we change applicable tax rate to 15% for the current year from Taxation > Corporation Tax and extract the resulting financial reports into Scenario 2:
Scenario 2 shows £586,294 Profit After Tax vs. £551,806 in Scenario 1. The increase is due to the reduction in corporate tax rate assumptions from 20% to 15%.
It is possible to create multiple scenarios by changing multiple types of assumptions (income, expenses, fixed asset purchases & disposals, tax rates, etc.) in the model.
The resulting scenarios can be exported into a separate excel workbook as described in Section 8.1: Export Reports.