assignment must be done in Excel showing all calculations

assignment must be done in Excel showing all calculations


Instructions Please complete the following: · Problem P15-1 · Problem P15-4 · Problem P15-13 · Problem P16-2 · Case - Kanton Company Carry all calculations to TWO decimal places to reduce rounding error, 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 correct, 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 arrived 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 arrived 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. b. 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. b. 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? b. 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. b. 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 Borrowing Arrangement. 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 borrowing arrangement 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 borrowing 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 borrowed would be required. The prime rate is currently 7%. Both the line-of-credit agreement and the revolving credit agreement would have borrowing limits of $1,000,000. For purposes of his analysis, Morton estimates that Kanton will borrow $600,000 on the average during the year, regardless of which financing strategy and loan arrangement 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 current assets to total $4 million throughout the year, determine the average amount of net working capital under each financing strategy. (Hint: Current 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 borrow an average of $600,000, which borrowing arrangement would you recommend to Kanton? Why? Instructions Please complete the following: · Problem P15-1 · Problem P15-4 · Problem P15-13 · Problem P16-2 · Case - Kanton Company Carry all calculations to TWO decimal places to reduce rounding error, 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 correct, 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 arrived 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 arrived 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. b. 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. b. 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? b. 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. b. 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 Borrowing Arrangement. 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 borrowing arrangement 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 borrowing 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 borrowed would be required. The prime rate is currently 7%. Both the line-of-credit agreement and the revolving credit agreement would have borrowing limits of $1,000,000. For purposes of his analysis, Morton estimates that Kanton will borrow $600,000 on the average during the year, regardless of which financing strategy and loan arrangement 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 current assets to total $4 million throughout the year, determine the average amount of net working capital under each financing strategy. (Hint: Current 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