Excel HW 2_Bruin Ski Magazine Campaign

Ski Pole Production Plan Ski Poles Unit Cost Downhill $ 1,050.00 $ 31.99 600.00 400.00 Cross Country 1,195.00 29.87 1084.00 271.00 Alpine 1,305.00 39.82 1250.00 1000.00 TOTALS 2934.00 1671.00 Constraints Table 1 (H7) Total Cost <= $ 28,000 Raw M (G4) Downhill Quantity >= 200 (G5) Cross Country Quantity >= 225 Downhill (G6) Alpine Quantity >= 250 Cross Country (D7) Total Resin Used <= 3805 Alpine (E7) Total Surlyn Used <= 2675 (F7) Total Rubber Used <= 1965 (G4:G6) Total Skis Produced int Integer Directions: The resources or constraints found on this spreadsheet are the $28,000 production budget and th The Downhill ski pole has a product set up cost of $1050 and a variable unit cost of $31.99 to prod and a variable unit cost of $29.87 to produce, and the Alpine ski pole has a product set up cost of You have promised the marketing group to give away at least 200, 225, 250 of each brand, respec The total ounces of resin, surlyn, and rubber on hand are 3805, 2675, and 1965. This is all of the r Table 1 contains the raw material breakdown required to produce each brand of ski pole. The number of ski poles produced is limited by the following requirements: Production Setup Cost Resin Used (ounces) Surlyn Used (ounces) Use Solver to determine the best way Bruin Outdoors can allocate a $28,000 budget to maximize Your objective is to maximize the total number of ski poles produced by varying the number of ea 1.        Do not exceed the $28,000 budget 2.        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.

Total Cost Questions to be answered: 600.00 200.00 $ 7,448.00 542.00 271.00 $ 9,289.77 6. How many ski poles were produced? 750.00 250.00 $ 11,260.00 7. What is the total cost to produce the Cross C 1892.00 721.00 $ 27,997.77 8. How many ounces of resin were used durin 9. How many ounces of rubber remain in inven 10. What is the total cost for all ski poles comb Material Used per Ski Pole (Ounces) Resin Surlyn Rubber 3 2 3 4 1 2 5 4 3 he available amount of raw materials (Resin (3,805 oz), Surlyn (2,677 oz), and Rubber (1,965 oz). duce, the Cross Country ski pole has a product set up cost of $1195 $1305 and a variable unit cost of $39.82 to produce. ctively for a total of at least 675 ski poles. raw materials on hand to produce the 3 brands of ski poles. Rubber Used (ounces) Number of Ski Poles Produced e the production of three types of ski poles. ach brand produced.

Belmont Outdoors - Bruin Ski Magazine Ad Campaign Media Circulation 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 Min G9 Constraints Total audience (F9) >= 12,000,000 Ad placement E4:E8 >= 2 Ad placement E4:E8 <= Issues per year <= D4:D8 Questions to be answered: 1. How many total ads were placed for this promotion? 28.00 2. What is the total cost for the magazine promotion? 940000 3. What is the total audience reached during the promotion? 12004418 4. What is the total cost for Ski Magazine for this promotion? 124000 5. What is the total audience for Ski Quarterly? 3,458,128 Ad Cost per Issue Issues per Year Objective: Minimize Total Cost INSTRUCTIO Roberto Lam same ad in e prepared a s issue (the co issues each m issues. You c Roberto asks readers in on (circulation m multiplied by Roberto’s fin

Total Audience Total Cost 6.00 3,293,670 $ 234,000.00 4.00 3,458,128 $ 164,000.00 8.00 1,835,928 $ 208,000.00 6.00 2,388,324 $ 210,000.00 4.00 1,028,368 $ 124,000.00 28.00 12,004,418 $ 940,000.00 Ad Placement (Number of Issues) ONS: mberto, marketing manager, is heading up a magazine promotion for Belmont Outdoors. Roberto will use th each of five magazines that he has selected as representing the primary target audience. Roberto has spreadsheet that includes each magazine’s circulation (the number of distributed copies) and ad cost per ost of placing one full-page ad in one issue of the magazine). Roberto's spreadsheet also indicates how man magazine produces yearly: monthly magazines produce 12 issues and bimonthly magazines produce six cannot have more Ad Placements in a magazine than that magazine has Issues per Year. s you to determine the best way to place ads in each magazine to reach his primary goal of at least 12 millio ne year while minimizing the total cost. You need to calculate the total audience reached for each magazine multiplied by the number of issues in which the ads are placed) and the total cost (the ad cost per issue y the number of issues in which the ad appears) for running the specified number of ads in each magazine. nal requirement is that you must place the ad in at least two issues of each magazine during the promotion