For Problems 1-3 below, submit a non-technical consulting report (approximately ? a page for each problem) accompanying by a technical appendix. The report should highlight your findings (e. g. business implications) and be prepared as if to be presented to an audience that has little knowledge of quantitative models. The technical appendix should include a formulation of a linear model, as we did in class (decisions, objective, constraints), and standard printouts of the spreadsheet model with an optimal solution (see Instructions for Standard Printouts below). Problem 1: Perfume (30 marks)
Rylon Corporation manufactures Brute and Chanelle perfumes. Raw material costs $3 per pound. Processing a pound of raw material takes one hour of laboratory time, and yields 3 ounces of Regular Brute and 4 ounces of Regular Chanelle perfume. Regular Brute can be sold for $7/ounce and Regular Chanelle can be sold for $6/ounce. Rylon has the option of further processing Regular Brute perfume to produce Luxury Brute perfume, selling for $18/ounce. Each ounce of Regular Brute processed requires additional 3 hours of laboratory time and yields one ounce of Luxury Brute at a cost of $4.
They can also process Regular Chanelle into Luxury Chanelle. Processing an ounce of Regular Chanelle requires 2 additional hours of lab time and yields one ounce of Luxury Chanelle, again at a cost $4. Luxury Chanelle sells for $14/ounce. Rylon has 4000 pounds of raw material on hand, and 6000 hours of lab time available. How can they maximize their profit? SKOLKOVO FT MBA Problem 2: Production & advertisement (35 marks) Your firm makes fluorescent paint pigments in four plants and ships them to four distributors (abbreviated "D1" through "D4"), as follows: Plant Northeast Southeast Northwest
Southwest Unit Shipping Cost To D2 D3 Capacity Unit Cost Impurities D1 1000 $ 12. 40 12 $ 1. 20 $ 1. 75 $ 2. 35 1250 $ 11. 55 15 $ 1. 95 $ 1. 35 $ 1. 75 950 $ 10. 85 18 $ 2. 45 $ 1. 50 $ 2. 10 1200 $ 12. 05 12 $ 2. 75 $ 2. 25 $ 2. 00 D4 $ 2. 85 $ 2. 15 $ 1. 95 $ 1. 45 The distributors' demand for the pigments is as follows: D1 15. 0 Max Impurities 700 Base Demand Advertising Sensitivity 0. 05 D2 15. 0 600 0. 1 D3 14. 0 550 0. 05 D4 15. 5 675 0. 125 For example, distributor D1 will accept up to 700 units of pigment, plus 0. 05 units for every dollar you spend on national advertising.
Advertising is not separated by distributor: a single expenditure affects all distributors simultaneously. Thus, if you spend $100 on advertising, D1's demand will be 700 + (0. 05)(100) = 705 units, D2's demand will be 600 + (0. 1)(100) = 610 units, D3's demand will be 555 units, and D4's demand will be 687. 5 units. "Max impurities" indicates the maximum average impurity level allowed for shipments to each distributor. For instance, the shipments from the four plants to D1, when mixed together, should have an average impurity level of at most 15. . You have at most $59,000 to spend on production, shipping and advertising, and all the distributors pay you $28. 50 per unit. How can you maximize your profits? Note: this problem combines blending, transportation, and elements of the "pickles" problem. 1) 2) Formulate a linear model. Give clear definitions to your decision variables. Set up a spreadsheet model. Use Solver to find the optimal solution. SKOLKOVO FT MBA Problem 3: Kingston Manufacturing (35 marks) Kingston Manufacturing produces heads for engines used in the manufacture of trucks.
The production line is highly complex and measures 500 meters in length. Two types of engine heads are produced on the line: the P-Head and the H-Head. The P-Head is used in heavy duty trucks and the H-head is used in smaller trucks. Because only one type of head can be produced at a time, the line is either set up to manufacture the P-Head or the H-Head, but not both. Changeovers from producing one type to the other are made on weekends and cost $500. The line has capacity to produce the PHead at 100 units per week and the H-Head at 80 units per week.
Kingston Manufacturing has just shut down for the week and the line has been producing the PHead. The manager wants to plan production and changeovers for the next eight weeks. Currently Buckeye has an inventory of 125 P-Heads and 143 H-Heads. Inventory carrying costs are charged at an annual rate of 19. 5% of the value of inventory. The production cost for the P-Head is $225 and for the H-Head is $310. The objective in developing a production schedule is to minimize the sum of production cost, inventory carrying cost and changeover costs.
Kingston Manufacturing has received the following requirements schedule from its customer (an engine assembler) for the next nine weeks. Week 1 2 3 4 5 6 7 8 9 Product Demand P-Head H-Head 55 38 55 38 44 30 0 0 45 48 45 48 36 58 35 57 35 58 Safety stock requirements are such that week-ending inventory must provide for at least 80% of next week’s demand. You should prepare a production and changeover schedule report for the Kingston Manufacturing management to minimize total costs for the next eight weeks. (Hint: To model the changeover costs, you may introduce a binary decision ???????????? 1, if there is a changeover in week ?????? = 1, … 8. Let a binary variable ???????????? represent a decision whether to produce Pheads (???????????? = 1) or H-heads (???????????? = 0) in week ?????? , ?????? = 1, … 8. Then you need the constraints which say that if you change the production in week ?????? from P-heads to H-heads or H-heads to P-heads, ???????????? must be 1: ???????????? ? ???????????? ? ????????????? 1 and ???????????? ? ????????????? 1 ? ???????????? .) Instructions for Standard Printouts Throughout the course, I will ask for "standard printouts" of your Excel models.
The standard printouts for a model consist of two things. The first is a printout of the model as a set of values, the way it usually appears on the screen. To get this printout, you perform the following steps: • • • • • Go to Print/Page Setup. Click on the Sheet tab. If there is no "X" in the box next to “Gridlines” and "Row and Column Headings", click there so that one appears. Click OK Click on the printer icon in the toolbar, or choose Print... from the file menu to print the spreadsheet. If possible, you should try to make each spreadsheet printout fit on a single page.
Under the Print/Settings select "landscape" orientation, and "fit sheet on one page" before you print. The second printout should be as a set of formulas. It should show the formulas in your spreadsheet; for optimization models (which will be most of our spreadsheets), it should also clearly indicate the target cell, the changing cells, and all constraints. Also indicate whether you are minimizing or maximizing the target cell. To get this printout, follow these steps: • • • Type control-tilde (hold down "ctrl" and type the key marked ` ~) Adjust the column widths so that you can see all the formulas.
Print out the spreadsheet, using the same procedure as above. To indicate the target cell, minimization or maximization, changing cells, and constraints, you may make handwritten notations on this second printout. Alternately, you may make notations using text and graphics on the spreadsheet itself. Excel will let you draw arrows right on your spreadsheet. Points will be deducted if you fail to follow these guidelines. Common errors are forgetting the row and column headings, or not clearly indicating the changing cells, target cell, or constraints. To go back to the values view, type control-tilde