Suppose that you currently work with a team of credit analysts at a bank that is...

Transcribed Image Text:

Suppose that you currently work with a team of credit analysts at a bank that is reviewing a renewal application for an existing line of credit from a client, Morton Construction Ltd. (Morton). Morton builds high-end custom-designed homes. Morton is currently using its maximum allowable amount of line of credit and is asking for an additional amount, up to $1,000,000, to manage its working capital needs. You must decide if Morton should be provided any additional amount or if the existing limits should stay in place. I As part of your due diligence, you are reviewing data from 10 other construction companies located in the same geographic area that provide services ranging from industrial and corporate construction to housing construction in new residential areas, and custom builds. An assistant has provided a file (see "Morton Case Data (Summer 2022).xlsx" attached in Assignments tool on OWL), with comparative data that you have requested for all 10 companies. Note that all the financial data in the file is in thousands of dollars. Your team has been asked to make a recommendation about Morton's request to management. Company Name Solid Home Construction Inc. Lawrence Smit Construction Co. London Construction Co. Smithy Buildings Inc. Morton Construction Peterson & Gordon Limited Kennedy & Sons Inc. Peters and Co. Kinsdale Construction Inc. Waterdown Inc. Harley & Co. Total Assets Current Assets Total Liabilities Current Liabilities Long-term debt Shareholders Equity Sales 3654 9808 4750 8510 2490 2191 1725 3180 4540 6353 1241 8748 3968 3007 1250 6743 2460 3449 1310 I 3598 9988 6908 2943 5653 8161 3212 6494 3672 5568 4239 1147 3391 1889 499 921 1546 1052 1542 1125 1910 765 2493 6512 4753 1786 3641 5092 1378 3020 1575 2394 1369 941 3516 1796 405 361 1551 1027 1834 249 2142 748 1552 2996 2957 1381 3280 3541 351 1186 1326 252 621 1105 3476 2155 1157 2012 3069 1834 3474 2097 3174 2870 5727 9290 EBIT 1380 1. Using the data provided, the first comparison that you've decided to analyze is the Total Debt Ratio (which you studied in Chapter 3). This ratio considers Total Liabilities (Debt) as a percentage of Total Assets. Add a new column to your Excel data file where you calculate the Total Debt Ratio for each company. H (1.1) Using your data from above, create a funnel chart showing each company's name and their total debt ratio. This chart should be clearly labeled, and should be added to a separate labelled tab in your Excel Workbook. Upload a PDF copy of your completed chart. [File upload should be entered directly into the Gradescope Assignment for this question] both the relationships of (1.2) Based on the Morton's total debt ratio, relative to its peers, do you believe that the data supports an increase to Morton's credit limit? Please provide 1 or 2 sentences of rationale to justify your thoughts. [Your answer should be entered directly into the Gradescope Assignment for this question] Suppose that you currently work with a team of credit analysts at a bank that is reviewing a renewal application for an existing line of credit from a client, Morton Construction Ltd. (Morton). Morton builds high-end custom-designed homes. Morton is currently using its maximum allowable amount of line of credit and is asking for an additional amount, up to $1,000,000, to manage its working capital needs. You must decide if Morton should be provided any additional amount or if the existing limits should stay in place. I As part of your due diligence, you are reviewing data from 10 other construction companies located in the same geographic area that provide services ranging from industrial and corporate construction to housing construction in new residential areas, and custom builds. An assistant has provided a file (see "Morton Case Data (Summer 2022).xlsx" attached in Assignments tool on OWL), with comparative data that you have requested for all 10 companies. Note that all the financial data in the file is in thousands of dollars. Your team has been asked to make a recommendation about Morton's request to management. Company Name Solid Home Construction Inc. Lawrence Smit Construction Co. London Construction Co. Smithy Buildings Inc. Morton Construction Peterson & Gordon Limited Kennedy & Sons Inc. Peters and Co. Kinsdale Construction Inc. Waterdown Inc. Harley & Co. Total Assets Current Assets Total Liabilities Current Liabilities Long-term debt Shareholders Equity Sales 3654 9808 4750 8510 2490 2191 1725 3180 4540 6353 1241 8748 3968 3007 1250 6743 2460 3449 1310 I 3598 9988 6908 2943 5653 8161 3212 6494 3672 5568 4239 1147 3391 1889 499 921 1546 1052 1542 1125 1910 765 2493 6512 4753 1786 3641 5092 1378 3020 1575 2394 1369 941 3516 1796 405 361 1551 1027 1834 249 2142 748 1552 2996 2957 1381 3280 3541 351 1186 1326 252 621 1105 3476 2155 1157 2012 3069 1834 3474 2097 3174 2870 5727 9290 EBIT 1380 1. Using the data provided, the first comparison that you've decided to analyze is the Total Debt Ratio (which you studied in Chapter 3). This ratio considers Total Liabilities (Debt) as a percentage of Total Assets. Add a new column to your Excel data file where you calculate the Total Debt Ratio for each company. H (1.1) Using your data from above, create a funnel chart showing each company's name and their total debt ratio. This chart should be clearly labeled, and should be added to a separate labelled tab in your Excel Workbook. Upload a PDF copy of your completed chart. [File upload should be entered directly into the Gradescope Assignment for this question] both the relationships of (1.2) Based on the Morton's total debt ratio, relative to its peers, do you believe that the data supports an increase to Morton's credit limit? Please provide 1 or 2 sentences of rationale to justify your thoughts. [Your answer should be entered directly into the Gradescope Assignment for this question]

Answer rating: 100% (QA)

ANSWER 11 To calculate the Total Debt Ratio for each company a new co... View the full answer