Help with excel sheet and formuals

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) 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 631220. 3) Once you have demand and capacity you know the implied utilization. 3. You can use the same formula to complete the interview, UW, Approved and QA columns. 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 70minutes (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 631220. 3) Once you have demand and capacity you know the implied utilization. 3. 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. Read at least chapter 3 thoroughly. The problem 3.2 (on which you have video as well) is very much like (a simplified form of) this case. Understand this video thoroughly. Exhibit 1: Overview of the loan processing operation Exhibit 2: Detailed analysis of the interviewing activity. The white bars correspond to the performance of the top quartile performance interviewers (person 2 and 3 on the team). The shaded bars correspond to the performance of the bottom performance interviewers (persons 4 and 6 on the team). Total activity time for one additional right party connect is 22 (24) minutes for the top (bottom) quartile interviewer. This case was developed solely as the basis for class discussion. It is not intended to serve as an endorsement, source of primary data or illustration of effective or ineffective management. All data in the case has been disguised. This case was developed solely as the basis for class discussion. It is not intended to serve as an endorsement, source of primary data or illustration of effective or ineffective management. All data in the case has been disguised. Exhibit 6: Attrition losses starting with 100 applications. Data assumes the current handling policies and the current wait times - 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. - Implied utililization = ? - Now I have given enough hints, read the case, the chapters and the questions. You should be able to answer all the questions I asked. 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) 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 631220. 3) Once you have demand and capacity you know the implied utilization. 3. You can use the same formula to complete the interview, UW, Approved and QA columns. 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 70minutes (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 631220. 3) Once you have demand and capacity you know the implied utilization. 3. 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. Read at least chapter 3 thoroughly. The problem 3.2 (on which you have video as well) is very much like (a simplified form of) this case. Understand this video thoroughly. Exhibit 1: Overview of the loan processing operation Exhibit 2: Detailed analysis of the interviewing activity. The white bars correspond to the performance of the top quartile performance interviewers (person 2 and 3 on the team). The shaded bars correspond to the performance of the bottom performance interviewers (persons 4 and 6 on the team). Total activity time for one additional right party connect is 22 (24) minutes for the top (bottom) quartile interviewer. This case was developed solely as the basis for class discussion. It is not intended to serve as an endorsement, source of primary data or illustration of effective or ineffective management. All data in the case has been disguised. This case was developed solely as the basis for class discussion. It is not intended to serve as an endorsement, source of primary data or illustration of effective or ineffective management. All data in the case has been disguised. Exhibit 6: Attrition losses starting with 100 applications. Data assumes the current handling policies and the current wait times - 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. - Implied utililization = ? - Now I have given enough hints, read the case, the chapters and the questions. You should be able to answer all the questions I asked