This guide explains

Request Free Demo

If you are still using Excel for your inventory planning, can we suggest that you register for a free demo of Lokad - Inventory Optimization Software.

**Size your inventories optimally**

Time is money. Room is money. So what you want to do is use all means at your disposal in order to reduce your stocks – without experiencing any shortages, of course.

How? By forecasting!

**Always label your columns**. Use the first row of each column to describe the data it contains.**Different data, different columns**. Do not put different numbers (for example, you costs and your sales) on the same column. It is incredibly likely for you to get confused, and it makes computations and data handling more difficult.**Give each file a clearly understandable name.**It takes little effort and speed things up. It makes them easy to identify visually, and easier to find using the windows search function.**Use Comments.**

Even if you don't usually work with a large amount of data, it is still very easy to get confused. This applies especially if you come back to the data you have created a long time before. Excel has a great solution to offer:

The usefulness of comments |

You can use them:

- to
**explain**the content of a cell (i.e.*unit cost according to Mr Doe's estimates)* - to leave
**warnings**to future users of the sheet (i.e.*I have a doubt about this calculation...*)

Get advanced sales forecasts with our inventory forecasting webapp. Lokad specializes in inventory optimization through demand forecasting. The content of this tutorial - and much more - are native features of our forecasting engine tool.

First, it is always useful to create a graph in Excel, in order to take a look at the data. Your eyes are excellent tools that can help you identifying trends in a few seconds.

To do this, we select our data, then use Insert > Chart, and chose the XY(Scatter) option. We want to estimate sales as a function of quality, therefore we put the unit cost on the horizontal and the sales on the vertical axes.

Now, we stop a few seconds and take a good look at what we see: the relationship seems to be increasing, and linear.

In order to get an idea of the exact form of the relationship, we right click on the chart, and select the "Trendline" option.

Creating a trendline |

Now, we have to select the relationship that seems to "fit" (i.e. best describe) our data. Here again, we use our eyes: In this case, the dots are almost in a straight line, so we use the "linear" setting. Later on, we will use other - more complex, but often more realistic - settings, like "exponential".

Our trendline is now displayed on the chart. Another right click allows us to display the exact form of the relationship: y = 102.4x - 191.64.

Understand: Number of unit sold = 102.4 times the unit cost - 191.64.

So, if we decide to produce at a $150 unit cost, we can expect to sell 102.4*150 - 191.64 = 15168 units.

A linear trendline |

We have just completed our first forecast successfully.

However, be careful: The software is always able to find a relationship between the two columns, even if this relationship is in reality very weak! Therefore a check for robustness is required. Here is how you quickly do this:

**First, always take a look at the chart**. If you find the dots closely located to the trendline, as is the case in our example above, there is a good chance that the relationship is robust. However, if the dots seem to be located almost randomly and are in general quite far from the trendline, then you should be careful: the correlation is weak, and the estimated relationship should not be blindly trusted.

The dots are everywhere: no evident relationship, unreliable forecasts |

The dots "make sense", and allow more reliable forcasting |

- After taking a look at the chart, you can use the
**CORREL**function. In our example, the function would read: CORREL(A2:A83,B2:B83). If the result is close to 0, then the correlation is low, and the conclusion is: there is simply no real trend. If it is close to 1, then the correlation is strong. The latter is a helpful, since it increases the explanatory power of the relationship you found.

There are more subtle ways of making sure the correlation is high; we will come back to this later on.

Of course, these last steps can be automated: you don't have to note the relationship, and use your pocket calculator to do the computation. You need the Analysis Toolpak!

Here is how to perform an exponential fitting:

1) Take look at your data. Draw a simple graph, and just look at it. If they follow an exponential evolution, they should look like this:

perfect exponential shape |

Using trendlines |

2) Luckily, you can also do all this directly, using the Analysis Toolpak: Put all your data into a blank excel sheet, and go to

- Make sure you have your Office CD with you. Excel might require you to insert the CD in order to install the ATP files
- Open an excel sheet, and go to Tools Menu, and then select Add-Ins. Check the first box of the window, labeled « Analysis Toolpak ».
- Insert your Office CD if asked to do so by the software.
- That's it! Notice that your « Tools » menu now includes many more features, including a « Data Analysis » option. This is the one that we will use the most.

Open your data sheet, then open the « tools » menu and select « Data Analysis ». A window pops up, asking you what kind of analysis you want to perform. Select « regression » for linear settings.

Now you need to give Excel two arguments: an « Y range » and an « X range ». The Y range indicates what you want to estimate (i.e. your sales), and the X range contains the data that you think can explain your sales (here, your unit cost). In our example (see example1.xls), our sales data are in column B, from row 3 to row 90, so you need to put « $B$3:$B$90 » as the Y range, and «$A$3:$A$90 » as the X range. When you are done, click « ok ».

A new sheet appears, containing the « regression results ».

The Analysis Toolpak Output, in the case of an Ordinary Least Squares regression |

The most important result is contained in the « Coefficients » column at the bottom of the sheet. The intercept is the constant, and the « X variable » coefficient is the coefficient of X (here, your unit cost). Hence, we find the same equation we found using the « trendline » function. Sales = Intercept + Xcoefficient * unit costSales = -126 + 100 * unit cost

This sheet also contains a useful number that gives you information about how good your estimation is: the « R Square ». If it is close to 1, then your estimate is good, which means that the equation you found is a fairly good representation of your data. If it is close to 0, then the estimation is not good, and you should probably try another kind of fitting (see exponential fitting below).

This method is probably faster than the « trendline » techniques. However, it is a bit more technical and much less visual. So if you do not want to go through the trouble of plotting and eyeballing your data, make sure you at least check the « R square » value.

Launch the Analysis Toolpak, as usual: Open your data sheet, then open the « tools » menu and select « Data Analysis ». A window pops up, asking what kind of analysis you want to perform.

In our exponential setting, what we want to select is « exponential ».

Notice that Excel only asks you for one input range. Select the column that contains the data you want to forecast (i.e. unit cost), and pick a “smoothing factor”.

Click here to learn more about inventory optimization software

- ABC analysis
- Container shipments
- Economic order quantity
- Financial impact of accuracy
- Inventory accuracy
- Inventory control
- Inventory costs (carrying costs)
- Inventory turnover
- Lead time
- Lead demand
- Min/Max Planning
- Multichannel Order Management
- Optimal service level formula
- Perpetual Inventory
- Phantom Inventory
- Product life-cycle
- Reorder point
- Replenishment
- Safety stock
- Service level
- Stock-keeping unit (SKU)

- Backtesting
- Forecasting accuracy
- Forecasting methods
- Obfuscation
- Overfitting
- Pinball loss function
- Quantile regression
- Seasonality
- Time-series

- Bundle Pricing
- Competitive Pricing
- Cost-Plus Pricing
- Decoy Pricing
- Long-term maintenance agreement pricing
- Long-term pricing strategies
- Price Elasticity of Demand
- Price Skimming
- Repricing software (Repricer)
- Styling Prices for Retail