PLEASE POST EXCEL FORMULAS :)
B C D E G . N 1 Project Two Part Four (20 points) 2 Pennhurst, Inc. manufactures a high-quality office desk, which is offered in both oak and walnut. The demand for the desks is 1000 units for the oak desk and 1500 for the walnut desk. The desks are priced at $560 and $990 for the oak and walnut desks, respectively. The materials cost is $270 for the oak desk and $450 for the walnut desk. In addition, the company has the following information for the four manufacturing processes (receiving, preparation, assembly and finishing) showing the amount of time (in minutes) required for each desk for each process, and the total minutes of time available for each process. 6 Time Required/unit Time Required/unit Time 7 Name Oak Walnut Available 8 Receiving 25 50 120,000 an 9 Preparation 80 120 320,000 10 Assembly 120 200 400,000 11 Finishing 60 40 150,000 12 13 Required: 14 1. Which process(es), if any, are constraints for the current level of demand? 15 2. Given any constraints identified in part (1) above, determine the best production and sales plan for the two types of tables. 16 This will be accomplished by performing all three steps based upon the structure below: (1) Identify the constraint (2) Identify the Most Profitable Product 17 and (3) Identify the Most Profitable Product Mix 18 4. Complete step four by addressing the two questions related the theory of constraints. 19 20 Answer to Part One: 21 22 23 Answer to Part Two: : 24 Step One: Identify the Constraint - Total Time Required for Each Activity for Given Demand 25 Time Slack 2 26 Oak Walnut Total Time Available Time 27 Receiving 28 Preparation 29 Assembly 30 Finishing 31 32 33 Step Two: Identify the Most Profitable Product 34 35 Oak Walnut 36 Price 37 Materials Cost 38 Throughput Margin 39 Constraint Time 40 Throughput/Time 41 42 Step Three: Identify the Most Profitable Product Mix 43 Oak 44 Walnut 45 46 Oak Walnut Total 47 Units 48 Throughput/unit 49 Total Throughput 50 51 Step Four: Use five to six sentences to address the following: 52 1. Explain the usefulness of the theory of constraints in the determination of the most profitable product mix for this furniture manufacturer. 53 2. Describe some ideas for improving the production process based upon the constraint, so the company can improve the total number of units produced for both products. B C D E G . N 1 Project Two Part Four (20 points) 2 Pennhurst, Inc. manufactures a high-quality office desk, which is offered in both oak and walnut. The demand for the desks is 1000 units for the oak desk and 1500 for the walnut desk. The desks are priced at $560 and $990 for the oak and walnut desks, respectively. The materials cost is $270 for the oak desk and $450 for the walnut desk. In addition, the company has the following information for the four manufacturing processes (receiving, preparation, assembly and finishing) showing the amount of time (in minutes) required for each desk for each process, and the total minutes of time available for each process. 6 Time Required/unit Time Required/unit Time 7 Name Oak Walnut Available 8 Receiving 25 50 120,000 an 9 Preparation 80 120 320,000 10 Assembly 120 200 400,000 11 Finishing 60 40 150,000 12 13 Required: 14 1. Which process(es), if any, are constraints for the current level of demand? 15 2. Given any constraints identified in part (1) above, determine the best production and sales plan for the two types of tables. 16 This will be accomplished by performing all three steps based upon the structure below: (1) Identify the constraint (2) Identify the Most Profitable Product 17 and (3) Identify the Most Profitable Product Mix 18 4. Complete step four by addressing the two questions related the theory of constraints. 19 20 Answer to Part One: 21 22 23 Answer to Part Two: : 24 Step One: Identify the Constraint - Total Time Required for Each Activity for Given Demand 25 Time Slack 2 26 Oak Walnut Total Time Available Time 27 Receiving 28 Preparation 29 Assembly 30 Finishing 31 32 33 Step Two: Identify the Most Profitable Product 34 35 Oak Walnut 36 Price 37 Materials Cost 38 Throughput Margin 39 Constraint Time 40 Throughput/Time 41 42 Step Three: Identify the Most Profitable Product Mix 43 Oak 44 Walnut 45 46 Oak Walnut Total 47 Units 48 Throughput/unit 49 Total Throughput 50 51 Step Four: Use five to six sentences to address the following: 52 1. Explain the usefulness of the theory of constraints in the determination of the most profitable product mix for this furniture manufacturer. 53 2. Describe some ideas for improving the production process based upon the constraint, so the company can improve the total number of units produced for both products
