Creating an Excel financial model with a user-friendly app

Perhaps the greatest challenge for those who have a good business idea is translating it into a well-designed financial model. This is a complex job even for those who already have some experience. Thus, we are launching an important update to our "Excel Apps": we are building financial models with an app! That's it, you describe it, and the app will build the model for you. With an easy to use interface, which automatically generates the Excel model, exactly tailored to the business idea, with a design and language chosen by you.

How does Finance App work?

Before you can start using the app, you need to get access to it, which will be done by creating an account. There is nothing to download to a local computer, you may access it via a browser.

From the app, a process of creating a business plan in stages follows. From the business idea, through the definition of the name, the identification of the value proposition and the positioning in the market, the application helps the user to structure the business idea in a solid way.

Using generative AI, the app suggests names for the business and Copilot will help write the business idea, the company's differentiation in the market and how it will present itself to customers:

We take advantage of this initial phase to also include the styling of the file that will give rise to the financial model. What is the intended color and font scheme and, very importantly, in which language will it be created: in Portuguese or in English?

Create a forecast of financial results and assess the feasibility of the investment project

It is very important to understand that there are several business models with different financial models. The creation of a business in a Traditional Retail business model, for example, is different from the creation of a model for a Services company. They have different economies, with different cost structures, with greater or lesser capital intensity.

Therefore, Finance App leads the user to specify their business idea and translate it into sources of income, fixed costs, variable costs and investment. Following a structured route is simpler.

Let's look at an example of a company providing training and consulting services. How do you create a business model for this company?

Starting with the definition of the plan, we will have the name, the identification of the products you will sell, the period for which you intend to create the financial model, among other characteristics, including the definition of the business model and the rate of change of the assumptions to be included in the construction of the model.

In this case, we see, for example, that the model to be created will vary the assumptions up to 20% of its base value by positive and negative, in order to test the sensitivity of the business model.

To define this degree of sensitivity, the user drags the slicer to the value that interests him and the model will generate three scenarios: the base, the pessimistic and the optimistic scenario:

For a user with less technical knowledge, we have included some explanatory notes throughout the application that help you better understand what each of the parameters means:

This is what happens when financial or general assumptions are created, which are transversal to the entire business model. I think most users will know what the inflation rate is, but others may be a little more difficult to understand:

To create a forecast for a given analysis period, it will be necessary to identify the ways in which the company will make money, there may be several, of course, and they may have several units of measurement. A consulting firm, in principle, will be able to obtain measurable returns through the number of "billable hours", that is, the time allocated to the development of projects with its clients, which is measured in hours.

If it were another source of income, the unit of measurement could be different. For example, for the sale of goods, the unit of measurement could even be the number of units sold of each product or family of products. The model will take into account the selected unit of measure, multiplying the quantity by the respective unit price to obtain the billing volume.

And by following the path to translating the business idea into numbers, the application will help to make the forecast based on relatively simple assumptions. For example, assuming that this company plans to sell 1000 hours of consulting at a unit price of €80 in the first year. It expects the number of billable hours to grow at the rate of 3% per year and to grow the unit price at the rate of 5% per year.

It should be noted that these data are merely fictitious and any resemblance to reality is a mere coincidence.

By clicking on the "Make new forecast" button, the forecast for this source of income is created in moments:

And the values are calculated automatically allowing the user to edit them manually if necessary. As you can see, we used a "base year followed by growth rates" criterion to make the forecast without using any type of formula.

Everything else in the process is identical. Certain parameters are defined that lead the user to predict fixed and variable costs, based on inducers, to calculate how much he will spend on staff, including charges, to define the amounts to be invested to start the business and keep it running, among several other elements.

The Financial Model In Excel

After all the elements are properly filled in, it is possible to create the Excel template. In short, this template includes the translation of all assumptions into a spreadsheet, with the design and language characteristics previously defined.

The calculations are carried out through Excel formulas, which will allow the user to adjust the Excel model itself according to their needs. You can view and change calculations if you want. You can even add new tabs and basically do whatever you want with this file. The file extension is .xlsx, has no macros and will work in Excel desktop and Excel online, and can also be imported into Google Sheets and any other compatible platform.

The process of building the financial model in excel takes about 5 minutes and the user conveniently receives a copy of it in their email:

The user will also be able to navigate the different financial models through the application. With some limits, it is possible to create several Excel financial models for each business plan with different assumptions and simulations. And it is also possible to create several business plans, with different characteristics and values.

Regarding the content of the financial model, we have adopted the best practices in the construction of this type of models (which we also teach how to do in our training courses in Excel for finance), namely the separation between assumptions, calculations and outputs, the use of named ranges in formulas, among many others.

In summary, we will have a model with a very summarized introduction page with the main characteristics of the model and the main feasibility indicators:

An Input tab where all assumptions are made with sources of income, fixed and variable costs, personnel costs, scenarios, initial and working capital investment, among others:

A Calculations tab where all the heavy lifting of forecasting the project's free cash flows is carried out, the preparation of financial statements balance sheet and income statement and the calculation of the project's financial viability, through the calculation of the Net Present Value (NPV), Internal Rate of Return (IRR) and Payback:

A Charts tab containing some analysis charts and the result of the Monte Carlo simulations:

Finally, there is a hidden tab where the details of the Monte Carlo simulation results are listed:

As mentioned, the entire model is built dynamically and as a result of the assumptions introduced in the application. If a given project has 5 revenue streams and the forecast time horizon is 3 years, the model structure will be different from that of a model with 2 revenue streams and a 10-year time horizon.