Excel, Inventory Management, Purchasing, Supply Chain Management

Quantitative forecasts are as in-demand as ever. This post provides four solid forecasting options in Microsoft Excel that can be used to predict sales, operating costs, performance and more. The forecasting methods explained include: moving average, the Excel FORECAST function, trendline and regression using the Analysis ToolPak.


Moving Average

A moving average predicts the future value of a time period through averaging past time period values. That forecast can then be used in further forecasting down the line, averaging it with other time period values. The risk in Moving Average forecasting is that it can lag behind a trend.

  1. Select the Data tab, then Data Analysis command button. In the Data Analysis dialog box, select the Moving Average item from the list and then click OK.
  2. Identify the data you want to use to calculate the moving average. Click in the Input Range text box of the Moving Average dialog box. Then identify the input range, either by typing a worksheet range address or by selecting the worksheet range. Your range reference should use absolute cell addresses ($A$1:$A$5 as opposed to A1:A5).
  3. Indicate how many values are to be included in the moving average calculation in the Interval text box. By default, Excel uses the most recent three values to calculate the moving average (i.e 3 month moving average, 3 year moving average, etc.). To specify that another number of values are to be used to calculate the moving average, enter that value into the Interval text box.
  4. Use the Output Range text box to establish the worksheet range into which you want to place the moving average data.

The FORECAST Function

The FORECAST Function predicts a future value using existing values. The predicted value is a y value for a given x value. The known values are existing x values and y values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

FORECAST (x, known_y’s, known_x’s)

example =FORECAST(100,A2:A10,B2:B10)

  • X is the data point for which you want to predict a value.

  • Known Ys are the dependent range of data.

  • Known Xs are the independent range of data.


Trendline


A Trendline is simple way to analyse the trend within a collection of data points within a graph, smoothing out the data oscillations in the process. The trend within the data is then used to forecast future performance.
  1. Create a graph with your existing data.
  2. Right click on any of the data oints within the graph and select Add Trendline.
  3. In the Format Trendline window select whether you would like to forecast/analyse using: exponential, linear, logarithmic, polynomial, power or moving average. Linear is most common. If the data appears to trend towards compounding, try exponential
  4. Click on close.

Regression Using the Analysis Toolpak (ATP)


  1. Ensure Analysis Toolpack is installed (Tools tab, select Excel Add-ins, select Analysis Toolpak)
  2. Select the Tools tab
  3. Select Data Analysis
  4. In the Data Analysis window select regression.
  5. The Input Y range represents what you want to estimate (likely sales)
  6. The Input X range represents the data that can explain your Y (likely unit cost or price)
  7. Click OK


What does all this mean?

In Regression Statistics
Multiple R represents to correlation coefficient between Y and X
R Square represents the percentage of Y you can explain from X. A number closer to 1 indicates low variability and a number closer to 0 indicates a random correlation between your X and Y.

In ANOVA
Regression represents the number of independent variables
Risidual represents Total – Regression
Total represents the number of values – 1 (minus 1) (likely the number of active rows – 1)
SS represents Sum of Square
MS represents Mean Sum of Square
Lower % and Upper % means that 95% of the time, your coefficient will be between the lower value and the upper value


P-Value – The lower the P-Value, the less variability you have. The result of 1 – P-Value provides you with the percentage that the intercept will be correct


Cover photo credit: Finance Monthly
0

Purchasing, Supply Chain Management

In pursuing my Post-Graduate Certificate in Supply Chain Management – Global Logistics, I could not help to draw parallels between the components of SCM and my previous career in participant sports event operations. This indirect background in Supply Chain often led to constant back-and-fourth between my instructors and I. The biggest take-away was that all three segments of Supply Chain Management, which include: purchasing, inventory management and logistics (transportation) are required for a well-managed event, whether on a large or small scale. This article focusses on how mass participant event operations relates to the supply chain management component of purchasing (procurement) and is the first in a series of three articles on the topic. The remaining two will focus on logistics and inventory Management.


Forecasting

An event will require the procurement of various goods and services to ensure a quality experience for their participants. Needless to say, large scale events will require more goods and services than medium to small scale events, while benefitting from economies of scale. As mass participant events often allow individuals to register for the event up until the days, or even the day before that event is to take place, accurate forecasting is required. In the case of a marathon, organizers often use a 3-year moving (or rolling) average of registration numbers as of a certain calendar date (sometimes every day of the year) to forecast based on historical data. For example: on September 1, 10,000 were registered in 2015, 11,500 were registered in 2016 and 11,250 were registered in 2017. Therefor we can forecast that 10,917 will register by September 1, 2018. That data is to be compiled in Excel, where a trendline can be used to forecast sales. You can now order the required goods (food, water, medical equipment, timing chips, merchandise, participant shirts, finisher’s medals, etc.) and services (medical staff, parking attendants, waste disposal services, massage therapists, physiotherapists other contractors, etc.).





The Forecast is Always Wrong


The problem with a moving average is that it will lag behind the trend, so it is advised to utilize qualitative (Delphi method, market research, and historical life-cycle analogy) data in combination to quantitative (historical) data for forecasting purposes. New events are forced to rely completely on qualitative data, as no historical data exists. It is best practice for event operations professionals to order extra quantities (safety stock) to mitigate an unexpected last minute surge in registration (inaccurate forecast), as I can assure you that all hell will break loose if you under forecast participant shirts or finisher’s medals. It is often less expensive to order hundreds of one particular item from overseas that are shipped months ahead of time by sea, than a small order of last minute items shipped by air.

However, in the case of merchandise, you want to sell out. As marginal profit is most often lower than marginal cost per unit, the cost of not selling an item of merchandise outweighs the cost of selling an additional item. Therefore your optimal order quantity is most likely lower than your estimated demand. Chances are you overestimated demand anyway as the forecast is always wrong. Plus the inventory carrying cost associated with excess inventory will cause all kinds of headaches down the road.

 

Strategic Partnerships

As is often the case when negotiating contracts in a Just-In-Time or traditional Order-To-Stock environment, the creation and fostering of strategic partnerships is paramount. In my experience, it is advantageous to give a little extra, for example: taking a less hardline stance when negotiating price. No one appreciates feeling ripped-off, which will lead to cognitive dissonance and a toxic relationship moving forward. Quality, especially in the case of a top-tier/ premium event, is rarely worth sacrificing. Delivery lead time is also rarely worth delaying, with the exception being for the most experienced of event directors.

0