To learn how to develop a financial model using Excel, start with simple tables. Use external indicators such as cost and demand for goods, and among internal data rely on revenue and costs. For now, a minimal number of elements will be enough for you, and you can not try to achieve high accuracy of calculations.
It is more important to set up working relationships that will ensure automatic recalculation. Later you will be able to complicate this tool, add details to it.
As an example, let's look at creating a financial model in Excel with a small number of variables. Prepare a separate page for each table.
Set the initial data
We need to forecast revenue. To do exploring amazon: the essentials this, we will rely on the annual plan for implementing the company's proposals. For now, the values can be rounded or approximate figures can be used. It is worth explaining that revenue is the amount of funds received from the provision of services / sale of products.
The plan for the implementation of services looks like this, for example (information is given in thousands of rubles):
Initial data
Determine variable costs
We are talking about the costs of staff salaries. Let's imagine that in our company this figure is determined by the volume of products sold and is equal to 25% of revenue. Salaries are calculated every month by multiplying the coefficient 0.25 (25% of 100%) by the sales plan for a specific month.
The costs of renting premises and management will be considered as fixed values.
To determine how much to pay staff for January, we multiply the sales plan for this month by the coefficient:
151 * 0.25 = 38 thousand rubles.
In Excel you need to substitute the formula:
fx = 0.25*title of the page with the table containing information on the implementation plan !B8.
The amount of planned costs corresponds to the costs of salaries, rent and management expenses taken together.
The cost plan in our financial management model will look like this:
Definition of variable costs
Prepare an income and expense plan
Enter data in the Operating Income and Operating Expenses lines using references to the corresponding cells of the functional plans.
This part of the table has the following appearance (information is given in thousands of rubles):
Income and Expense Plan
Operating income corresponds to the amounts received from the sale of services 1–4. Thus, for January this figure will be 151 thousand rubles (the sum of 15 + 30 + 46 + 60).
As a formula we write:
fx =SUM(B5:B8)
Specify the numerical values for services using links to the cells of the “Service Implementation Plan” table.
The “Total” column displays the sum of the values in cells B4–M4 using the formu