Revenue Projection Formula

The Financial Projections Template was designed to be simple and flexible and for that reason revenue projections are entered manually as a fixed numeric amount for each of the five years. For example, if the revenues for year 1 to 5 are 50,000, 90,000, 162,600, 291,600, and 524,880 respectively then these amounts are simply entered on line 3 in cells D3, E3, F3, G3, and H3.

While entering the amounts is a simply process, it is not necessarily the quickest. If a simple relationship can be established between the revenue figures for each of the five years, then there is nothing to prevent formulas being entered into the Financial Projections Template in cells D3 to H3, avoiding the need to re-enter fixed numeric amounts each time a change is made.

For example, the revenue figures given above are increased by 80% each year. If the first year revenue is changed from 50,000 (cell D3) to say 60,000, then manual calculations would have to be performed to produce the new revenue forecast values (60,000, 108,000, 194,400, 349,920, and 629,856), and each entry would then need to be entered into its respective cell.

The same answer could be obtained by entering the first year revenue of 60,000 in cell D3, and then entering a revenue projection formula to calculate the revenue for years 2 to 5 in cells E3 to H3. In that way, if the first year revenue is changed again, then the Financial Projections Template would automatically recalculate the revised revenue projections.

For example, the revenue for year 2 would be calculated using a revenue projection formula as follows:

Year 1 revenue (cell D3) = 60,000
Year 2 revenue (cell E3) = Year 1 revenue (cell D3) x (1 + % increase)
Year 2 revenue = 60,000 x ( 1 + 80%)
Year 2 revenue = 108,000

The revenue projection formula to enter in cell E3 is therefore =D3*(1+80%). Using this formula each time cell D3 is changed, cell E3 will automatically change without manually re-entering data. Providing a relationship can be established between the revenue amounts for each of the 5 years, any revenue projection formula can be inserted into cells D3 to H3.

Typically the relationship tends to be a percentage increase or a fixed amount increase on the previous years amount, as explained in the examples below.

Revenue Increases by a Fixed Percentage Each Year

Suppose for example a business projects first year revenue of 40,000 and estimates that revenue growth will be 50 percent each year. The first year revenue is entered into cell D3 and then the revenue for years 2 to 5 can be calculated by inserting the following revenue projection formulas into each of the cells.

In the above example, if the formulas are entered correctly the revenue for each of the five years should be 40,000, 60,000, 90,000, 135,000, and 202,500 respectively.

By simply amending the starting revenue (40,000) or changing the percentage (50%) used in the revenue projection formula, the revenue for years 1 through 5 can be quickly recalculated.

Revenue Increases by a Fixed Amount Each Year

Suppose for example a business projects first year revenue of 60,000 and estimates that it will increase by a fixed amount of 50,000 each year. The first year projected revenue is entered into cell D3 and then the revenue for years 2 to 5 can be calculated by inserting the following revenue formulas into each of the cells.

The revenue for each of the five years using the revenue formula should be 60,000, 110,000, 160,000, 210,000 and 260,000 respectively.

By simply amending the starting revenue (60,000) or changing the fixed amount (50,000) used in the revenue projection formula, the projections for years 1 through 5 can be quickly recalculated.

Combining Different % Increases and Fixed Amounts in the Revenue Projection Formula

By combining percentages increases and fixed amounts a realistic projection of the revenue of the business can be built.

Suppose for example a business projects first year revenue of 50,000 and estimates that it will increase by 100%, 50%, 25%, and 20% over the following four years. In addition it anticipates specific increases (perhaps new contracts or branch openings), which will add 20,000, 25,000, 100,000, and 10,000 to revenue in each of the next 4 years.

The first year revenue is entered into cell D3 and then the revenue for years 2 to 5 can be calculated by inserting the following revenue projection formulas into each of the cells.

In the above example the revenue for each of the five years would be 50,000, 120,000, 205,000, 356,250, and 437,500 respectively.

By simply amending the starting revenue (50,000) or changing the percentages or the fixed amounts used in the revenue projection formula, the revenue for years 1 through 5 can be quickly recalculated.

By establishing a relationship between the revenue amounts for each of the five years, it is possible to use a revenue projection formula in the financial projections template to speed up the process of recalculating and creating different revenue forecasting models and scenarios.