What I would like help with is creating the spreadsheet.

Blue Note Mortgage Case Study

The process of obtaining a mortgage for a house or condominium is more complex than most people think. It starts with an application that contains all pertinent information about the borrower that the lender will need. The bank or mortgage company then initiates a process that leads to a loan decision. It is here that key information about the borrower is provided by third-party providers. This information includes a credit report, verification of income, verification of assets, verification of employment, and an appraisal of the property, among others. The result of the processing function is a complete loan file that contains all the information and documents needed to underwrite the loan, which is the next step in the process. Underwriting is where the loan application is evaluated for its risk. Underwriters evaluate whether the borrower can make payments on time, can afford to pay back the loan, and has sufficient collateral in the property to back up the loan. In the event the borrower defaults on the loan, the lender can sell the property to recover the amount of the loan. But if the amount of the loan is greater than the value of the property, the lender cannot recoup their money. If the underwriting process indicates that the borrower is creditworthy, has the capacity to repay the loan, and that the value of the property in question is greater than the loan amount, then the loan is approved and will move to closing. Closing is the step where the borrower signs all the appropriate papers agreeing to the terms of the loan.

Beverly Frydann, the manager of a loan-processing department, needs to know how many employees will be needed over the next several months to process a certain number of loan files per month so she can better plan capacity. Staffing changes are expensive and time-consuming. Thus, it is quite important to understand staffing requirements well in advance. In many cases, the time to hire and train new employees can be 90 to 180 days, so it is not always possible to react quickly to changes in staffing needs. Hence, advance planning is vital so that Beverly can make good decisions about overtime or reductions in work hours, or adding or reducing temporary or permanent staff.

Suppose that there are different types of products that require processing. A product could be a 30-year, fixed-rate mortgage, 7/1 ARM, FHA loan, or a construction loan. Each of these loan types vary in their complexity and require different levels of documentation and, consequently, have different times to complete. Assume that the manager forecasts 750 loan applications in May, 825 in June, 900 in July, and 775 in August. Each employee works productively for 7 hours each day, and there are 22 working days in May, 20 in June, 22 in July, and 22 in August. Beverly also knows, based on historical loan data, the percentage of each product type and how long it takes to process one loan of each type. These data are presented in Exhibit 14.38 (See Screenshot below).

Beverly would like to predict the number of full-time equivalent (FTE) staff needed each month to ensure that all loans can be processed. Determine how to calculate the FTE required in order to provide sufficient resources to meet all production requirements. 1) Develop a spreadsheet model that Beverly can use to easily update the product mix for other months with different numbers of working days.

2) Use the model to determine how many FTE staff are required in May through August.

3) What types of aggregate planning strategies might Beverly use in this situation? First, read the Blue Note Mortgage case study in Chapter 14, pages 449 - 450 of the course textbook, taking note of the key issues in the case. The case also requires lean operating principles found in Chapter 18 as well as OM concepts found throughout the textbook. Follow the requirements listed below. This is an APA formatted research paper. Develop an introductory summary paragraph identifying the key operational issues in the case. Answer the following questions fully, applying correct concepts and terminology. Develop a spreadsheet model that Beverly can use to easily update the product mix for other months with different number of working days. Use the model to determine how many FTE staff are required in May through August. What type of aggregate planning strategies might Beverly use in this situation? If you were to design a mortgage company based only upon the four principles of lean operating systems - elimination of waste, increased speed and response, improved quality, and reduced cost - what would it look like? Incorporate OM concepts and methods used throughout this textbook, such as mission and strategy, competitive priorities, process type, service guarantees, supply chain, and so on as appropriate, into your discussion. Provide cited support from the case and chapter readings as appropriate. Additional research outside of the textbook may be required. Conclude with a brief paragraph stating what you learned from the case and how it helped you understand specific concepts from the reading. Support your conclusion with specific examples and use appropriate course terminology.

Loan Processing Data