Assistance needed with 5 year sales projection for manufacturing company

jfrancis

New Member
Joined
Nov 7, 2013
Messages
9
Hi everyone, I have recently been asked to make changes to a sales forecast. I've never done a task like this before but I would like to make a 5 year sales projection based on historical data from 2010-2016.

Every company deals with different items, and so, it becomes difficult to follow online tutorials and YouTube videos on how to create a sales forecast. A bit more about my company: we manufacture pipeline products (steel only) and they come in various sizes, grades and thicknesses.

Our selling price greatly depends on the current oil prices (WTI). When the price of oil drops, we make less revenue and vice versa. My current forecast looks like this and I have sales data from 2010 to 2016.



I also have forecast oil prices from 2016 to 2020 and since there is a positive correlation between oil prices and our revenue, I want to prepare a sales projection based on this data. Any help would be appreciated. Can I make a projected revenue graph and table without having to increment each future year by 10%. That approach would merely increase the sales projection without taking into consideration the trends in oil prices.

I currently have the below fields of data:
1. Sales revenue per client (in US$)
2. Sales volume (in metric tons) per client
3. Historical oil prices (2010 - 2016)
4. Forecast oil prices (2016 - 2020)
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
Well, at least it's a pretty cool and fun project... if a bit challenging :)

To some degree it is probably going to depend on how fancy you want to get with your predictions. If you want to go all-out, you would be talking Azure ML or R... which integrates pretty well with Power BI (but I am less sure about Excel). But you would be talking about a pretty serious learning curve.

If we are just talking about something a bit more "straight math" like... growth rate is constant 10% then multiplied by N% of oil price... then that is certainly easier. Folks here in the power bi forum will tend to steer you towards power pivot... because it is awesome :) Some learning curve there as well, though -- pretty helpful crew here for that.

If you want to stick to "just straight excel", then you should probably jump to the main excel forum here on mr excel, instead of the power bi one.

Good luck!
 

jfrancis

New Member
Joined
Nov 7, 2013
Messages
9
Thank you scottsen for your quick reply. I would want to use Power Pivot for this one instead of plain old math formulas. And, as you said, there's a learning curve too, which is always good for me. Since the prices of crude oil steer our revenue to a great extent, I want to use something that takes in this data (historical oil prices) and calculates accordingly. I've taken this data from the U.S. Energy Information Administration (EIA), this data is pretty accurate.

The current sales projection is done by multiplying previous years' revenue by 1.1 and this 10% growth looks good on the sheet, but it's not considering the oil prices. I would like to know how I can use the Analysis ToolPak or Power Pivot to make a more realistic prediction for 2016 - 2020.

P.S. I'm using a company laptop with no admin privileges (can't install software), so I'm unable to dive into R until I want to learn it on my personal PC.
 
Last edited:

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
If you aren't going to do fancy machine learning, I would personally just math it up, and import the results as a static table. So, maybe... manually do a linear regression on sales vs oil price, and just use that calculated correlation with future estimates to get a future estimated sales... that I guess you further multiply by some growth factor.

You probably can do this all in power pivot, but I'm not sure its necessary to do the above calcs in power pivot vs once a year and re-import.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,847
Messages
5,483,279
Members
407,390
Latest member
jenniferjohns

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top