Question II
The PriceRite big box discount center orders printer cartriges from a manufacturer in the Far East. PriceRite annual demand for the printer cartridges is 6000 units. Assume that demand is steady, and the lead time is zero. The fixed cost per order is $200, while the inventory carrying cost is 25%. The wholesale price is $45 per unit. The supply firm has just offered PriceRite a quantity discount contract: if they place an order of at least 2000 units, the price will be $44 per unit. Build a spreadsheet model to evaluate the quantity discount offer.
Input
Description Symbol Value Metric
Annual demand D cases
Regular price V /case
Discount price V_ /case
Minimum order quantity MOQ cases/order
Ordering cost A /order
Carrying cost factor H /case/yr
Result
Description Symbol Regular Symbol Discount
Economic order quantity EOQ EOQ_
Actual order quantity AOQ AOQ_
Number of order per year N N_
Cycle stock CS CS_
Annual ordering cost OC OC_
Annual carrying cost CC CC_
Annual purchase cost PC PC_
Total relevant cost TRC TRC_
Savings from discount SAV
Question III
Penny Smith, a logistics analyst at 3W Company, would like to develop a convenient and accurate method of examining LTL shipment rates from Seattle to Atlanta. Penny queried the 3W Company database for the shipment rates. The results of the query are shown in the Shipment Rate Table. Complete the following spreadsheet model for Penny.
1. Decision Variable 2. Shipment Rate Table
Description Symbol Value Metric Break (lb) MW (lb) Rate ($/lb)
Shipment Weight SW 45 lb 0 1 $330.10
5 $259.93
3. Results 10 $217.28
Description Symbol Value Metric 20 $185.23
Minimum weight MW lb per shipment 50 $161.39
Billed weight BW lb per shipment 100 $134.16
Freight rate F per lb 200 $126.52
Shipment cost SC per shipment
Effective Rate ER $/lb
Question V
Winter Gear, a manufacturer of high-end snowboarding and skiing equipment in Boulder Colorado, must decide how many units of its new snowboard, the Daredevil 5000X to put in production for the upcoming season. Based on historical demand, management forecasts that demand will be normally distributed with a mean of 5000 and a standard deviation of 750. Winter Gear’s production cost per unit is $500, and they sell the snowboards for a retail price of $999. If any snowboards are left over in March, they can be sold on eBay for $250 each. If the retailer were to run out of boards, management estimates that the cost of lost goodwill is $400. Build a spreadsheet model to determine how many units of the Daredevil 5000X snowboard Winter Gear should put in production.
Inputs Symbol Amount Measure Work Area Symbol Amount Measure
Demand Parameters Cost of underage CU
Mean of Demand UD units Cost of Overage CO
Standard Deviation of Demand SD units Critical Ratio CR
Price and Cost Parameters
Unit retail selling price P /unit
End-of-season salvage price V /unit Results Symbol Measure
Unit cost C_ /unit Order Quantity Qstar units
Goodwill cost G /unit Probability of stockout POS
