Hello,
I am looking at the effects that diesel gas prices had on what we paid in fuel surcharges to our various carriers 2014. I am comparing this to what we would have paid if our diesel budget $ for 2014 was on target. Each carrier or type of carrier has a different fuel surcharge matrix and this is incorporated into the model.
I found that keeping a Canadian Dollar (CAD) exchange rate constant at what we budgeted (affects some lanes), we saved $235k on fuel surcharges. $3.95/gal budget vs $3.824/gal average of National No. 2 Diesel.
So for this year, I wanted to be able to track and explain variances on our logistics costs based on what we budgeted this year ($4.14/gal) and what the actual diesel prices turn out to be. So I was trying to do a sensitivity analysis and try to come up with a formula, which I've done many times in the past for other things.
Usually, when trying to find a trend line I would input various values into the model I was working with, take the outputs which the model spits out, and put them in a scatter plot to see the relationship via a linear trend line and displaying the formula and R-value.
In the attached model, cell E1 of the "Model" tab would be input and the "$/Unit of Measure effect" (cell AA10) is the output.
However, as you can see in the "Charts and Formula" tab, I plotted the inputs/outputs and it doesn't seems to be a linear function at all. When I add a Moving Average Trendline, I get a graph of what I believe is a Discontinuous Function.
I do have limited experience with data graphs and trend lines, so I am wondering if I am doing something wrong and if someone is able to help me figure out a good formula to use going forward?
I've removed some sensitive information from the model before uploading, but nothing that affects calculations.
Thank you for any help and insight you can provide!
Bookmarks