Operating Expense Formula

To keep the financial projections template flexible and simple to use, operating expenses on spreadsheet lines 7, 8 and 9 are entered manually as fixed numeric amounts for each of the five years.

For example, if sales and marketing expenses for year 1 to 5 are 4,000, 7,500, 10,000, 20,000, and 30,000 respectively then these amounts can simply be entered on line 8 in cells D8, E8, F8, G8, and H8.

However, while entering the amounts is a simply process, it is not necessarily the quickest. If a simple relationship can be established between the operating expense and some other template cell value, then there is nothing to prevent formulas being entered on lines 7, 8 and 9, avoiding the need to re-enter fixed numeric amounts each time a change is made.

Operating Expense Formula Example

For example, it is quite common for sales and marketing expenses to be linked to revenue. If the sales and marketing expense is set at 10% of revenue, then each time the revenue is adjusted then the Financial Projections Template would automatically recalculate the revised sales and marketing expense without the need to manually recalculate and re-enter the expense.

Suppose, the revenue for year 1 is 40,000 then the sales and marketing expense for year 1 would be calculated using an operating expense formula as follows:

Year 1 revenue (cell D3) = 40,000
Sales and marketing expense (cell D8) = Revenue (cell D3) x 10%
Sales and marketing expense = 40,000 x 10%
Sales and marketing expense = 4,000

The operating expense formula to enter in cell D8 in this case is =D3*10%. Using this formula each time cell D3 is changed, cell D8 will automatically change to 10% of revenue without manually re-entering any data.

Providing a relationship can be established between the operating expense and the revenue for each of the 5 years, any operating expense formula can be inserted into the cells on lines 7, 8 and 9. In practice, the relationship tends to be based on a percentage of the revenue for the year.

Expense is a Fixed Percentage of Revenue

Suppose for example a business projects revenue for each of the 5 years of 60,000, 75,000, 150,000, 200,000, and 320,000, and estimates that sales and marketing expenses should be set as a variable cost of 8% of revenue. The revenue is entered into cells D3 to H3 in the normal manner, and the sales and marketing expense is then calculated by inserting the following operating expense formulas into each of the cells.

In the above example, if the operating expense formulas are entered correctly the sales and marketing expense for each of the five years should be 4,800, 6,000, 12,000, 16,000, and 25,600 respectively.

By simply amending the percentage (8%) used in the cost formula, the operating expense for years 1 through 5 can be quickly recalculated.

Combining Different Percentages and Fixed Amounts in the Operating Expense Formula

In effect, the formula linking the expense to revenue makes the expense a variable cost, as it changes in proportion to the level of revenue.

It is often the case that the expense will have both a variable and a fixed element. For example, the sales and marketing budget for the 5 years might include fixed amounts of 3,000, 3,000, 8,000, 10,000, and 20,000, together with a variable cost element of 10%, 8%, 5%, 5%, and 5% of revenue.

The sales and marketing expense for each of the 5 years can be calculated by inserting the following operating expense formulas into each of the cells.

In the above example if the revenue for each of the five years is set at say 60,000, 75,000, 150,000, 200,000, and 320,000, then the sales and marketing expense would be calculated using the operating expense formula as 9,000, 9,000, 15,500, 20,000, and 36,000 respectively.

By simply amending the percentages or the fixed amounts used in the operating expense formula, the sales and marketing expense for years 1 through 5 can be quickly recalculated.

The above examples were shown in relation to the sales and marketing expense in line 8 of the Financial Projections Template, however a similar technique could equally well be applied to the research and development expense on line 7, and the general and administrative expense on line 9.

By establishing a relationship between the operating expense and another cell in the template, it is possible using an operating expense formula to speed up the process of recalculating and creating different business forecasting models and scenarios.