How to Forecast Revenue Growth Using Excel

Do you want to predict your sales revenue for the next year? or years?

Here’s what you should know when forecasting data.

What is forecasting?

Forecasting is the process of making predictions of the future based on past and present data and most commonly by analysis of trends. A commonplace example might be estimation of some variable of interest at some specified future date.

If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.

Pretty cool huh.

How can you actually use this?

Imagine you’re a CEO that produce security software for this company and you need to see how you can increase revenue for the next year.

How do you do this?

You have one of your Financial Analysts draft a sales revenue forecast for 2018 based on the past data.  You provide your analyst with the sales revenue numbers and he crunches some forecasts for you.  He/she provides you with the prediction for next year’s growth along with a trend line based on how well you will do.

Here’s how you do it….

1. Open Excel > select a new spreadsheet

See example:

Excel

2. Select Blank Workbook

Blank workbook

3. Input sales figures and years into the cells.

Forecast1

4. Input forecast formula into the blank cell for the year you want to forecast, such as 2018.

The formula is shown below as =FORECAST(x, known y’s, known x’s)

Like this…

=FORECAST(B10(year 2018), y values(Sales), x values(year))

Forecast2

5. Enter FORECAST formula.  See example below.

Note:  For a faster and easier way.  You can highlight cells A2:A9, put a comma, then highlight B2:B9.  Then press enter.  The figure should appear in the left cell for the predicted outcome.

Forecast3

6.  See answer below for the year 2018.  The revenue is based on the predicted growth for the next year.

Forecast4

7.  Visualize the data in a chart.  Copy data > Paste in different cell where you want the new data.

copy forecast

8. Click > insert > chart > recommended chart > Line chart.  Insert a trend line to indicate the sales trend for the next year(s).

Note: Make sure you have room to insert your chart where you want to place it.  Format the y axis title to say Amount, and x axis title to entitle it Year.  Remember to change the title of your chart to be Sales.

Forecast Chart

There you have it. Forecasting revenue using linear regression.  Have fun playing around with Excel.  Stay tunned for more Excel tips later using multiple regression.

Thanks for reading!

Follow me to learn more amazing data tips!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s