A store manager looks at the following operational and sales forecasting data. The manager needs to decide the quantity for each SKU to replenish to maximize the total expected profits. 

 

Note that the manager must make the replenishment quantity decisions before observing which demand scenario actually occurs. For each SKU under each demand scenario, the store earns the sales revenue consisting of two parts: 

1) the selling price multiplied by the sales volume; and 2) the salvage value multiplied by the leftover inventory (if any).

Further, the store pays the procurement costs which equal to purchase cost per unit multiplied by the purchase volume. Assume that the five demand scenarios have equal likelihood. 

 

A) Help the manager build an Excel-based model to get the optimal replenishment quantities. [10 pts]

B) The upper-level management informs the store manager that no more than four SKUs can be replenished to control the fixed ordering costs. Help the manager build an Excel-based model to decide which SKUs to replenish and by how much. [10 pts]

 

SKU ID	Starting Inventory before replenishment	Demand Scenario I	Demand Scenario II	Demand Scenario III	Demand Scenario IV	Demand Scenario V	Selling Price	Salvage Value	Purchase Cost Per Unit
1001	77	40	17	54	78	99	$6	$2	$3
1002	1	75	11	99	53	13	$10	$4	$5
1003	57	81	32	53	9	56	$8	$6	$7
1004	33	41	97	47	62	11	$5	$2	$3
1005	24	18	100	39	90	25	$8	$5	$6
1006	24	79	69	58	59	3	$4	$2	$3
1007	9	43	98	41	43	94	$8	$5	$7
1008	19	22	60	92	26	52	$4	$2	$3
1009	98	81	34	5	66	57	$10	$5	$7
1010	43	53	98	54	80	19	$4	$2	$3

Answer rating: 100% (QA)

To build an Excelbased model to determine the optimal replenishment quantities and decisions for the ... View the full answer