Our hypothetical company, The Sweater Company (TSC),
Added on - 18 Sep 2019
Our hypothetical company, The Sweater Company (TSC), manufacturesand 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 theproducts in each of the three production departments is shown in thetable below:DepartmentLeisure SweaterLetter SweaterCutting13Sewing22Finishing13Each product must be processed by each one of the three departmentsbefore it's complete and ready for sale. Reading the table above, forexample, each letter sweater must spend three hours in the CuttingDepartment, two hours in the Sewing Department, and three hours in theFinishing Department before it’s complete. The supervisors of the threedepartments have estimated that the following numbers of labor hourswill be available during the next monthdepartmenthourscutting900sewing1,600finishing2,000Use Microsoft Excel to solve this problem. Remember the concepts ofuseful information (accurate and formatted). The spreadsheet mustcorrectly solve the problem _and_ be easy to read and understandable.Create a spreadsheet which will allow someone to play a “what if” gameto maximize the profit. The user will input the number of leisure sweatersand the number of letter sweaters to produce. The spreadsheet willdetermine the profit generated by that “mix” of leisure and lettersweaters. For example, if 50 leisure sweaters and 20 letters sweaters areproduced, the profit would be (50 * $3) + (20 * $6). The user can thenchange the number of each sweater type produced and see if a betterprofit figure is produced. However, there are constraints on the number ofsweaters that can be produced, as given in the labor-hours table. Thecompany can’t produce 2,000 letter sweaters as each takes 3 hours offinishing work and there are only 2,000 hours of finishing work available.Your spreadsheet needs to enforce these constraints. If the user entersnumbers that result in too many hours of labor, the spreadsheet will alertthe user to that fact. Your spreadsheet will be used by the plant managerto determine the “best” number of leisure sweaters and letter sweaters tomanufacture in the next month, given the constraints, that will maximizeprofits. The model will allow the manager to experiment with a variety ofproduct mix scenarios.