Scenario Manager in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Using Scenario Manager to Compare Sales Projections

You have a sales projection model with three variables: Price per Unit, Units Sold, and Marketing Budget. You create three scenarios: Best Case, Base Case, and Worst Case. Which of the following is the correct way to set up the Worst Case scenario in Scenario Manager?

A Set Price per Unit to highest value, Units Sold to lowest value, Marketing Budget to highest value.

B Set Price per Unit to highest value, Units Sold to highest value, Marketing Budget to lowest value.

C Set Price per Unit to lowest value, Units Sold to highest value, Marketing Budget to lowest value.

D Set Price per Unit to lowest value, Units Sold to lowest value, Marketing Budget to highest value.

💡 Hint

Think about what would make the sales outcome the worst.

The worst case means lowest revenue and highest cost. So Price and Units Sold should be low, and Marketing Budget (a cost) should be high.

📊 Formula Result

intermediate

Result of Applying a Scenario in Excel

You have a formula =B2*C2-D2 where B2 is Price, C2 is Units Sold, and D2 is Marketing Budget. The Base Case scenario has Price=10, Units Sold=100, Marketing Budget=200. The Best Case scenario has Price=12, Units Sold=120, Marketing Budget=180. What is the formula result after applying the Best Case scenario?

A 1260

B 1140

C 1020

D 1200

💡 Hint

Calculate (Price × Units Sold) - Marketing Budget using Best Case values.

Best Case: (12 × 120) - 180 = 1440 - 180 = 1260.

❓ Function Choice

advanced

Choosing the Right Function to Summarize Scenario Results

You have multiple scenarios saved in Scenario Manager showing different profit outcomes in cell E5. You want to create a summary table that shows the average profit across all scenarios. Which Excel function should you use to calculate this average from the scenario summary report?

A SUM(E5:E7)

B MIN(E5:E7)

C AVERAGE(E5:E7)

D MAX(E5:E7)

💡 Hint

Think about which function calculates the average value.

AVERAGE calculates the mean of the values, which is what you want for average profit.

❓ data_analysis

advanced

Analyzing Scenario Summary Data for Decision Making

You have a scenario summary report with three scenarios: Low, Medium, and High. The profit values are $500, $750, and $1000 respectively. You want to identify the scenario with the highest profit and the difference between the highest and lowest profits. What are these values?

A Highest profit: $1000; Difference: $500

B Highest profit: $750; Difference: $250

C Highest profit: $1000; Difference: $750

D Highest profit: $500; Difference: $1000

💡 Hint

Find max and subtract min from it.

Highest profit is $1000. Difference = 1000 - 500 = 500.

🧠 Conceptual

expert

Understanding Scenario Manager Limitations

Which of the following is NOT a limitation of Excel's Scenario Manager?

A It can only handle up to 32 changing cells per scenario.

B It automatically updates all scenarios when you change a value in the worksheet.

C It does not support scenarios with formulas as changing cells, only values.

D It cannot create scenarios with more than 128 scenarios in a workbook.

💡 Hint

Think about what Scenario Manager can and cannot do regarding changing cells.

Scenario Manager can handle up to 32 changing cells per scenario, so this is a true limitation. The question asks for what is NOT a limitation. Option B is a limitation, so it's not the correct answer. Option B is false because Scenario Manager does NOT automatically update all scenarios when you change a worksheet value; you must apply scenarios manually. Option B is true; changing cells must be values, not formulas. Option B is true; maximum scenarios per workbook is 128. Therefore, the correct answer is B.