Questions to be answered
Ski Pole Production Plan Number of Ski Production Resin Used Surlyn Used Rubber Used Poles Ski Poles Setup Cost Unit Cost (ounces) (ounces) (ounces) Produced Total Cost Questions to be answered: Downhill $ 1,050.00 $ 31.99 Cross Country 1, 195.00 29.87 1. How many ski poles were produced? Alpine 1,305.00 39.82 2. What is the total cost to produce the Cross Country ski pole for this product run? TOTALS 3. How many ounces of resin were used during this production run? 4. How many ounces of rubber remain in inventory (not used in the product run)? 5. What is the total cost for all ski poles combined for this production run? Constraints Table 1 (H7) Total Cost $ 28,000 Raw Material Used per Ski Pole (Ounces) G4) Downhill Quantity 200 Resin Surlyn Rubber G5) Cross Country Quantity 225 Downhil 3 2 3 G6) Alpine Quantity >= 250 Cross Country 4 (D7) Total Resin Used 3805 Alpine 5 4 3 (E7) Total Surlyn Used 2675 (F7) Total Rubber Used 1965 (G4:G6) Total Skis Produced Integer Directions: Use Solver to determine the best way Bruin Outdoors can allocate a $28,000 budget to maximize the production of three types of ski poles. The resources or constraints found on this spreadsheet are the $28,000 production budget and the available amount of raw materials (Resin (3,805 oz), Surlyn (2,677 oz), and Rubber (1,965 oz). The Downhill ski pole has a product set up cost of $ 1050 and a variable unit cost of $31.99 to produce, the Cross Country ski pole has a product set up cost of $1195 and a variable unit cost of $29.87 to produce, and the Alpine ski pole has a product set up cost of $1305 and a variable unit cost of $39.82 to produce. You have promised the marketing group to give away at least 200, 225, 250 of each brand, respectively for a total of at least 675 ski poles. The total ounces of resin, surlyn, and rubber on hand are 3805, 2675, and 1965. This is all of the raw materials on hand to produce the 3 brands of ski poles. Table 1 contains the raw material breakdown required to produce each brand of ski pole. Your objective is to maximize the total number of ski poles produced by varying the number of each brand produced. The number of ski poles produced is limited by the following requirements: 1. Do not exceed the $28,000 budget Produce at least 200 Downhill ski poles 3. Produce at least 225 Cross Country ski poles 4. Produce at least 250 Alpine ski poles 5 . Do not exceed the available amounts of resin, surlyn, and rubber in inventory.Ad Placement Ad Cost per Issues per (Number of Media Circulation Issue Year Issues) Total Audience Total Cost extreme Skis 548,945 $ 39,000.00 6.00 Ski Quarterly 864,532 $ 41,000.00 4.00 Slopes Monthly 229,491 $ 26,000.00 12.00 Downhill - 4 - Real 398,054 $ 35,000.00 6.00 Ski Magazine 257,092 $ 31,000.00 12.00 Total Objective: Minimize Total Cost Min G9 INSTRUCTIONS: Constraints Roberto Lamberto, marketing manager, is heading up a magazine promotion for Belmont Outdoors. Roberto will use the same ad Total audience (F9) 12,000,000 in each of five magazines that he has selected as representing the primary target audience. Roberto has prepared a spreadsheet Ad placement E4:E8 2 that includes each magazine's circulation (the number of distributed copies) and ad cost per issue (the cost of placing one full-page Ad placement E4:E8 int integer Ad placement E4:E8
