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.

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

Based on the above case, please complete the following questions: 1. Using the data provided, the first comparison that youve decided to analyze is the Total Debt Ratio. 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.

(1.1) Using your data from above, create a funnel chart showing each companys name and their total debt ratio. This chart should be clearly labelled, and should be added to a separate labelled tab in your Excel Workbook.

(1.2) Based on the Mortons total debt ratio, relative to its peers, do you believe that the data supports an increase to Mortons credit limit? Please provide 1 or 2 sentences of rationale to justify your thoughts.

2. Using the data provided, your second comparison looks at both the relationships of Morton's current liabilities vs EBIT, and total debt vs EBIT. Add two new columns to your Excel data file. In the 1st column, calculate Current Liabilities/EBIT for each company. In the 2nd column, calculate Total Debt/EBIT for each company.

(2.1) Using your data from above, create 2 stacked bar charts showing each companys name and their respective Current Liabilities/EBIT (1st chart) and Total Debt/EBIT (2nd chart). These charts should be clearly labelled, and should be added to a separate labelled tab in your Excel Workbook (you may add both charts under a single tab).

(2.2) Based on the Mortons Debt/EBIT and Current Liabilities/EBIT, relative to its peers, do you believe that the data supports an increase in Mortons credit limit? Please provide 1 or 2 sentences of rationale to justify your thoughts.

3. Using the data provided, your final comparison considers Morton's EBIT margin, which is calculated as EBIT divided by sales. Add a new column to your Excel data file where you calculate the EBIT margin for each company. (3.1) Using your data from above, create a scatter chart which plots EBIT margin (y-axis) against total assets (x-axis) for each company. This chart should be clearly labeled, and should be added to a separate labelled tab in your Excel Workbook.

(3.2) Based on the Mortons EBIT margin, relative to its peers, do you believe that the data, in the context of the chart that you created, supports an increase to Mortons credit limit? Please provide 1 or 2 sentences of rationale to justify your thoughts.

4. Given all of the data that you have analyzed, you now need to make a final recommendation on whether to increase Morton's credit limit on its line of credit. Based on ALL of your analysis of Mortons performance, relative to its peers, do you believe that the data supports an increase to Mortons credit limit? Please provide 2 or 3 sentences to summarize your rationale