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 in the balance sheet with drivers. The presentations are close to IFRS standards. The baseline logic of financial statement linkage is reusable for any company. The 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 use 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 Dollars 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. A few other financials can be found in the following table.

Structure

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

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 the income statement have an effect on the balance sheet positions (30); e.g inventories lead to an increase in sales and the net income results in a change in 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 the Balance Sheet Calculation. Another variant to avoid duplicates would be to create the resulting balance sheet preview straightaway in the workspace. For simplification purposes, the other statements like a statement of comprehensive income and a statement of changes in equity were not modeled.

Solving calculations challenges

Balance Sheet Account Aggregation

Remember that the model includes monthly level data with additional dimensions for product and currency. When aggregating 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 dimensions. Setting the 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 a 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 31/12/2018 and the production and cost data for 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 FILTERthe calculation results for the 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 by the depreciation rate (Fig 3).   

Fig. 5 Using ROLLFORWARD and FILTER to calculate depreciation, a 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 SHIFTingthe cost of sales or revenues for a given number of months. 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, and the explicit driver of unit price is the exchange rates (Fig. 7). 

Fig. 7 Advance Payments calculation

Financial Statement Simulation 

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

The baseline scenario describes stagnation on all fronts, the Technology Leadership scenario tries to simulate the development and sales of a new airliner. With the 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, a 10% increase in units produced should be possible thanks to the digitization of the production. To achieve this optimistic scenario, Winside needs to increase research and development spending, invest in the 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 cause an 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, and 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 presentations, 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 company’s commitment to its share and stakeholders. There are many possible extensions 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 TIMELAGfunction is perfect for such tasks. The costs of capital would increase accuracy. Financing operations with repayment of the debt, issuing new bonds, or raising new capital by giving a 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.