Mark Whiskey needs to decide how many balloons to order for an upcoming citywide event. Because each store buys bottles whiskey from Mark's as a package of 500 and there are 3 potentially interested stores, demand will be 0, 500, 1000, or 1500. Mark has a decision to make: whether to order 0, 500, 1000, or 1500 units (bottles). Each unit costs Mark $6 and is sold for $10 to the stores. Any bottle that Mark does not sell will be donated.Enter in cell B8 a single formula that calculates total profit based on the inputs and intermediary inputs given. Note that profit depends not just on price and cost but also on number of bottles sold, which depends on both the order size (E5) and the demand (E4). Note that the demand and order size values of 0 in cells E5 and E4 are only there to construct a flexible profit formula (i.e., their values could be anything).Fill in the above two-way data table using Data/What-If-Analysis/Data Table.Use INDEX & MATCH functions to automatically return the profit value in cell C15 from the two-way data table that corresponds to the selected order size and demand (note that the formula should work for any order size and demand values selected from the dropdown menu in cells B15 & B16). A1 Mark Whiskey23 Inputs4 Unit price5 Unit cost678910Decisions11 (Order Size)1213141516174567Order SizeDemandB$10.00$6.001000500050010001500сDIntermediary InputsDemandOrder SizeE00Outcomes (Demand)5001000Calculate Profit from table using an Index/Match FunctionLLF1500

Answered: Mark Whiskey needs to decide how many…

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Related questions

Question

Mark Whiskey needs to decide how many balloons to order for an upcoming citywide event. Because each store buys bottles whiskey from Mark's as a package of 500 and there are 3 potentially interested stores, demand will be 0, 500, 1000, or 1500. Mark has a decision to make: whether to order 0, 500, 1000, or 1500 units (bottles). Each unit costs Mark $6 and is sold for $10 to the stores. Any bottle that Mark does not sell will be donated.

Enter in cell B8 a single formula that calculates total profit based on the inputs and intermediary inputs given. Note that profit depends not just on price and cost but also on number of bottles sold, which depends on both the order size (E5) and the demand (E4). Note that the demand and order size values of 0 in cells E5 and E4 are only there to construct a flexible profit formula (i.e., their values could be anything).
Fill in the above two-way data table using Data/What-If-Analysis/Data Table.
Use INDEX & MATCH functions to automatically return the profit value in cell C15 from the two-way data table that corresponds to the selected order size and demand (note that the formula should work for any order size and demand values selected from the dropdown menu in cells B15 & B16).

A
1 Mark Whiskey
2
3 Inputs
4 Unit price
5 Unit cost
6
7
8
9
10
Decisions
11 (Order Size)
12
13
14
15
16
17
4567
Order Size
Demand
B
$10.00
$6.00
1000
500
0
500
1000
1500
с
D
Intermediary Inputs
Demand
Order Size
E
0
0
Outcomes (Demand)
500
1000
Calculate Profit from table using an Index/Match Function
LL
F
1500

Expert Solution

Step 1

Instruction set: It is possible that sometimes demand is less than, equal to, or greater than the order size. Calculations will be different as per the situations.