Multiple Variable Sensitivity Analysis

**RO24** · 08-20-2018, 09:29 AM

Morning - I have a question that I'm sort of stuck on. I'm attempting to do a sensitivity analysis on how the price of gas/gal, as well as the weight of the cargo being transported, would cut into profits.

See attached:

I have shown the axis with Price/Gal and Freight Weight. There is a key below, which shows the MGP with the allotted range of weights per MPG. There are other components to this, but one of them is that the round trip is 90 miles.

What is the best route to find the increase/decrease in freight charge? Is this kind of analysis the best way or is there another method?

Thank you in advance.

RO

**RO24** · 08-20-2018, 01:40 PM

Please move to the correct thread if you believe this belongs elsewhere.

Thank you,

RO

**MrShorty** · 08-20-2018, 04:46 PM

How much of this is an Excel specific "how do I perform the analysis described in Excel?" and how much (as I see in your question about another method) is the more generic, "how do I perform this kind of analysis (in any programming language or even paper and pencil)?" We are usually good at the former, if you can explain to us exactly how you want to perform the analysis. I don't know if any of us is familiar with logistics to be able to say that this is "the best way" (TM), if your question is more of the latter.

The first thing I see in the way that you arranged the table is that, once one decides what the formula should be, it should be easy to program into the spreadsheet if one understands the behavior of relative and absolute references (https://support.office.com/en-us/art...es_in_formulas ). You would enter the formula into one cell, then copy-paste into the rest of the table.

Assuming I can guess a reasonable approach to the problem, I see something akin to those old "distance=rate*time" problems you should have learned back in algebra https://www.purplemath.com/modules/distance.htm Of course, in this case, we are looking more at "how much distance can I cover on so many gallons of fuel if I consume fuel at this rate?" or "how much does it cost to buy fuel if I buy so many gallons at a given cost/gallon rate?" I might spend some time with the algebra to make sure I understand how distance, miles per gallon, and cost per gallon come together to give me total fuel cost. That formula would go into the table.

The other variable is freight weight, and you have a nice lookup table that you can use in a lookup function to get miles per gallon at a given freight weight. Are you familiar with Excel's lookup functions (https://support.office.com/en-us/art...rs=en-US&ad=US category --- lookup and reference functions). In this case, where your lookup table has miles per gallon in the leftmost column, you will need to use an INDEX(...,MATCH()) combination. Unfortunately, almost all examples across the internet are for "exact match" (3rd argument of MATCH() =0) type lookups, but your situation will call for an "approximate match" (3rd argument =1).

With the problem broken down into smaller pieces, which of those pieces of the problem do you need help with?