The “Total” column displays the sum of the values in cells B4–M4 using the formula:
Posted: Wed Jan 22, 2025 8:57 am
The same principle is used to calculate operating expenses in the financial model. The data must be synchronized with the "Expense Plan" table .
Operating profit must be calculated by subtracting similar expenses from operating income. Let's say operating profit in January was: 151 - 96 = 55 thousand rubles.
Profitability is the ratio of operating profit to operating income, which is additionally multiplied by 100%.
In January, this indicator in our example is at the level of: 55 / 151 * 100 = 36.69%. Formula in Excel:
fх = B13 / B4 * 100.
It should be emphasized that exploring amazon: the essentials the final profitability is not the sum of the indicators achieved in the previous months. It is understood as the ratio of the final operating profit amounts to income.
The cumulative profit is the profit/loss for the previous and current reporting periods. Let's say the operating profit in January is 55 thousand rubles. The following month, you need to add 58 thousand rubles to it, which makes 113 thousand rubles. In March, the final figure grows to 176 thousand rubles, since they added 64 thousand rubles. And so on.
In the financial model, the cumulative total, for example, for February is fixed using the formula:
fх=B15+C13.
Make a cash flow plan
Let's assume that the business is operating only, and there are no capital investments or borrowed funds. To simplify the calculations, we will also exclude accounts receivable, as if the time of payment and delivery of goods were identical.
When building a financial model, it is necessary to take into account that the cost of rent and salary are paid the following month after their accrual. While management expenses are incurred immediately.
The cash flow plan in the example looks like this (data in thousands of rubles):
Cash Flow Plan
To calculate operating expenses, wages are added to rent and management expenses.
The operating balance is the receipts from which the operating payments are deducted. For January, this figure is 113 thousand rubles, i.e. we subtract 38 from 151.
To calculate the balance at the end of the period in a financial model, you need to add the balance at the beginning of the period and the balance on operating activities. If in the example at the beginning of January the balance was 10 thousand rubles, then at the end of the period we will get 123 thousand rubles.
Form a forecast balance
It is created based on income and expense plans, financial flows. Be sure to take the balances from the balance of the previous period - for example, they are 10 thousand rubles.
Forecast balance in the financial model for our example (information in thousands of rubles):
Formation of the forecast balance
In the "Cash" column , you need to indicate the balance at the end of the previous month. It turns out that cash for February is 123 thousand rubles.
Assets are the sum of cash, fixed assets, and accounts receivable.
To calculate accounts payable, you need to add up this indicator for the previous period and the planned costs of wages and rent for the same month. Then subtract the expected cash flows for wages and rent from the result. So we get that as of February 1, accounts payable equals 58 thousand rubles: 0 + 38 + 20 – 0 – 0.
Capital is calculated as this indicator for the previous period, added to the amount of operating profit for the same month. On February 1, it is at the level of 65 thousand rubles: 10 + 55.
Liabilities are the sum of capital and accounts payable.
Accounts receivable are calculated by adding this figure for the previous date and the shipment figure for the period. The cash received is then subtracted from the amount.
The resulting model can be further made more detailed, highlighting different types of management expenses, breaking down salaries by specialists, and detailing the sales plan.
To get the best financial model, it is important to adjust the template, set actual indicators instead of planned ones, and control risks and results.
Operating profit must be calculated by subtracting similar expenses from operating income. Let's say operating profit in January was: 151 - 96 = 55 thousand rubles.
Profitability is the ratio of operating profit to operating income, which is additionally multiplied by 100%.
In January, this indicator in our example is at the level of: 55 / 151 * 100 = 36.69%. Formula in Excel:
fх = B13 / B4 * 100.
It should be emphasized that exploring amazon: the essentials the final profitability is not the sum of the indicators achieved in the previous months. It is understood as the ratio of the final operating profit amounts to income.
The cumulative profit is the profit/loss for the previous and current reporting periods. Let's say the operating profit in January is 55 thousand rubles. The following month, you need to add 58 thousand rubles to it, which makes 113 thousand rubles. In March, the final figure grows to 176 thousand rubles, since they added 64 thousand rubles. And so on.
In the financial model, the cumulative total, for example, for February is fixed using the formula:
fх=B15+C13.
Make a cash flow plan
Let's assume that the business is operating only, and there are no capital investments or borrowed funds. To simplify the calculations, we will also exclude accounts receivable, as if the time of payment and delivery of goods were identical.
When building a financial model, it is necessary to take into account that the cost of rent and salary are paid the following month after their accrual. While management expenses are incurred immediately.
The cash flow plan in the example looks like this (data in thousands of rubles):
Cash Flow Plan
To calculate operating expenses, wages are added to rent and management expenses.
The operating balance is the receipts from which the operating payments are deducted. For January, this figure is 113 thousand rubles, i.e. we subtract 38 from 151.
To calculate the balance at the end of the period in a financial model, you need to add the balance at the beginning of the period and the balance on operating activities. If in the example at the beginning of January the balance was 10 thousand rubles, then at the end of the period we will get 123 thousand rubles.
Form a forecast balance
It is created based on income and expense plans, financial flows. Be sure to take the balances from the balance of the previous period - for example, they are 10 thousand rubles.
Forecast balance in the financial model for our example (information in thousands of rubles):
Formation of the forecast balance
In the "Cash" column , you need to indicate the balance at the end of the previous month. It turns out that cash for February is 123 thousand rubles.
Assets are the sum of cash, fixed assets, and accounts receivable.
To calculate accounts payable, you need to add up this indicator for the previous period and the planned costs of wages and rent for the same month. Then subtract the expected cash flows for wages and rent from the result. So we get that as of February 1, accounts payable equals 58 thousand rubles: 0 + 38 + 20 – 0 – 0.
Capital is calculated as this indicator for the previous period, added to the amount of operating profit for the same month. On February 1, it is at the level of 65 thousand rubles: 10 + 55.
Liabilities are the sum of capital and accounts payable.
Accounts receivable are calculated by adding this figure for the previous date and the shipment figure for the period. The cash received is then subtracted from the amount.
The resulting model can be further made more detailed, highlighting different types of management expenses, breaking down salaries by specialists, and detailing the sales plan.
To get the best financial model, it is important to adjust the template, set actual indicators instead of planned ones, and control risks and results.