Heading up 2 answers pdf

EXERCISES CHAPTER # 9

Exercise Ch9-1: Level 1 – Sports Wear Retailers John Pinot is the marketing manager who is heading up the magazine promotion for the TZ- Advantage tennis racquets. John already worked with a graphic artist and copy editor to produce the full-page color ad for the campaign. John will use the same ad in the five magazines that he has selected as representing the primary target audience. John prepared a worksheet that includes each magazine’s circulation (the number of distributed copies) and ad cost per issue (the cost of placing one full-page ad in one issue of the magazine). John’s worksheet also indicates how many issues each magazine produces yearly: monthly magazines produce 12 issues and bimonthly magazines produce six issues. John asks you to determine the best way to place ads in each magazine to achieve his primary goal of reaching at least 10 million readers in one year while minimizing the total cost. You need to calculate the total audience reached for each magazine (circulation multiplied by the number of issues in which the ads are placed). You also need to find the total cost for running the specified number of ads in each magazine (the ad cost per issue multiplied by the number of issues in which the ad appears). John’s final requirement is that you must place the ad in at least two issues of each magazine during the promotion. Complete the following: 1. Open the Magazine.xlsx workbook from the Chapter 9 folder, and then save the file as

9-1-MagazineAds-YourName.xlsx. 2. Insert the appropriate formulas in cells F4:F8 to calculate the audience (circulation multiplied by the ad placement) for each magazine. 3. Insert the appropriate formulas in cells G4:G8 to calculate the total cost (ad cost per issue multiplied by the ad placement) for running the specified number of ads in each magazine. 4. Insert formulas in cells E9, F9, and G9 to calculate the total number of issues in which the ad will appear (ad placement), the total audience, and the total cost of the magazine ad campaign. 5. In the constraints table, specify the constraints that John has provided and any other necessary constraints. After determining the objective cell, variable cells, and constraints, use Solver to specify your inputs. 6. Run Solver to calculate your solution and then evaluate your solution. If necessary, add, change, or delete constraints, and then rerun Solver to produce a feasible solution. 7. Save your solution as a scenario named Minimize Cost, and then produce an answer report. 8. John just gave you some new information to include in your solution. He has replaced Magazine 3 with a bimonthly magazine that has a circulation of 235,000 and the ads cost $20,000 per issue. He wants you to recalculate the promotion with this calculate a new solution, save your solution as a scenario named Reduced Issues, and then produce an answer report. 9. John wants the gross audience for the entire promotion to be more than 14 million readers. Without changing any of the constraints in your Solver model, explain why the magazine promotion can or cannot support a gross audience of more than 14 million readers. If you determine that the promotion

2

WEEK # 5 - EXERCISES CHAPTER # 9

cannot support an audience of this size, which constraint(s) impedes or binds the goal of increasing the audience? Explain the problem.

10. Save and close the 9-1-MagazineAds-YourName.xlsx workbook. -----------------------------------------------------------------------------------------------------------------------------------

Note ----------------------------------------------------------------------------------------------------------------------------------- For your help the initial Solver Model is:

a) Objective Cells: Maximize Total Cost ($G$9) b) Variable Cells: Ad Placement (Number of Issues) ($E$4:$E$8) c) Constrain:

Total Audience (F9) >= 10,000,000

Minimize Total Cost (G9)

Ad Placement (E4:E8) <= D4:D8

Ad Placement (E4:E8) int integer

Ad Placement (E4:E8) >= 2

The Scenarios and the Reports need to be created after the week that the exercise was published. Scenarios and Reports with previous dates will not be accepted and the grade of the exercise will be zeroes.

3

WEEK # 5 - EXERCISES CHAPTER # 9

Exercise Ch9-2: Level 1 – Creating a Production Plan for ATC Inc.

ATC Inc. is a manufacturing company that produces industrial valves. As the operations manager, you need to determine the production schedule for manufacturing three valves in the new 102 Series, which is used in oil refineries. Producing the 102 Series valves is a three-part process. During the casting phase, the body of the valve and some of the attaching pieces are created. During the machining phase, the casting surfaces are finished, and the inner valve flange is created. Finally, during the last phase, the valves are assembled and inspected. The plant manager provided you with a workbook that contains the production times for each production phase, and the unit cost and list price for each 102 Series valve. The plant manager also told you that your time is limited to 500 hours in the Casting Department, 500 hours in the Machining Department, and 100 hours in the Final Assembly Department. No more time is available in these departments. The workbook also includes the setup costs associated with a production run for each valve. You must account for these costs in the profit amounts for each valve. Based on historical demand for these valves, the company has a policy that any single valve in the 102 Series should constitute at least 10% of the production run and that no single valve should constitute more than 50% of the total production time. Your goal is to maximize the total profit of producing these valves while not exceeding the time available in each department. Complete the following: 1. Open the ATCValve.xlsx workbook from the Chapter 9 folder, and then save the file as

9-2-ATCValve-Production-YourName. 2. Write the formulas that calculate the total production time used for casting, machining, and final assembly. Enter a mathematical formula in the objective cell, and then enter formulas to link the objective cell with the variable cells. Add sample values to the variable cells to ensure that your worksheet is set up correctly and produces the expected results. 3. Create a constraints table in the worksheet that identifies the constraints in this problem. 4. Use Solver to determine the optimal combination of valves to build to maximize profit. 5. Produce an answer report of your solution. Which constraints could you modify to maximize the total profit? Place your recommendations in a text box on the answer report worksheet. 6. Save your solution as Scenario 1. 7. Modify the constraints in your constraints table and in Solver that you determined would further improve the solution, and then run Solver again. Save this scenario as Scenario 2. Add a comment to the scenario describing the constraints you modified and why you chose to do so. 8. Produce an answer report of your revised solution. Did your solution accomplish the goals you identified in Step 5? What other changes can you recommend to maximize the total profit? Place your recommendations in a text box on the answer report worksheet.

9. Save and close the 9-2-ATCValve-Production-YourName.xlsx workbook.

4

WEEK # 5 - EXERCISES CHAPTER # 9

-----------------------------------------------------------------------------------------------------------------------------------

Note ----------------------------------------------------------------------------------------------------------------------------------- For your help the initial Solver Model is:

a) Objective Cells: Maximize Profit Total Cost ($K$9) b) Variable Cells: Number Produced ($H$5:$H$7) c) Constrain:

Constraints

Casting time (C9) <= 500

Machining time (D9) <= 500

Final Assembly time (E9) <= 100

Number Produced (H5:H7) int Integer

Number Produced (H5:H7) >= 10% of Total (I5:I7)

Number Produced (H5:H7) <= 50% of Total (J5:J7)

The Scenarios and the Reports need to be created after the week that the exercise was published. Scenarios and Reports with previous dates will not be accepted and the grade of the exercise will be zeroes.

5

WEEK # 5 - EXERCISES CHAPTER # 9

Exercise Ch9-3: Level 2 – Radio Advertisement The marketing group has allocated a $225,000 budget for the radio promotion that it has planned as part of the product launch for TZ-Advantage. The marketing group has provided you with a worksheet that shows the cost estimates for producing and placing radio ads with the three major radio networks that the marketing manager plans to use. The one-time cost of each ad appears in the Production Cost column. The AQH Persons column identifies the average number of people listening to each network during any 15 minutes. The Cost per Ad column indicates the cost of running an ad once on each network. The Number of Ads column identifies the number of times that the ad will run on the network, and the Total Cost column shows the cost of running the specified number of ads. The Gross Impressions column is the product of multiplying the AQH Persons value by the Number of Ads value and indicates the potential reach of the ad. Gross impressions represent the number of times an ad is heard during a promotion. This shouldn’t be confused with how many people have heard it. Your goal is to maximize the number of gross impressions by varying the number of ads purchased from each radio network. The number of ads you can purchase from each network is limited by the following requirements:

• Do not exceed the $225,000 budget. • Achieve at least 15,000,000 gross impressions. • Purchase at least 50 ads from each network. • Purchase no more than 100 ads on each network.

Complete the following: 1. Open the Radio.xlsx workbook from the Chapter 9 folder, and then save the file as

9-3-RadioAds-YourName.xlsx.

2. Enter the appropriate formulas in the Radio worksheet to calculate the gross impressions for each radio network. 3. Enter the appropriate formulas to calculate the total cost for running the specified number of ads on each network. 4. Insert formulas in cells E7, F7, and G7 to calculate the total number of ads in the campaign, the total gross impressions, and the total cost for the radio campaign. 5. Insert formulas in cells H4:H6 to calculate the percentage of ads placed on each network. 6. Create a constraints table that identifies the constraints that you must use in your solution. After determining the objective cell, the variable cells, and the constraints, use the Solver Parameters dialog box to specify your inputs. 7. Use Solver to calculate your solution and then evaluate your solution. If necessary, add, change, or delete constraints and rerun Solver to produce a feasible solution. Balance the need to stay at or under the budget with the desire to reach a level of at least 15,000,000 gross impressions. 8. Produce an answer report for your solution

6

WEEK # 5 - EXERCISES CHAPTER # 9

9. The marketing manager just gave you some new information about the radio promotion. The advertising managers from two of the three radio networks have limited the number of units (advertising spots) available to no more than 40 ads per client for Radio Network 2 and no more than 50 ads per client for Radio Network 3 during the time in which TheZone wants to run its promotion. The manager raised the limit on Radio Network 1 ads to no more than 125. Save your first Solver model as a scenario named 50 Ads per Network, and then change your Solver model and find a new solution that limits the number of ads for the second and third networks accordingly. Save the scenario as Limited Ads and keep your Solver solution. 10. Run the Solver Answer Report and examine it. Which constraint(s) are binding? Which ones are policy constraints? Explain your findings, and then recommend some changes that might create a better solution in a text box inserted into the answer report worksheet. 11. Add your name and date at the end of the exercise

12. Save and close the 9-3-RadioAds-YourName.xlsx workbook. -----------------------------------------------------------------------------------------------------------------------------------

Note ----------------------------------------------------------------------------------------------------------------------------------- For your help the initial Solver Model is:

a) Objective Cells: Maximize Gross Impressions ($F$7) b) Variable Cells: Number of Ads ($E$4:$E$6) c) Constrain:

Total Cost (G7) <=

$225,000.00

Number of Ads (E4:E6) int integer Min Number of Ads (E4:E6) >= Max Number of Ads (E4:E6) <=

Gross Impressions (F7) >=

15,000,000

Max Number of Ads (E5:E6) <=

Radio Network 1 <= 125

Radio Network 2 <= 40

Radio Network 3 <= 50

The Scenarios and the Reports need to be created after the week that the exercise was published. Scenarios and Reports with previous dates will not be accepted and the grade of the exercise will be zeroes.

7

WEEK # 5 - EXERCISES CHAPTER # 9

Exercise Ch9-4: Level 2- Managing Purchases for BrightStar Toy Company.

Brightstar Toy Company, a national toy store, is planning a huge promotion for Power Blocks action figures during the upcoming holiday season. As the company’s purchasing manager, your job is to determine the best way to purchase these toys from the manufacturer at the lowest price. After contacting the Power Blocks manufacturer, you learned that volume discounts and discounts for preseason orders are available on the Power Blocks product line. You need to determine the best product mix based on the following information. The Power Blocks Urban Adventure set is the first item that most people will buy. Customers can also purchase the optional Turbo Action kit, which can be attached to the Power Blocks Urban Adventure set. Another companion set, the Outdoor set, has an optional Camping kit that customers can purchase separately. The marketing director for Brightstar, Betty Wright, wants to allocate the purchasing budget of $100,000 among the four toys. Because the Turbo Action kit and the Camping kit are accessories, Betty wants to buy at least half as many kits as sets, but the total number of kits cannot exceed the number of sets. Betty also wants to purchase at least 5,000 units of each set. Because you are purchasing the Power Blocks toys early, you must consider the cost of storing the toys in the Brightstar warehouse until the holiday sales season begins. Your workbook includes the retail price for each toy, the unit cost from Power Blocks, and the storage cost for storing the toys until you can send them to retailers. Your objective is to maximize the profit on all four toys. Complete the following: 1. Open the Power.xlsx workbook from the Chapter 9 folder, and then save the file as

9-4-Power-Blocks-YourName.xlsx. 2. Write the formulas that calculate the gross revenue (number purchased multiplied by the retail price), the total unit cost (unit cost multiplied by the number purchased), the total storage cost (storage cost per unit multiplied by the number purchased), the total cost (total unit cost plus total storage cost), and the profit (gross revenue minus the total cost). 3. Enter a mathematical formula in the objective cell, and then check that formulas entered in Step 2 link the objective cell with the variable cells. Add sample values to the variable cells to ensure that your worksheet is set up correctly and produces the expected results. 4. Create a constraints table in the worksheet that identifies the constraints in this problem. 5. Use Solver to determine the best way to purchase the Power Blocks sets and kits, and maximize the profit. 6. Produce a Feasibility Report to troubleshoot the solution to determine how the cost and quantity constraints caused the solution to be infeasible. TROUBLESHOOTING: In order to complete this step to produce a Feasibility Report, you must run the Solver model again without the integer constraints. 7. Save your Solver model as a scenario named Purchase Plan 1. 8. Betty tells you that she cannot increase the available budget and asks you to change the constraints to maximize the profit. She also asks you to keep the constraint to purchase more sets

8

WEEK # 5 - EXERCISES CHAPTER # 9

than kits and to purchase at least half as many Power Block Outdoor sets as Power Blocks Urban Adventure sets. She also wants you to purchase at least 7,250 total items. Update your constraints table and the Solver model with your changes, and then run Solver again. If necessary, adjust your constraints until you find a feasible solution. Save your Solver model as a scenario named Purchase Plan 2. 9. Produce an answer report of your final solution. Use the answer report to recommend any additional changes that might maximize the total profit. Place your recommendations in a text box on the answer report worksheet.

10. Save and close the 9-4-Power-Blocks-YourName.xlsx workbook. -----------------------------------------------------------------------------------------------------------------------------------

Note ----------------------------------------------------------------------------------------------------------------------------------- For your help the initial Solver Model is:

a) Objective Cells: Maximize Profit ($J$8) b) Variable Cells: Number Purchased ($B$4:$B$7) c) Constrain:

Total Cost (I8) <= 100000

Power Blocks Urban Adventure Set (B4) >= 1

Turbo Action Kit (B5) >= 1074 1/2 of B4

Power Blocks Outdoor Set (B6) >= 1074 1/2 of B4

Camping Kit (B7) >= 1342.5 1/2 of B6

Number Purchased (B4:B7) int integer

Turbo Action Kit (B5) <= 2148 <= B4

Camping Kit (B7) <= 2685 <= B6

Total Number Purchased >= 7250

The Scenarios and the Reports need to be created after the week that the exercise was published. Scenarios and Reports with previous dates will not be accepted and the grade of the exercise will be zeroes.