Blue Raider Toy Company is planning a huge promotion for Mighty Toys action figures during the upcoming holiday season. As the companys purchasing manager, your job is to determine the best way to purchase these toys from the manufacturer at the lowest price.

After contacting the Mighty Toys manufacturer, you learned that volume discounts and discounts for preseason orders are available on the Mighty Toys product line. You need to determine the best product mix based on the following information. The Blue Raider Urban Adventure set is the first item that most people will buy. Customers can also purchase the optional Super Action kit, which can be attached to the Blue Raider Urban Adventure set. Another companion set, the Blue Raider Outdoor set, has an optional Hiking kit that customers can purchase separately.

The marketing director for MTSU Toy Company, Betty Wright, wants to allocate the purchasing budget of $100,000 maximum among the four toys. Since the Super Action kit and the Hiking kit are accessories, Betty does not want to purchase more Super Action kits than Blue Raider Urban Adventure sets and does not want to purchase more Hiking kits than Blue Raider Outdoor sets. She also wants to purchase at least half as many Blue Raider Outdoor sets as Blue Raider Urban Adventure sets. The minimum number of any specific toy to be purchased is 250. She wants you to purchase at least 7,250 total items. All purchases must be for a whole item no fractions of an item can be purchased.

Since you are purchasing the Mighty Toys early, you must consider the cost of storing the toys in the MTSU Toy warehouse until the holiday sales season begins.

Your objective is to maximize the profit on all four toys.

Complete the following:

1. Open the workbook named Mighty.xlsx and save it as MightyToys(your last name).xlsx.
2. Create a new worksheet called Lookup to the right of Purchase Plan. In the Lookup sheet, create a table that has monetary information on each of the toys:
   1. Blue Raider Urban Adventure Set has a retail price of $19.95, a unit cost of $15.36, and a storage cost of $.15.
   2. Super Action Kit has a retail price of $14.95, a unit cost of $14.21, and a storage cost of $.10.
   3. Blue Raider Outdoor Set has a retail price of $16.95, a unit cost of $13.77, and a storage cost of $.25.
   4. Hiking Kit has a retail price of $11.95, a unit cost of $10.03, and a storage cost of $.15.
3. On the Purchase Plan sheet, write the formulas that will populate the retail price, unit cost, and storage cost per unit in their respective cells.
4. 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) for each of the toys.
5. Write the formulas to add the total number of toys purchased in cell B8, the total cost in cell I8, and the total profit in cell J8. Check that formulas entered in Step 4 link the objective cell (total profit) 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.
6. Create a constraints list in the worksheet that identifies the constraints in this problem.
7. Format all monetary fields as currency with two decimal places.
8. Use Solver to determine the best way to purchase the Mighty Toys sets and kits and maximize the profit.
9. Produce an answer report of your final solution. Use the answer report to recommend additional changes that might maximize the total profit. Place your recommendations in a cell on the answer report worksheet.