Income statement simulation model

 

ABSTRACT

 

By using “Sensitivity Analysis” in Microsoft Excel, we will be able to analyze an algorithm with a maximum of two independent variables (two-way data table). The purpose of this project is to present an analytic model made by Microsoft Excel where this model allows us to analyze an algorithm with up to four independent variables. An example of an “Income Statement Simulation Model” to obtain a maximum of 10 scenarios has been presented in which this model shows us how the independent variables of Sales growth rate, COGS growth rate, Operating expenses growth rate, and Interest growth rate should be combined to reach a target net income based on the data of previous year.

Introduction

 As you know, there are three important financial statements to show the financial performance of a company in a specific period of the time which are as follows:

- Income Statement

- Balance sheet

- Cash flow statement

 An income statement which sometimes is referred to as a “profit and loss statement (P&L)” is included some independent variables such as revenue, Cost of Goods Sold, expenses, gains, and losses where an algorithm is establishing a logical relationship among them to reach a net income. This document can help to business owners make decision about the company’s strategies how the combination of increase or decrease on the growth rate of these independent variables will lead the company to earn more profit.

Income Statement Simulation Model

 Suppose we need to know that if we want to reach a growth rate of ”X” on previous year of net income our company in the next year, how the independent variables of “Sales growth rate”, “Cost of Goods Sold growth rate” , “Operating expenses growth rate” and “Interest growth rate” will move. Definitely the speed of this analysis is very important because every time we have to change and compare these variables with the conditions of macroeconomic indicators such as Inflation, GDP, Interest, Unemployment, CPI, and so on.

Basically, we will determine a target net income in accordance with a growth rate of ”X” for the next year and  we will examine many scenarios combined with above four independent variables to reach our target net income. This Income Statement Simulation model can help us to increase the acceleration of our analysis with the least error to find out the best optimized solutions.

 How does this model work?

 First of all, we need to import four levels of the data, such as the INPUTS, into the cells of this model as follows:

1. The data from the income statement of your company related to the previous year. 

2. To enter the range for all four independent variables (Min & Max)

3. To choose a minimum for the error

4. To determine the growth rate for the target net income

 Now, we have a maximum of 10 scenarios (OUTPUTS) for a combination of four independent variables, including “sales growth rate”, “Cost of Goods Sold growth rate”, “Operating expenses growth rate” and “, Interest growth rate”, where the impact of these variables will be approximately equal to our target net income and also the OUTPUTS are included amounts of minimum, average, and maximum for these valuables. For comparing the average results of this model and defined target net income based on the percentage of error, we are referred to the cells of “A16:B18”.

Please see the below video:

When we start our analysis with this model, for 10 combinations of these variables, we will also obtain 10 scenarios for income statement as follows:

Please see the below video:

If you have any questions, please do not hesitate to send them to my email address: Soleimani_gh@hotmail.com

WhatsApp: +98 9109250225