Maximizing Profit for The Sweater Company


Added on  2019-09-18

2 Pages569 Words146 Views
Our hypothetical company, The Sweater Company (TSC), manufactures and sells two products: leisure sweaters and letter (athletic) sweaters. Thecompany makes a profit of $6 for each letter sweater sold and a profit of $3 for each leisure sweater sold. The labor-hour requirements for the products in each of the three production departments is shown in the table below:DepartmentLeisure SweaterLetter SweaterCutting13Sewing22Finishing13Each product must be processed by each one of the three departments before it's complete and ready for sale. Reading the table above, for example, each letter sweater must spend three hours in the Cutting Department, two hours in the Sewing Department, and three hours in the Finishing Department before it’s complete. The supervisors of the three departments have estimated that the following numbers of labor hours will be available during the next monthdepartmenthourscutting900sewing1,600finishing2,000Use Microsoft Excel to solve this problem. Remember the concepts of useful information (accurate and formatted). The spreadsheet must correctly solve the problem _and_ be easy to read and understandable.Create a spreadsheet which will allow someone to play a “what if” game to maximize the profit. The user will input the number of leisure sweaters and the number of letter sweaters to produce. The spreadsheet will determine the profit generated by that “mix” of leisure and letter sweaters. For example, if 50 leisure sweaters and 20 letters sweaters are produced, the profit would be (50 * $3) + (20 * $6). The user can then change the number of each sweater type produced and see if a better profit figure is produced. However, there are constraints on the number ofsweaters that can be produced, as given in the labor-hours table. The company can’t produce 2,000 letter sweaters as each takes 3 hours of finishing work and there are only 2,000 hours of finishing work available. Your spreadsheet needs to enforce these constraints. If the user enters numbers that result in too many hours of labor, the spreadsheet will alert the user to that fact. Your spreadsheet will be used by the plant manager to determine the “best” number of leisure sweaters and letter sweaters tomanufacture in the next month, given the constraints, that will maximize profits. The model will allow the manager to experiment with a variety of product mix scenarios.
Maximizing Profit for The Sweater Company_1

End of preview

Want to access all the pages? Upload your documents or become a member.