Linear programming problems

See attached file for full problem description.

1. The Computer Gaming Company (CGC) plans on releasing a new product. It is a keyboard designed for hard core gamers. The total cost to produce and market this keyboard would be $250,000. It will cost $25 per keyboard for materials and assembly. The company plans on selling them for $65 each.
a. Use an algebraic procedure to determine the break even point.
b. Create a spreadsheet to help solve this problem.
c. How many keyboards does the company have to sell in order to break even?
d. A sales forecast of 8,000 keyboards has been obtained. Assume that 8,000 keyboards are made and sold. How large can the marginal production cost be before the keyboard ceases to be profitable?
e. Management has determined that they could reduce the fixed cost to $225,000 if they produce the keyboards in a different country. Since the shipping will increase the marginal cost will increase to $29 per keyboard. How many units would now have to be sold in order to breakeven?
f. Comparing the answers of c with e, recommend and justify where the keyboards should be made.

2. The Computer Gaming Company now wants to introduce a new gaming desktop and a laptop computer. While some of the desktop and laptop construction will take place in separate parts of the factory, the final finishing stage needs to share the same part of the facility.
Desktop Laptop
Unit Profit $1,000 $600
Hours Available
Hours needed in Part 1 of Factory 4 0 16
Hours needed in Part 2 of Factory 0 2 8
Hours needed in Part 3 of Factory 4 6 34
a. Create a spreadsheet to find the best product mix, of desktop and laptop computers. Test your spreadsheet with values of (Desktop, Laptop) of (1,1), (2,2) and (3,2).
b. Use Solver to find the optimal solution.
c. Would the product mix change if more hours were made available for the final finishing (Part 3) stage of the factory? Explain your answer.

3. The Computer Gaming Company is growing in leaps and bounds. Now they want to add Computer Desks, Chairs, and a lap table (for use in bed with the new laptops produced in question 2). The production of each of these pieces of computer furniture requires 75, 25, and 2.5 pounds of a new space age material respectively. The computer furniture requires five hours (Desk), two and a half hours (Chair), and half an hour (lap table) to assemble. Each piece of furniture produces $250, $85, and $10 profit. CGC has 675 pounds of the new space age material and 175 hours of labor available per week.
a. Make a rough sketch of a spreadsheet model, with blocks laid out for the data cells, changing cells, output cells, and target cell.
b. Build the spreadsheet model and determine how many desks, chairs, and lap tables should be made in order to achieve the greatest total profit.
c. Management has decided that at least 6 desks and 6 chairs must be made per week. Update the spreadsheet and determine how many desks, chairs, and lap tables should be made in order to achieve the greatest total profit.

4. Your great uncle has admired your ambition with this new company and upon his death he has left you his two space age material making facilities. You have decided that you can use these raw materials once processed to make your desks, chairs, and laptop tables. The materials will need to be shipped to one of two plants to be processed and then shipped to you factory where it can be used. The diagram below shows the distribution network.

a. Formulate and solve a linear programming model for this problem using Excel. The goal is to determine the most economical plan for shipping the space age material from their two sources (S1 and S2) through the distribution network to the Factory (F).

5. You are very happy with your new products, but somehow you feel like you are becoming disconnected from the main reason why you created CGC. You want to reexamine your computer line. You are curious to see how your total profit will change if you change the number of hours available in the factories to produce the laptop and desktop computers. You are also curious to see what would happen to your products if you had to change unit profit on these items. Use the information below.
Desktop Laptop
Unit Profit $1,000 $600
Hours Available
Hours needed in Part 1 of Factory 4 0 16
Hours needed in Part 2 of Factory 0 2 8
Hours needed in Part 3 of Factory 4 6 34

a. You need to determine how the total profit will change with respect to the number of hours available in the first part of the facility. Use Solver Table to create a table that will show the optimal solution of the number of desktops and laptops that should be made to optimize the total profit if Factory 1 is open between 10 and 38 hours using one hour increments. Calculate the shadow price for the different optimal solutions for desktops and laptops.
b. What conclusion can be drawn about how much the estimate of the unit profit can differ in each direction from its original value of 16 hours for Factory 1.
c. Create a solver table that will show how the total profit would change if the unit profit varied between $400 and $1,000 dollars per laptop and $500 to $1,200 per desktop. Explain your findings.
d. Create a solver table that will show hot the optimal quantity of desktops and laptops will change if the unit profit varied between $400 and $900 dollars per laptop and $500 to $1,200 per desktop. Does the optimal solution change and if so explain why/