Case Description: 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. 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. 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. (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] (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] 2. Using the data provided, your second comparison looks at the 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 15t column, calculate Current Liabilities/EBIT for each company. In the 2nd column, calculate Total Debt/EBIT for each company. (3.2) Based on the Morton's 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 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] 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. I Based on ALL of your analysis of Morton's performance, relative to its peers, do you believe that the data supports an increase to Morton's credit limit? Please provide 2 or 3 sentences to summarize your rationale. [Your answer should be entered directly into the Gradescope Assignment for this question] 5. Upload your completed Excel file, which should now be populated with 4 new columns of data and shnuld have 3 new tabs containing the charts requested in the preceding questions. Case Description: 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. 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. 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. (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] (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] 2. Using the data provided, your second comparison looks at the 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 15t column, calculate Current Liabilities/EBIT for each company. In the 2nd column, calculate Total Debt/EBIT for each company. (3.2) Based on the Morton's 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 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] 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. I Based on ALL of your analysis of Morton's performance, relative to its peers, do you believe that the data supports an increase to Morton's credit limit? Please provide 2 or 3 sentences to summarize your rationale. [Your answer should be entered directly into the Gradescope Assignment for this question] 5. Upload your completed Excel file, which should now be populated with 4 new columns of data and shnuld have 3 new tabs containing the charts requested in the preceding questions