Great Deal! Get Instant \$10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

# assignment must be done in Excel showing all calculations

Instructions
Â· Problem P15-1
Â· Problem P15-4
Â· Problem P15-13
Â· Problem P16-2
Â· Case - Kanton Company
Ca
y all calculations to TWO decimal places to reduce rounding e
or, especially in multi-step problems.
There might be some problems where the solution will require graphing. This will require using the graphing function in Excel.
If you submit your answer without an Excel graph points will be deducted.
If you submit your answer by drawing a graph even if it is co
ect, points will be deducted.
Remember to show all calculations. You must show your calculation and solution. please see the example below. If you do not show how you a
ived at your answers, you will not receive credit. Merely showing the formula without the calculations is not sufficient. The problems should be set up in columns or using other appropriate formatsâ€”do not hide all steps in formulas behind one answer. I must still be able to see how you a
ived at the answer. EXAMPLE OF HOW WORK SHOULD LOOK Please see below
Â· Problem P15-1
Cash conversion cycle: American Products is concerned about managing cash efficiently. On average, inventories have an age of 80 days, and accounts receivable are collected in 40 days. Accounts payable are paid approximately 30 days after they arise. The firm has annual sales of about \$30 million. Goods sold total \$20 million, and purchases are \$15 million.
a. Calculate the firmâ€™s operating cycle.
. Calculate the firmâ€™s cash conversion cycle.
c. Calculate the amount of resources needed to support the firmâ€™s cash conversion cycle.
d. Discuss how management might be able to reduce the cash conversion cycle.
Â· Problem P15-4
Aggressive versus conservative seasonal funding strategy: Dynabase Tool has forecast its total funds requirements for the coming year as shown in the following table.
a. Divide the firmâ€™s monthly funds requirement into (1) a permanent component and (2) a seasonal component, and find the monthly average for each of these components.
. Describe the amount of long-term and short-term financing used to meet the total funds requirement under (1) an aggressive funding strategy and (2) a conservative funding strategy. Assume that, under the aggressive strategy, long-term funds finance permanent needs, and short-term funds are used to finance seasonal needs.
c. Assuming that short-term funds cost 5% annually and that the cost of long-term funds is 10% annually, use the average found in part a to calculate the total cost of each of the strategies described in part b. Assume that the firm can earn 3% on any excess cash balances.
d. Discuss the profitability-risk tradeoffs associated with the aggressive strategy and those associated with the conservative strategy.
Â· Problem P15-13
Lockbox system Eagle Industries believes that a lockbox system can shorten its accounts receivable collection period by three days. Credit sales are \$3,240,000 per year, billed on a continuous basis. The firm has other equally risky investments that earn a return of 15%. The cost of the lockbox system is \$9,000 per year. (Note: Assume a 365-day year.)
a. What amount of cash will be made available for other uses under the lockbox system?
. What net benefit (cost) will the firm realize if it adopts the lockbox system? Should it adopt the proposed lockbox system?
Â· Problem P16-2
Cost of giving up the early payment discounts Determine the cost of giving up the early payment discount under each of the following terms of sale. (Note: Assume a 365-day year.)
a. 2/10 net 30.
. 1/10 net 30.
c. 1/10 net 45.
d. 3/10 net 90.
e. 1/10 net 60.
f. 3/10 net 30.
g. 4/10 net 180.
Â· Case - Kanton Company
KANTON COMPANY
Selecting Kanton Company's Financing Strategy and Unsecured Short-Term Bo
owing A
angement.
Morton Mercado, the CFO of Kanton Company, carefully developed the estimates of the firm's total funds requirements for the coming year. These are shown in the following table:
In addition, Morton expects short-term financing costs of about 10% and long-term financing costs of about 14% during that period. He developed the three possible financing strategies that follow:
Strategy 1 - Aggressive: Finance seasonal needs with short-term finds and permanent needs with long-term funds.
Strategy 2 - Conservative: Finance an amount equal to the peak need with long-term funds and use short-term funds only in an emergency.
Strategy 3 - Tradeoff: Finance \$3,000,000 with long-term funds and finance the remaining funds requirements with short-term funds.
Using data on the firm's total funds requirements, Morton estimated the average annual short-term and long-term financing requirements for each strategy in the coming year, as shown in the following table.
To ensure that, along with spontaneous financing from accounts payable and accruals, adequate short-term financing will be available, Morton plans to establish an unsecured short-term bo
owing a
angement with its local bank, Third National. The bank has offered either a line-of-credit agreement or a revolving credit agreement. Third National's terms for a line of credit are an interest rate of 2.50% above the prime rate, and the bo
owing must be reduced to zero for a 30-day period during the year. On an equivalent revolving credit agreement, the interest rate would be 3% above prime with a commitment fee of 0.50% on the average unused balance.
Under both loans, a compensating balance equal to 20% of the amount bo
owed would be required. The prime rate is cu
ently 7%. Both the line-of-credit agreement and the revolving credit agreement would have bo
owing limits of \$1,000,000. For purposes of his analysis, Morton estimates that Kanton will bo
ow \$600,000 on the average during the year, regardless of which financing strategy and loan a
angement it chooses. (Note: assume a 365-day year.)
Case Study Questions:
1. Determine the total annual cost of each of the three possible financing strategies.
2. Assuming that the firm expects its cu
ent assets to total \$4 million throughout the year, determine the average amount of net working capital under each financing strategy. (Hint: Cu
ent liabilities equal average short-term financing.)
3. Using the net working capital found in part b as a measure of risk, discuss the profitability-risk trade-off associated with each financing strategy. Which strategy would you recommend to Morton Mercado for Kanton Company? Why?
4. Find the effective annual rate under: 1) the line-of-credit agreement and 2) the revolving credit agreement. (Hint: Find the ratio of the dollars that the firm will pay in interest and commitment fees to the dollars that the firm will effectively have use of.)
5. If the firm expects to bo
ow an average of \$600,000, which bo
owing a
angement would you recommend to Kanton? Why?

Instructions
Â· Problem P15-1
Â· Problem P15-4
Â· Problem P15-13
Â· Problem P16-2
Â· Case - Kanton Company
Ca
y all calculations to TWO decimal places to reduce rounding e
or, especially in multi-step problems.
There might be some problems where the solution will require graphing. This will require using the graphing function in Excel.
If you submit your answer without an Excel graph points will be deducted.
If you submit your answer by drawing a graph even if it is co
ect, points will be deducted.
Remember to show all calculations. You must show your calculation and solution. please see the example below. If you do not show how you a
ived at your answers, you will not receive credit. Merely showing the formula without the calculations is not sufficient. The problems should be set up in columns or using other appropriate formatsâ€”do not hide all steps in formulas behind one answer. I must still be able to see how you a
ived at the answer. EXAMPLE OF HOW WORK SHOULD LOOK Please see below
Â· Problem P15-1
Cash conversion cycle: American Products is concerned about managing cash efficiently. On average, inventories have an age of 80 days, and accounts receivable are collected in 40 days. Accounts payable are paid approximately 30 days after they arise. The firm has annual sales of about \$30 million. Goods sold total \$20 million, and purchases are \$15 million.
a. Calculate the firmâ€™s operating cycle.
. Calculate the firmâ€™s cash conversion cycle.
c. Calculate the amount of resources needed to support the firmâ€™s cash conversion cycle.
d. Discuss how management might be able to reduce the cash conversion cycle.
Â· Problem P15-4
Aggressive versus conservative seasonal funding strategy: Dynabase Tool has forecast its total funds requirements for the coming year as shown in the following table.
a. Divide the firmâ€™s monthly funds requirement into (1) a permanent component and (2) a seasonal component, and find the monthly average for each of these components.
. Describe the amount of long-term and short-term financing used to meet the total funds requirement under (1) an aggressive funding strategy and (2) a conservative funding strategy. Assume that, under the aggressive strategy, long-term funds finance permanent needs, and short-term funds are used to finance seasonal needs.
c. Assuming that short-term funds cost 5% annually and that the cost of long-term funds is 10% annually, use the average found in part a to calculate the total cost of each of the strategies described in part b. Assume that the firm can earn 3% on any excess cash balances.
d. Discuss the profitability-risk tradeoffs associated with the aggressive strategy and those associated with the conservative strategy.
Â· Problem P15-13
Lockbox system Eagle Industries believes that a lockbox system can shorten its accounts receivable collection period by three days. Credit sales are \$3,240,000 per year, billed on a continuous basis. The firm has other equally risky investments that earn a return of 15%. The cost of the lockbox system is \$9,000 per year. (Note: Assume a 365-day year.)
a. What amount of cash will be made available for other uses under the lockbox system?
. What net benefit (cost) will the firm realize if it adopts the lockbox system? Should it adopt the proposed lockbox system?
Â· Problem P16-2
Cost of giving up the early payment discounts Determine the cost of giving up the early payment discount under each of the following terms of sale. (Note: Assume a 365-day year.)
a. 2/10 net 30.
. 1/10 net 30.
c. 1/10 net 45.
d. 3/10 net 90.
e. 1/10 net 60.
f. 3/10 net 30.
g. 4/10 net 180.
Â· Case - Kanton Company
KANTON COMPANY
Selecting Kanton Company's Financing Strategy and Unsecured Short-Term Bo
owing A
angement.
Morton Mercado, the CFO of Kanton Company, carefully developed the estimates of the firm's total funds requirements for the coming year. These are shown in the following table:
In addition, Morton expects short-term financing costs of about 10% and long-term financing costs of about 14% during that period. He developed the three possible financing strategies that follow:
Strategy 1 - Aggressive: Finance seasonal needs with short-term finds and permanent needs with long-term funds.
Strategy 2 - Conservative: Finance an amount equal to the peak need with long-term funds and use short-term funds only in an emergency.
Strategy 3 - Tradeoff: Finance \$3,000,000 with long-term funds and finance the remaining funds requirements with short-term funds.
Using data on the firm's total funds requirements, Morton estimated the average annual short-term and long-term financing requirements for each strategy in the coming year, as shown in the following table.
To ensure that, along with spontaneous financing from accounts payable and accruals, adequate short-term financing will be available, Morton plans to establish an unsecured short-term bo
owing a
angement with its local bank, Third National. The bank has offered either a line-of-credit agreement or a revolving credit agreement. Third National's terms for a line of credit are an interest rate of 2.50% above the prime rate, and the bo
owing must be reduced to zero for a 30-day period during the year. On an equivalent revolving credit agreement, the interest rate would be 3% above prime with a commitment fee of 0.50% on the average unused balance.
Under both loans, a compensating balance equal to 20% of the amount bo
owed would be required. The prime rate is cu
ently 7%. Both the line-of-credit agreement and the revolving credit agreement would have bo
owing limits of \$1,000,000. For purposes of his analysis, Morton estimates that Kanton will bo
ow \$600,000 on the average during the year, regardless of which financing strategy and loan a
angement it chooses. (Note: assume a 365-day year.)
Case Study Questions:
1. Determine the total annual cost of each of the three possible financing strategies.
2. Assuming that the firm expects its cu
ent assets to total \$4 million throughout the year, determine the average amount of net working capital under each financing strategy. (Hint: Cu
ent liabilities equal average short-term financing.)
3. Using the net working capital found in part b as a measure of risk, discuss the profitability-risk trade-off associated with each financing strategy. Which strategy would you recommend to Morton Mercado for Kanton Company? Why?
4. Find the effective annual rate under: 1) the line-of-credit agreement and 2) the revolving credit agreement. (Hint: Find
Answered 1 days After Feb 15, 2024

## Solution

Prince answered on Feb 17 2024
P15-1
Given
Average inventory age    80
Accounts receivable collection period    40
Accounts payable payment period    30
Annual sales    \$30
Total goods sold    \$20
Total purchases    \$15
1. Calculate the Operating Cycle    120    Days
2. Cash Conversion Cycle    90    Days
3. Resource Need
Inventory balance    \$4.38    million
Receivable balance    \$3.29    million
Payable Balance    \$1.23    million
4. To reduce the cash conversion cycle, management could focus on optimizing inventory management, negotiating better payment terms with suppliers, and implementing efficient accounts receivable practices. By improving inventory turnover, negotiating longer payment terms with suppliers, and encouraging prompt payment from customers, the firm can reduce the time it takes to convert sales into cash, ultimately improving cash flow and operational efficiency. Additionally, implementing technology solutions for faster order processing and invoicing can also contribute to a shorter cash conversion cycle.
P15-4
Part A:
Month    Total funds requirement    Permanent Component    Seasonal Components
January    \$2,000,000    \$2,000,000    \$0
Fe
uary    \$2,000,000    \$2,000,000    \$0
March    \$2,000,000    \$2,000,000    \$0
April    \$4,000,000    \$2,000,000    \$2,000,000
May    \$6,000,000    \$2,000,000    \$4,000,000
June    \$9,000,000    \$2,000,000    \$7,000,000
July    \$12,000,000    \$2,000,000    \$10,000,000
August    \$14,000,000    \$2,000,000    \$12,000,000
September    \$9,000,000    \$2,000,000    \$7,000,000
October    \$5,000,000    \$2,000,000    \$3,000,000
November    \$4,000,000    \$2,000,000    \$2,000,000
December    \$3,000,000    \$2,000,000    \$1,000,000
Total        \$24,000,000    \$48,000,000
Average        \$2,000,000    \$4,000,000
Average permanent requirement = \$2,000,000
Average seasonal requirement = \$4,000,000
Part B:
In a aggressive approach, the company intends to secure loans ranging from \$1,000,000 to \$12,000,000...
SOLUTION.PDF