Show me steps on this Excel

This problem involves a cost-benefit process analysis for laying out a facility. The process involves a "Chipotle-style" line whereby customers arrive, order food, and move through the system. Your objective is to staff the system with the optimal number of employees, given an estimate of how many customers will be arriving per hour. Each of your employees is paid $11 hour, and each customer's food order averages $9.80, out of which $1.3 is net profit. The starting point for analysis is when you staff the system with one employee (@ $11 /hour; we will treat revenue and costs as separate). This system is depicted below: the single employee takes care of the entire transaction in 150 seconds for each customer. For now, assume that the demand rate (i.e., the arrival rate in this system) is at least as fast as the maximum rate at which the system can operate. To put it a little differently, we are assuming that the system is in steady-state. (a.) Find the maximum output rate for this system, and the corresponding net profit with the single employee. Now you will consider adding a second employee to staff the system. It turns out that the 150 seconds for the entire transaction (currently depicted by task A above) cannot be divided equally, as you might have hoped. Instead, it can be broken down into two tasks, or steps, B and C, depicted below. Please note that we are not adding any extra time for the customer to transition from step B to step C, although in real life it might take time to do this. In this sense, the process we depict is "frictionless." While obviously this process has a higher output rate than the one in (a.), once again we will assume that the demand is more than sufficient, i.e., we will consider the process in steady state for now. (b.) As in (a.), find the maximum output rate for this system and the corresponding net profit. Will your process benefit (monetarily) from the addition of the second employee? Why or why not? (c.) Once again, you will consider applying the principle of division of labor yet again. Task B above is the bottleneck and you will consider breaking it down into two steps. As it turns out, you are able to modify the process as follows (below). Now, with three employees (each performs one step), and in a "frictionless" steady-state, find the maximum output rate for this system and the corresponding profit. Does the process benefit from adding the third employee? (d.) Finally, you want to continue the division of labor and the natural task to break down is F. (Why?) But the best you can do is to break F down into two tasks that take 15 and then 40 seconds. (I don't think we need another diagram here!) Once again, in steady-state, demonstrate whether it is worth it (in the same cost/benefit (e.) If you find in (d.) that the additional profit does not exceed the extra $11 in employee compensation (for the fourth staff member), then show what the per customer profit will have to increase to, to make the fourth employee worthwhile. 2 (f.) The whole point of this exercise is to enable you, as managers of such a system, to plan for staffing the system dynamically, that is, to staff the system differently as its characteristics change over time. So, consider three different times of day (e.g., morning, midday, and evening) which have different customer arrival rates. Suppose that the arrival rate in the morning is 22 customers per hour; that the midday arrival rate is 67/hour; and that the evening arrival rate is 45 per hour. For each of these three scenarios, and for the costs and profits originally provided, what are the optimal employee staffing levels? Bottleneck/Capacity Management Employee Pay Food Order Average Net Profit per transaction \begin{tabular}{|r|} \hline$11.00 \\ \hline$9.80 \\ \hline$1.30 \\ \hline \end{tabular} A. 1 employee Seconds Per Transaction Max Output Rate Sales Profit Net profit - employee expense \begin{tabular}{|l|l|l|} \hline & Transactions Per hour & 3600 seconds/seconds per transaction \\ \hline & Net Sales Profit & transactions*net profit \\ \hline & net Profit & transactions*net profit per transaction - employee exp. \end{tabular} B. 2 employees Bottleneck (seconds) Max Output Rate Sales Profit Net profit -employee expense \begin{tabular}{|l|l} \hline & Transactions Per hour \\ \hline & Net Sales Profit \\ \hline & net Profit \end{tabular} 24 C. 3 employees 25 Bottleneck (seconds) 26 Max Output Rate Transactions Per hour 27 Sales Profit Net Sales Profit 28 Net profit - employee expense net Profit 29 31 D. 4 employees at least what cost would 4 employees be profitable? This problem involves a cost-benefit process analysis for laying out a facility. The process involves a "Chipotle-style" line whereby customers arrive, order food, and move through the system. Your objective is to staff the system with the optimal number of employees, given an estimate of how many customers will be arriving per hour. Each of your employees is paid $11 hour, and each customer's food order averages $9.80, out of which $1.3 is net profit. The starting point for analysis is when you staff the system with one employee (@ $11 /hour; we will treat revenue and costs as separate). This system is depicted below: the single employee takes care of the entire transaction in 150 seconds for each customer. For now, assume that the demand rate (i.e., the arrival rate in this system) is at least as fast as the maximum rate at which the system can operate. To put it a little differently, we are assuming that the system is in steady-state. (a.) Find the maximum output rate for this system, and the corresponding net profit with the single employee. Now you will consider adding a second employee to staff the system. It turns out that the 150 seconds for the entire transaction (currently depicted by task A above) cannot be divided equally, as you might have hoped. Instead, it can be broken down into two tasks, or steps, B and C, depicted below. Please note that we are not adding any extra time for the customer to transition from step B to step C, although in real life it might take time to do this. In this sense, the process we depict is "frictionless." While obviously this process has a higher output rate than the one in (a.), once again we will assume that the demand is more than sufficient, i.e., we will consider the process in steady state for now. (b.) As in (a.), find the maximum output rate for this system and the corresponding net profit. Will your process benefit (monetarily) from the addition of the second employee? Why or why not? (c.) Once again, you will consider applying the principle of division of labor yet again. Task B above is the bottleneck and you will consider breaking it down into two steps. As it turns out, you are able to modify the process as follows (below). Now, with three employees (each performs one step), and in a "frictionless" steady-state, find the maximum output rate for this system and the corresponding profit. Does the process benefit from adding the third employee? (d.) Finally, you want to continue the division of labor and the natural task to break down is F. (Why?) But the best you can do is to break F down into two tasks that take 15 and then 40 seconds. (I don't think we need another diagram here!) Once again, in steady-state, demonstrate whether it is worth it (in the same cost/benefit (e.) If you find in (d.) that the additional profit does not exceed the extra $11 in employee compensation (for the fourth staff member), then show what the per customer profit will have to increase to, to make the fourth employee worthwhile. 2 (f.) The whole point of this exercise is to enable you, as managers of such a system, to plan for staffing the system dynamically, that is, to staff the system differently as its characteristics change over time. So, consider three different times of day (e.g., morning, midday, and evening) which have different customer arrival rates. Suppose that the arrival rate in the morning is 22 customers per hour; that the midday arrival rate is 67/hour; and that the evening arrival rate is 45 per hour. For each of these three scenarios, and for the costs and profits originally provided, what are the optimal employee staffing levels? Bottleneck/Capacity Management Employee Pay Food Order Average Net Profit per transaction \begin{tabular}{|r|} \hline$11.00 \\ \hline$9.80 \\ \hline$1.30 \\ \hline \end{tabular} A. 1 employee Seconds Per Transaction Max Output Rate Sales Profit Net profit - employee expense \begin{tabular}{|l|l|l|} \hline & Transactions Per hour & 3600 seconds/seconds per transaction \\ \hline & Net Sales Profit & transactions*net profit \\ \hline & net Profit & transactions*net profit per transaction - employee exp. \end{tabular} B. 2 employees Bottleneck (seconds) Max Output Rate Sales Profit Net profit -employee expense \begin{tabular}{|l|l} \hline & Transactions Per hour \\ \hline & Net Sales Profit \\ \hline & net Profit \end{tabular} 24 C. 3 employees 25 Bottleneck (seconds) 26 Max Output Rate Transactions Per hour 27 Sales Profit Net Sales Profit 28 Net profit - employee expense net Profit 29 31 D. 4 employees at least what cost would 4 employees be profitable