Logic for forecasting

[Jonathan9]

Hi Excelers,

I have a pickle that I need to solve in regards to forecasting.

I have two benchmarks - Market 1 and Market 2 which I need to use to make a forecast for market 3.

For Market 1 & 2 I have gross sales data for all months of 2012 and until April 2013.

For Market 3 I have gross sales data for April to December 2012 and until April 2013.

What I need to do is to make a forecast of the gross sales for Market 3 from May until December 2013 based on the data I currently have.

What would be the best way to approach this to get the most accurate forecast? And since my skills in Excel formulas are rather basic I would appreciate all help. I actually don't really know where to start.

Thanks.

[rcm]

I assume that the the product sales are seasonal.
to test that compare month sales/total yearly sales mkt 1vs mkt 2 and mkt 3

if there is correlation then multiply your desired total by the monthly porcentage above for the corresponding months (May -Dec)

send a sample to structure it.

[Jonathan9]

Thanks for the reply!

Yes, the sales are seasonal. Can you explain what you mean with desired total and how I reach that figure?

[rcm]

OK the desired total is the estimated annual figure one forecasts for the comming period.

in order to seasonalize the monthly amounts, one needs to calculate the % of the total that each month represents.
this % is then multiplied by the estimated annual figure and the forecasted amount is "seasonalized" by month.

[Jonathan9]

Right, it's the desired total that I want to project based on historic data for mkt 1 & 2 and possilbt jan - april for mkt 3.

Is still don't quite understand how to do the projections. But each market is selling 3 products and I have have what it represents in % share of overall gross sales for each month.

So if I understand it correctly one shall reach the average annual growth rate of, for example mkt 1, and then use that to multiply the monthly gross sales of mkt 3?

Would that be a somewhat correct way to do it?

[falkeauge]

When calculating a forecast there are other factors involved as well.
1. you need to determine what the average daily usage is, This can be done by dividing the total sales by 365. If you do not have sales data for twelve months you will need to calculate, Months: 1=300, 2=400, 3=250, 4=500 then 300+400+250+500 = 1450 / 4 = 362.5 X 12 = 4350 and 4350/365 = Daily usage of 11.9.

2. You need to determine the lead-time (how long it takes to get new stock). Say it takes 14 days then you need product for 14 days {11.9 X 14 = 167} else you will run out of product.

3. You need to calculate the re-order point i.e. when to order. It is normally half of lead-time thus 83 plus the leedtime it is known as ROP (re order point). So, now you will need to have 167 + 83 to trigger the re-ordering of the product so when the product is 250 it must be ordered.

4. You need to determine for how long you need stock before you can re-order again. So, if you order every three months then you need to have product for three months plus the lead-time stock, less the stock on hand, o order or on back order. So, three months (91.25 days)usage will be 91.25 X 11.9 = 1086. We will order 1086 + 167 (lead-time stock) = 1253 Less the stock on hand, on order and on back-order. Example: We have 250 of the product in stock when we order but nothing on order or back order: 1253 - 250 = 1003 So the re-order quantity will be 1003. If you want an example Workbook with all this logic in I will upload one.

[Jonathan9]

When calculating a forecast there are other factors involved as well.
1. you need to determine what the average daily usage is, This can be done by dividing the total sales by 365. If you do not have sales data for twelve months you will need to calculate, Months: 1=300, 2=400, 3=250, 4=500 then 300+400+250+500 = 1450 / 4 = 362.5 X 12 = 4350 and 4350/365 = Daily usage of 11.9.

2. You need to determine the lead-time (how long it takes to get new stock). Say it takes 14 days then you need product for 14 days {11.9 X 14 = 167} else you will run out of product.

3. You need to calculate the re-order point i.e. when to order. It is normally half of lead-time thus 83 plus the leedtime it is known as ROP (re order point). So, now you will need to have 167 + 83 to trigger the re-ordering of the product so when the product is 250 it must be ordered.

4. You need to determine for how long you need stock before you can re-order again. So, if you order every three months then you need to have product for three months plus the lead-time stock, less the stock on hand, o order or on back order. So, three months (91.25 days)usage will be 91.25 X 11.9 = 1086. We will order 1086 + 167 (lead-time stock) = 1253 Less the stock on hand, on order and on back-order. Example: We have 250 of the product in stock when we order but nothing on order or back order: 1253 - 250 = 1003 So the re-order quantity will be 1003. If you want an example Workbook with all this logic in I will upload one.

Thank you for your well-written answer. However, for this task taking into account the lead-time, re-order point and how long the stock lasts is not necessary.

I just need to figure out the projected sales for market 3 based on the historic data from market 1 and 2.

Speaking of which, I just found that there is a forecast function in Excel which I've been playing around which but I can't get to match up fully. Has anybody used this function?