To improve quality and reduce the number of defective ErgoBeds produced, Karake, Inc. management has hired 5 new quality control personnel. Remember from the previous narrative (Project 1) that the company has recently been experiencing increased demand for its ErgoBeds Line and was forced to increase the number of shifts in its factories which led to a problem in quality and increase in the number of defectives and returns. Due to increased demand, the company opened new production facilities and hired new production managers as well as more employees for customer support. Given the negative goodwill caused by the problem with production quality, Karake Inc. has decided to offer customers quantity discount on the ErgoBeds Line. Discounts are based on the quantity ordered as per the “Discount_Table” in range R1:S7 on the Transaction Sheet of the Excel data associated with this project (Project 2_Data_Winter 2022). What the “Discount_Table” indicates is that if the order is less than 33 beds (X<33), then the customer will not get a quantity discount; if the order is equal to or more than 33 but less than 83 (33<=X<83), then the customer will get a 2.7% discount, etc. Use the data in the Transaction sheet to answer the following questions:
1. During the ordering process of the Ergobeds, some customers have entered in their addresses with minor typos. Their most common errors are extra spaces and incorrect capitalization. Use the appropriate Text function(s) in column M to revise the addresses. (0.5 point).
2. Calculate Revenues of each sale transaction in column J and create a formula using SUMPRODUCT to get the Total Revenues in cell R12. Take returns into consideration for both calculations (quantity is computed net of returns). (1 point)
3. In column K, calculate the discounted price based on the table in the R1:S7 range. (0.5 points) Karake, Inc. is planning to bring to market a new line of products called ErgoChairs. Zaid Smart, the company’s financial analyst, needs to determine if this is a worthwhile investment. Zaid estimated the project’s initial capital outlay at $9.7 million, which will cover the cost of renovating a proposed manufacturing facility that will be leased, and the cost of new machinery to produce the new product line. Zaid has listed the projected returns over the next 11 years. The expected annual cash inflows from the project can be found on the ErgoChair Evaluation sheet of the Excel file. One critical element that is left to be analyzed is the funding required to produce the ErgoChairs. Before making a final decision to go with the project, Zaid must evaluate the proposed investment to determine if it is economically feasible. Khiem Williams, the VP of Finance, was tasked with the job of exploring several funding scenarios and generating cash flow projections to be provided to management before a final decision is made. Based on Khiem’s analysis of several financing options for the ErgoChairs Line, he decided to recommend the following option to finance the initial capital outlays for this project: “Fund the project by borrowing the money from Hogan Bank. The bank currently charges an interest rate (APR) of 9.5 percent compounded annually and requires the payments to be made annually over the next 11 years. Given that Karake, Inc. has been doing business with Hogan Bank for the past 30 years I am confident the loan will be approved.”
4. Use a simple Excel function to calculate the expected Net Present Value of the ErgoChairs investment. Show your answer in cell E5 of the ErgoChairs Evaluation Sheet. (0.5 Points) Refer to the Financial Analysis sheet to complete questions 5-8
. 5. Use a simple excel function to compute the Annual Payment in cell B5. (0.5 Points)
6. Complete the Amortization Table (C11-H22). Use simple excel functions to complete IPMT & PPMT columns (1 Points)
7. In cell F25, use an Excel built-in function to compute the total interest paid over the last 6 years of the life of the loan. (0.5 Points)
8. In cell G23, use an Excel function to compute the total principal paid over the first 7 years of the life of the loan. (0.5 points) Refer to the Questions 9&10 sheet to complete questions 9-10.
9. Assume you decided to go with the Loan from Hogan Bank, but you cannot afford to pay more than $1,250,000 per period (annually). You are willing to negotiate the interest rate with the bank, though. Using a What-If-Analysis application, how much should you ask the bank to charge in interest in order to make sure that your periodic payment will be $1,250,000? Assume payments are still made annually for the next 11 years. Make sure to explain how you arrived at your answer in cell D4 and if it makes sense relative to your original answer (which function(s)/ analysis models did you use? What inputs did you use?). Additionally, record your target interest rate in cell B12 to 2 decimal places. Make sure to revert all loan inputs back to their original values after you have recorded the target interest rate into its corresponding cell (1 Point).
10. Let’s assume the bank is unwilling to reduce the interest rate but is willing to work with you on increasing the periods over which the loan is fully paid to ensure that your periodic payment is set at $1,250,000. Given a loan of $9,700,000 and an interest rate (APR) of 10% paid annually, compute the number of periods over which the loan is to be amortized. Make sure to explain how you arrive at your answer in cell D13 and if it makes sense relative to your original answer (which function(s)/analysis models did you use? What inputs did you use?). Additionally, record your target periods in cell B13 to 2 decimal places. (1 Point)
11. Create a What-If Analysis table that shows the impact on the periodic payment of changing the interest rate of the loan (APR) between 7% to 12% with an increment of 0.5% and the value of the loan from $9 million to $13.5 million with an increment of $500,000. Assume payments are still made annually over the next 11 years. Use the sheet named Question 11 to complete this question. (Use the range D4:O15 to answer this question) (1 Points)