求saibt 的学习资料 qmb的 求解

武士刀

求qmb的学习资料 课堂上听的不是很明白
求解
Brass Widgets Inc. makes and sells 200 high quality intricate fittings every week. The fixed costs for buildings, machines and employees are $12,000 a week, while raw material and other variable costs are $50 per unit.
(a)         If the selling price is $130 per unit, what is the break-even number of units? Find the break-even point algebraically and illustrate it using an EXCEL graph. Attach the printout or copy your EXCEL graph into your assignment submission. On your graph, identify the region corresponding to profits and the region corresponding to losses.
EXCEL Instructions: Create a column called Number of Units and in that column enter values from 0 to 400 in increments of 50. Then create two more columns, one for Total Cost and another for Total Revenue. Enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of Units column. Highlight the resulting three sets of numbers and go to the Chart Wizard to obtain the graph. Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend). Please refer to Topic 3 in the green EXCEL Booklet for further instructions on entering formulae and graphing in EXCEL.
(b)         Based on your graph from part (a), is there a profit or a loss if 200 units are sold in a week at a price of $130 per unit? Explain briefly. Calculate the weekly profit or loss amount.
(c)         How many units should the company produce per week to make a profit if the selling price is lowered to $80 per unit? Is the rise in sales to 450 units per week sufficient for the company to make a profit? Explain briefly and then calculate the resulting weekly profit or loss amount.

Question 5
Jill Bright is a production manager at Bill’s Gate Computers. The company purchases a basic computer at wholesale prices and then adds a display, extra memory cards, extra USB ports, or a CD-ROM or DVD-ROM drive. The computers, with the added features, are then resold at retail prices. The company produces three models: Simplex, Omniplex and Multiplex. Each Simplex generates $200 in profit, while for each Omniplex the profit is $300. The profit for a Multiplex is $250. The Simplex model has fewer add-ons, so it requires only 60 minutes of installation time. The Omniplex has more add-ons and requires 120 minutes of installation time. Multiplex has more add-ons than Simplex, but not as many as Omniplex, and it requires 90 minutes of installation time. There are five employees available to do the installation work.
After several weeks of operation, the sales department does some market research. Based on this research, it is decided that the combined sales of Simplex and Multiplex models cannot exceed 20 units. Moreover, no more than 16 Omniplex computers can be sold in a week. Bill’s Gate employees also notice that they are getting many returns because of a problem with one of the add-ons. They realize that they need to test their finished products before shipping them. The task of testing the computers is assigned to only one of the installers. To accommodate this change, the other four installers agree to work 10 hours per week, so that the total available installation time remains 40 hours per week. The employee who does the testing also works 10 hours per week. It takes her 18 minutes to test a Simplex computer. Each Omniplex and each Multiplex both require 24 minutes testing time.
Jill must decide the rate of production per week for each computer model in order to maximize the company’s weekly profit.
Jill has chosen to use linear programming and she has asked for your help.
Questions that follow will guide you through the steps to obtain the solution and discuss the results.
(a)         Formulate a linear programming model for this problem. In doing so, consider the following:
•        What are the decision variables for this problem?
•        What is the objective for this problem? Using your decision variables, formulate the objective function.
•        What are the constraints in this problem? Using your decision variables, formulate these constraints.
(b)         Use EXCEL Solver to obtain a solution to the linear programming problem from part (a), together with an Answer Report and a Sensitivity Report. Provide a printout of your EXCEL spreadsheet and of the two Excel reports with your assignment submission.
EXCEL Instructions: All EXCEL output should bear your e-mail ID. To ensure this, you will need to save your EXCEL file as ‘E-mail ID Bill’s Gate.xls’. In addition, your constraint names should begin with your initials, e.g. MK_Testing.
Please refer to Topic 5 in the Excel Booklet or the Linear Programming Supplement to the textbook (pdf file posted on the course website) for detailed instructions on how to set up your spreadsheet and use Solver.
(c) One of Bill’s Gate competitors has launched a new advertising campaign and Jill worries that the profit per each Simplex model may decrease to $150.
Would the solution obtained in part (b) still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and Bill’s Gate Computers profit change, if at all? Attach a new set of results if necessary.
(d)        The other four installers agree to work 11 hours per week, so that the total available installation time increases to 44 hours per week
Would the solution obtained in part (b) still be optimal? Which of the EXCEL reports helps you answer this question? Justify your answer carefully. How would the solution and Bill’s Gate Computers profit change, if at all? Attach a new set of results if necessary.