Financial Statement Model of Winside Aerospace, Inc.

This page describes the Financial Statements demo model. It shows how you can use Valsight to model future income statement positions, cash flows and changes of the balance sheet with drivers. The presentations are close to IFRS standards. The baseline logic of financial statement linkage is reusable for any company. Following few paragraphs describe the backstory of an imaginary airplane manufacturer, Winside Aerospace, Inc., the basic structure of the model, the most interesting formulas, and the associated workspace. Lastly, different possible uses cases and expansions are described.

Background 

Our example is based on an aerospace manufacturing corporation, Winside, which manufactures three different types of products: Airplanes (Airliners), Helicopters and Private jets priced in two currencies: Euros and US Dollar with production based in Europe. The aircraft manufacturer market is very special. Their order books are full for years to come, the clients are expected to pay advance payments. This allows our fictitious Winside Aerospace to keep very thin equity at 13% of total assets which can quickly lead to solvency problems. Few other financials can be found in the following table.

KPI (Units & EUR) Baseline Value

Total Aircraft sold (Units)

1632
Revenue 61B
Gross Margin 7B
Net Income -1B
Total Assets

100B

Equity 13B
Advance Payments 37B

Structure

The baseline linkage logic of the financial statements shown below remains the same across the use cases (Fig 1.)

Financial Statements Model

Fig 1. Overview of the Financial Statement model

Our goal is to simulate monthly development for the years  2019-2021. Our imports come from 2018 and the production goals of 2022 have an effect on Working Capital in 2021. Therefore all our models include the time period from 2018 until 2022.  The Starting BS / Assumptions  model holds also key assumptions for the operations like quantity, baseline prices or reinvestment to depreciation ratio or general drivers like the market or salary development. In the next step, these assumptions and drivers are used to calculate the income statement (20) positions with net income as the main result. The operations simulated in income statement have an effect on the balance sheet positions (30); e.g inventories lead the increase in sales, the net income results in a change of retained earnings. To calculate the cash flow statement (40) using the indirect method, only the net income and balance sheet are required:

Net Cash Flow =  Net Income - Accruals =  Net Income - △Assets(w/o cash) + △Liabilities 

In the final step (50), the net cash flow is used to calculate the cash account. For completeness and control purposes, the Final Balance Sheet also copies all account results of the Balance Sheet Calculation. Valsight does not allow for creating circular references between models. This hinders adding the net cash flow back to Balance Sheet Calculation. Another variant to avoid the duplicates would be to create the resulting balance sheet preview straightaway in the workspaceFor simplification purposes, the other statements like a statement of comprehensive income and a statement of changes in equity were not modelled.

Solving calculations challenges

Balance Sheet Account Aggregation

Remember that the model includes monthly level data with additional dimensions for product and currency. When aggregating to the quarterly data to the quarter or year level, we need the last end-of-the-month balance sheet and sum over the product and currency dimension. Setting aggregation type of all balance sheet account nodes to Closing Date - Sum configures the nodes accordingly (Fig. 2).

Fig 2. Setting the aggregation type for the balance sheet accounts 
Incorporating Exchange Rates

Winside is a global company with customers all over the world and prices in both dollars and euros. However, the production costs occur in euros. This model assumes that cheaper dollar decreases the revenue in euros, however, the production costs remain the same. To be able to simulate the exchange rate risk, the model has a currency dimension for all revenue-associated nodes. The resulting euro revenue is calculated by multiplying the foreign revenue by the exchange rate node (Fig. 3). Note that just the multiplication does not remove the currency dimension, we need to add the DROPLEVEL function.  This setting would work perfectly without change after adding further currencies or other dimensions.

Fig. 3 Foreign Revenue, note that unit price is in both EUR and USD
Time dimension specification: Data Import, Rolling Forward and Filtering

The starting Balance Sheet on the 31/12/2018 and the production and cost data for the January 2019. This makes the transition between the data import and simulation period clean. With such setting and MOM_ABS (month-over-month changes) function, only one line is needed to calculate cash flows (Fig. 4).

Fig. 4 Calculation of BS Account Changes for CF

The main tool for the transition between past data and simulation is the ROLLFORWARD function. Unfortunately, time shifting (Fig. 5) schemes lead to uncalled-for results in the year 2018 at some of the nodes. To avoid this, we FILTER the calculation results for year 2018 out. For example, to find the depreciation amount, the current value of Plant, Property, and Equipment (PPE) is calculated by rolling forward the 2018 data following the investment trend ('PPE Value increase') and multiplying it with the depreciation rate (Fig 3).   

Fig. 5 Using ROLLFORWARD and FILTER to calculate depreciation, change of sign is included to indicate costs
Working Capital Development

The payables, inventories, and receivables account balances change with the size of the operation. We make the following assumptions. The supplier bills us three months before the sale of the final product. We pay the invoice together with the sale of the final product. The products stay on average two months in our inventory before the sale. Lastly, the customers pay us on average two months after the sales. Such schemes can be easily calculated by SHIFTing the cost of sales or revenues for a given number of months. The Fig. 6 shows the calculation of receivables. 

Fig. 6 Receivables calculation
Advance Payments: Putting it all together

A large portion of Winside's operations is financed by the advance payments of its customers lowering the interest expense. The development of advance payments is therefore crucial for its profitability and cash flows. After importing the starting balance, we model the development as 

World Wide Orders * Market Share * Unit Price * (Order Value / Advance Payment Ratio).

The explicit driver of total orders is the market growth, the explicit driver of unit price are the exchange rates (Fig. 7). 

Fig. 7 Advance Payments calculation

Financial Statement Simulation 

The associated Workspace features two different scenarios, presentation of all three main statements as well as additional analysis including break down of scenario differences according to cash flow and net income, cash account development.

Baseline scenario describes a stagnation on all fronts, the Technology Leadership scenario tries to simulate the development and sales of a new airliner. With quick development of a new generation airliner, Winside Aerospace expects to be able to charge higher prices thanks to lower fuel consumption, easier maintenance, and extra seats. The projected additional orders thanks to higher market share result in new advance payments. Also, 10% increase in units produced should be possible thanks to the digitization of the production. To achieve this optimistic scenario, Winside needs to increase the research and development spending, invest in digitization of production facilities and increase wages to attract top talent and retain current employees. Winside also cannot reap the benefits straight away thanks to the one year development time left for the airplane.

At the income statement tab, we see that in 2019, both scenarios result in red numbers, but the additional expenditures in the Technology Leadership scenario causes additional 3B of losses. In the following two years, the new sales kick in. The 3B and 7,5B of extra net income bring Winside safely into black numbers. The Cash Flow Tab shows that the development of cash flows is more dramatic. This is because in addition to the extra costs mentioned, a large facility investment leads to cash outflow. However, a large inflow of cash is generated by new advance payments after the airplane hits the market.  The Balance Sheet Tab accumulates the impact of these changes. At the end of 2021, the Technology leadership brings a higher cash level, more retained earnings, higher advance payments. Also, thanks to larger operations, inventories, payables and receivables accounts rise. 

Please feel free to explore additional analysis in other tabs. You can switch from quarterly to yearly presentation, drill down by different products or create new charts.  

Using and Expanding the model

The financial statement model can be used to track the impact of decisions and events on the highest level. It helps to highlight liquidity and solvency risks and drive to organization towards the fulfillment of its strategic goals. To some extent, the model can be used as a tool for managing company commitment to its share and stakeholders. There are many possible extension of the model. Firstly, seasonality can be added to revenue, costs and cash flows with yearly payments of taxes, Christmas salary bonuses, and others. The TIMELAG function is perfect for such tasks. The costs of capital would increase accuracy. The financing operations with repayment of debt, issuing new bonds or raising new capital giving certain threshold is also possible.

You can now go ahead  and simulate your financial statements. If you need more information or you are not sure about some functionality, don't hesitate to contact us. 


Was this helpful?

Yes | Somewhat | No