Karake, Inc. management
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)
