I need help creating and using formulas for the needed excel sheet.

Exhibit 3: Minutes per application spent at the underwriting step. The white bars correspond to the performance of the top quartile performance interviewers (person 1 and 5 on the team). The shaded bars correspond to the performance of the bottom performance interviewers (persons 2 and 3 on the team). The total activity time per application results from adding up the activity times from the six steps. - I have mentioned in the instructions for capital one case that you need analysis, this is a hint (or a partial example) of how it can look. - I supposed 300 as monthly demand, that is just my plugin number for this example, you should do your own calculaton. - The number of activities are clearly given in process steps in Exhibit 1, I took five activies for demonstration purpose. - According to the Exhibit 6, If you start with 100 interviews you end-up booking just 26 loans. So number of flow of applications I have shown here reflect that Attrition rate. - Demand is in minutes. So capacity is in minutes as well - Capacity = number worker* work minutes per months. Look at the case and think how many hours a day and how many days a month the work. Step by step explanation of the spreadsheet hint. 1. According to the spreadsheet I provided, there are four steps 1) Interview 2) Underwriting 3) Approval 4) QA 5) Closing If you complete these four steps you finish the process of providing the loan. You can take these four steps 2. I have (almost) complete the closing column. 1) According to it if you want to give 300 loans per month a) You need 380 closing (why? Look at exhibit 6) Exhibit 6: Attrition losses starting with 100 applications. Data assumes the current handling policies and the current wait times xhibit 1: Overview of the loan processing operation Exhibit 7: Impact on wait times on conversion Exhibit 8: Inventory levels across process steps over the course of the last six months Exhibit 4: Minutes per application spent at the QA step. 2. I have (almost) complete the closing column. 1) According to it if you want to give 300 loans per month a) You need 380 closing (why? Look at exhibit 6) a. According to the exhibit 6 if 33.4 loans are passed only 26.4 are actually booked. So you need to pass around 22% more than you actually give loans. b) Process time for per application is 70 minutes (I got that average from exhibit 5) c) The total minutes required is 26568(379.570). That is how many minutes are in demand per month. 2) Here is the capacity side a. You have 6 workers in closing ( I got that from exhibit 1) b. Each of them work for 312 minutes in a day (This is the average time workers in closing work per day. I got this from reading the case) c. You can find out how many minutes they work per minute in a month. That is capacity. For example, if they work 20 days a month. Then it should be 6*312*20. 3) Once you have demand and capacity you know the implied utilization. You can use the same formula to complete the interview, UW, Approved and QA columns. 4. Then answer those 10 questions. You do not need to answer those in the same order as I asked. The questions are meant to help you analyze the problems