In this segment, the focus is on the objective of the problem statement: to optimise delivery charges and delivery time using Excel’s What-If Analysis tool. This tool is extremely useful for decision-making.
Excel offers three types of What-If Analysis: Goal Seek, Data Table, and Scenario Manager. You will explore each one in this segment.
Previously, you created two pivot tables for delivery time and delivery charges. In the demonstration, both pivots were combined into a single table to make the What-If Analysis easier.
With a summary of delivery days and costs across all shipping routes, the next step is to use the Goal Seek function. Goal Seek allows you to set a desired output value and back-calculate the input variables that achieve that output.
For demonstration, the Bangalore-Delhi route was used to show how Goal Seek works.
The picture below summarises the What-If Analysis demonstration performed for optimising delivery charges and delivery time across shipping routes.
In this example, the average delivery target for the Bangalore-Delhi route was set to 4 days. Using Goal Seek, the calculation showed that 75% of orders should be assigned to FASTWHEELS. The key assumption here is that the average delivery time for each provider on a route remains constant. For instance, FASTWHEELS and SHIP2HOME both take 3.3 days on average, regardless of the number of orders.
The main focus is to redistribute the orders to meet the target average delivery time.
Important points for Goal Seek:
- The cell containing the target must have a formula for Goal Seek to function correctly.
- The variable cells in the formula should not contain other formulas. For example, the percentage of FASTWHEELS orders was replaced with values before running Goal Seek, while the average delivery days were also pasted as values.
If you do not have a predefined target for average delivery days, the Data Table function in Excel can help. This allows you to see how the average delivery days change as the percentage of FASTWHEELS orders varies. This approach is useful for performing sensitivity analysis and observing multiple input-output scenarios at once.
In the video example, Goal Seek was applied to the Bangalore-Delhi route to demonstrate the process.
Before moving on, try attempting the related practice questions using the Ecommerce_demo dataset.
The upcoming segment covers the third type of What-If Analysis called Scenario Manager. This feature helps you compare results of multiple predefined scenarios within a single table.