Question Details

(Solved) Skills covered in this problem: Analyze data by creating and running a Solver model Interpret an answer report Identify feasible and infeasible...

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 Power Blocks.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 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 Power Blocks.xlsx workbook


Solution details:

This question was answered on: Sep 05, 2019

PRICE: $15 (25.37 KB)

Buy this answer for only: $18

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free solution (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .

About this Question






Sep 05, 2019





We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.


Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.


Order Now