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