98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Business Forecasting With Microsoft Excel
Fri 23rd October 2009
Data forecasting enables a business to try and see the shape of things to come by taking past data and predicting future performance. Businesses need to know how much income will be generated and what costs will be involved. Microsoft Excel gives us a number of tools to help us predict the future, for example, we can forecast future trends based on historical data or we could create scenarios of Best Case, Worst Case and 'In The Middle' Case of income and costs and see what effect variables in the business model have on the organisation. We can also use Excel tools to help set our business targets - you can learn how to in one of our Excel forecasting and data analysis training courses.
There are a number of formula based forecasting tools. The FORECAST function. This function uses mathematical analysis called linear regression, to find relationships between existing data pairs and apply these relationships to extrapolate data where only part of the data exists. For example: if we have 6 years worth of annual sales, we may want to predict sales in year 7 based on the previous 6 years. The syntax of the function is as follows:
= FORECAST(x, known_y's ,known_x's)
Where X is the single data point for which you want to predict a Y value. In the above example x=7 for year 7 . The known x's are a range (B6:G6) containing 1,2,3,4,5,6 representing the past 6 years . Known_y's is the dependant range of numerical data and in this example is the cell range(B7:G7) containing the 6 historical annual sales. Therefore sales in year 7 can be forecasted by:
=FORECAST(7,B7:G7,B6:G6)
The TREND function works in a similar way to FORECAST but instead of calculating a single Y value as above, it will extrapolate multiple Y values by calculating a linear trend (a best-fit straight line) using an array formula.
Expanding on the above example, we wish to calculate sales for years 7 -12 based on the sales of years 1 -6. The syntax of the function is as follows:
=TREND(known_y's,known_x's,new_x's)
Known Y's & Known X's are similar to the FORECAST function above. The additional argument of new X's are a cell range (H6:M6) with new X values for which you want TREND to calculate corresponding y-values (in this example 7,8,9,10,11,12). Because we will be using an array formula, all the cell for the predicted sales (H7:M7) should be selected and the following formula entered:
=TREND(B7:G7,B6:G6,H6:M6)
As this is an array formula, pressing CTRL+SHIFT+ENTER.
The GROWTH function will perform an exponential (a curved line that rises or falls at increasingly higher rates) growth calculation also to extrapolate multiple Y values.
The syntax is similar to the TREND Function:
=GROWTH(known_y's,known_x's,new_x's)
Business forecasting is a tricky affair. However, using historical data and the tools above will give businesses some insight to what might lie ahead
Author is a freelance copywriter. For more information on excel computer training, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-636-business-forecasting-with-microsoft-excel.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsForth de Avellar Limited
Director David Forth Excel Advanced This course was very well run and paced. I also liked the 'courses never cancelled' promise, as a competitor had done just that to me previously.... LCR Property
AP & Treasury Manager Nicola Shaw Power BI DAX There is nothing - it was brilliant! Knauf UK GmbH
Demand Planner Tracy Hills Excel Intermediate The pace of the training was just right, and the delivery of the training by Tom was exceptional. It is the first time I have done training in this format with the trainer demonstrating and me following along, and it really worked for me and my style of learning. I also like the fact that we get to keep the training files so that I can refer back to it if I need to when I'm using it in my daily work. |
PUBLICATION GUIDELINES