Capacity Analysis and beat mix. Must be completed in Excel. No table was provided for the problem. The time period is one week. This is all of the information provided.
I have completed the scenarios and am attempting to check my work. Will provide excel screenshots if needed. Thank you!
*best mix. Sorry!
Part It: Consider a production system for making two products, P and Q. The relevant data about demand, selling prices, costs, and resource levels are summarized in the table below. Demand for products P and Q are 150 and 100 units respectively per week; the selling price for Pis $100 per unit and for Q it is $80 per unit. P requires one unit of raw material 1 at a cost of $20 and one unit of raw material 2 at a cost of $10. Q requires one unit of raw material 2 at a cost of $10 and one unit of raw material 3 at a cost of $15. An unlimited supply of all three raw materials is available. Scenario One: There are two work centers for the production of P and Q. Both P and Q require a total of 5 minutes of time in work center A. In work center B, product P requires 6 minutes of production, and Q requires 5 minutes. The total capacity of both work centers is constrained to 2400 minutes per period. Operating expenses are $10,000 Carry out a capacity analysis for this small production process with two work centers. Note that, since there are unlimited raw materials, the only possible constraint would be in processing time. In order to meet demand for Products A and P, what is the total minutes required for the two workstations? What percent of available capacity is used? Is there a constraint that must be managed in order to be able to meet demand? If so, which work station is the constraint? Given the production requirements, perform a profit analysis for the best mix to meet demand: Re-do your capacity analysis for Scenario 2. Since there is unlimited raw materials, the only possible constraint is in processing time. In order to meet demand for Products A and P, what is the total minutes required for the two workstations? What percent of available capacity is used? Is there a constraint that must be managed in order to be able to meet demand? If so, which work station is the constraint? Apply the five focusing steps of TOC. o Identify the constraint. o What is the capacity at the constraint? o Which product has the higher margin per unit of resource? So, what is your best production product mix in order to subordinate everything to the constrained resource? (Hint: To compute the return per constraint unit, first identify the constraint; then, determine the minutes required per unit produced of each type at the constraint: finally, given the contribution margin per unit, what is the dollar return per unit of constraint used? Based on these margins, how would you allocate the capacity to the production of P and Q?) questions in a word doc. B is the constraint and the product mix is to make all of P and use the remaining time to make 75 of Q. Thanks. Product Units Time at A (in minutes) Time at B (in minutes) Total Time at A Total Time at B Combined Time 2 150 5 6 750 900 1650 3 100 5 5 500 500 1000 4 Totals 250 10 11 1250 1400 2650 5 6 7 Machine Demand Utilization (2400 a week) 8 1250 52.08% 9 B 1400 58.33% 10 AB 2650 110.42% Selling Price per Unit Number of Units . Raw Material 1 Raw Material 2 6 Raw Material 3 7 Gross-Raw 8 Processing Time 9. Gross Profit per Minute 10 Total Processing Time 11 Operating Expenses 12 Net Profit 13 Product P $100 150 $20 $10 0 $70 11 $6.36 1650 $5,000 $5,500 Product a $80 75 0 $10 $15 $55 10 $5.50 750 $5,000 15875) Net Profit for both P and Q per week $4,625
