Modify the synchronized ordering model in Example 12.5 slightly so that you can use a two-way SolverTable on the fixed costs. Specifically, enter a formula in cell B9 so that the fixed cost of...

Modify the synchronized ordering model in Example 12.5 slightly so that you can use a two-way SolverTable on the fixed costs. Specifically, enter a formula in cell B9 so that the fixed cost of ordering kings alone is equal to the fixed cost of ordering queens alone. Then let the two inputs for SolverTable be the fixed cost of ordering queens alone and the joint fixed cost of ordering both kings and queens together. Let these vary over a reasonable range, but make sure that the first input is less than the second, and the second input is less than twice the first. (Otherwise, the model wouldn’t be realistic.) Capture the changing cells and the sum of annual setup and holding costs as SolverTable outputs. Describe your findings in a brief report.

EXAMPLE 12.5 SYNCHRONIZED ORDERING AT SLEEPEASE

Sleepease, a retailer of bedding supplies, orders king-size and queen-size mattresses from a regional supplier. There is a fairly constant demand for each of these products. The annual demand for queens is 2200; the demand for kings is 250. The unit purchasing costs for queen-size and king-size mattresses are $100 and $120, and the company’s cost to store either of these for one year is $15. Sleepease’s ordering cost is based primarily on the fixed cost of delivering a batch of mattresses. This ordering cost is $500 if either queens or kings are ordered separately, but the ordering cost is only $650 if both are ordered together. Sleepease’s cost of capital is 10%. The company wants to know whether synchronizing orders is better than not synchronizing them, and if so, it wants to find the best synchronized ordering policy.

Objective To find the optimal synchronized ordering policy, and to compare it to the EOQ policy where orders for the two are not synchronized.