Using financial statements for home depot and the 2 Excel template provided, you build a balance sheet and income statement, complete common size, common base year, and combined common size & base year analysis, and complete ratio analysis for several ratios using the ratio analysis worksheets provided in this document. Detailed Instructions: Financial statement must be most recent e.g year 2023 2)Obtain a copy of the companys core balance sheet, income statement, and statement of cash flows for the most recent 2 years. For this activity, identify the most recent year as Year 2 and the second most recent year as Year 1. 3)Build a new Excel model of the companys balance sheet and income statement for years 1 and 2 using the template provided. Years 1 and 2 dollar data should be provided in two adjacent columns in your Excel model. You may consolidate some line items on the publicly available statements if appropriate. If the statements are too abbreviated, you may have to investigate the firms annual report to gather a bit more detail to make your analysis meaningful. For example, your Balance Sheet should include the main sub-categories of Current Assets and should NOT simply list the total current assets without including the basic main components. 4)Build a common size balance sheet analysis using the Excel template on the same worksheet page as your balance sheet, in the columns to the right of the dollar value entries for the balance sheet. Include common base year analysis, as well as combined common size common base year analysis. See definitions below from the homework document. Thus, on a single balance sheet worksheet (tab) you should have: 1) the dollar values, 2) the common size values, 3) the common base year values and 3) the combined common size common base year values in columns to the right of the dollar values. Use Excel formulas for the computations. 5) Build a common size income statement analysis using the Excel template provided. Include common base year analysis, as well as combined common size common base year analysis. you should have: 1) the dollar values, 2) the common size values, 3) the common base year values and 3) the combined common size common base year values in columns to the right of the dollar values. Use Excel formulas for the computations. 6) Complete the ratio analysis worksheets provided at the end of this document for at least 5 of the 8 ratios, i.e. 5 of the Ratio 1 Ratio 8 worksheets provided. Note: some example industry average data (upper, middle, and lower quartiles) have been provided, but these are just examples. You may gather other industry average quartile data OR you may assume industry values for illustration (if assumed, then note that). Recall, upper, middle and lower quartile values indicate ranges that 25% (i.e. a quarter) of the reporting data fall. Thus, 25% above the upper quartile, 25% between the upper and middle quartile, 25% between the middle and lower quartile, and 25% below the lower quartile. 7)Complete the ratio analysis worksheet RATIO 9 (Cash Flow from Operations (CFO)Ratios) provided at the very end of this document. 8)Provide a cover page to your report, with all appropriate professional details (i.e. name, date, project title, etc.). Report order: Cover, 2-page summary, then completed ratio worksheets. 9)Provide a 2-page (double spaced) summary of your analysis and key insights. Report order: Cover, 2-page summary, then only your completed ratio analysis worksheets. Ratios 3. Common Size Analysis: Definitions: Common size balance sheet = all balance sheet entries are divided by total assets and thus expressed as a percent of total assets. Common size income statement = all income statement entries are divided by total sales and thus expressed as a percent of total assets. Explain & briefly illustrate how one would compute the common size balance sheet and common size income statement for the most current year for the Global Petroleum Corporation. Explain the value of common size balance sheets and income statements. Ratios 4. Common Base Year Analysis: Definition = each entry for a particular statement or report for a given year is divided by its amount from the base year. Explain & briefly illustrate how one would compute the common base year balance sheet for Global Petroleum Corporation. Explain the value of common base year balance sheets and income statements. Ratios 5. Combined Common Size and Common Base Year Analysis: Definition: A combined common size and common base year balance sheet is essentially the same as the common base year, except one uses the common size statements (i.e. the percent of total asset results) for the inputs, rather than the raw dollar entries. Thus, one takes each common size entry for a given year and divides by its common size entry for the base year. Explain & briefly illustrate how one would compute the combined common size and common base year balance sheet. Explain the value of combined common size and common base year balance sheets and income statements. Ratios 6.What is 1. trend analysis versus 2. industry (a.k.a.cross sectional) analysis? Ratio Analysis Activity Ratio 1 Name & Formula: Current Ratio = CR = CA/CL 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 2.6 Middle quartile 1.8 Lower quartile 1.4 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 2 Name & Formula: Days Cash on Hand = DCH = (Cash & Equivalents) / [(Operating Expenses Depreciation)/365] 1.What does this ratio tell you? 2.In general, would you like this ratio to be higher or lower? Why? 3.Compute this ratio for Year 2: 4.Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5.Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile n/a days Middle quartile n/a Lower quartile n/a 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 3 Name & Formula: Days in Accounts Receivables = DAR = AR/average daily revenues = AR / (revenue/365) 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 43 days Middle quartile 31 Lower quartile 14 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 4 Name & Formula: Debt to Equity = total debt / total equity 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 2.4 Middle quartile 0.78 Lower quartile 0.33 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 5 Name & Formula: Operating Profit Margin = (revenues total operating expenses) / total revenues 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile n/a %, BUT Net PM = 4.9% Middle quartile n/a, BUT Net PM = 1.5% Lower quartile n/a, BUT Net PM = 0.5% 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 6 Name & Formula: Net Profit Margin = net income / total revenues Aka Return on Sales 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 4.9% Middle quartile 1.5 Lower quartile 0.5 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 7 Name & Formula: Return on Assets = net income / total assets 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 10% Middle quartile 3.3 Lower quartile 1.3 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 8 Name & Formula: Return on Equity = net income / total equity 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 12.5% Middle quartile 5.2 Lower quartile 3.5 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 9 Name & Formula: Cash Flow Statement Ratios = Cash Flow from Operations (CFO) relative to other items, such as: Note: CFO = top category of CF Statement. Year 2 Year 1 CFO / Revenue = CFO / Operating Income = CFO / Total Assets = CFO / Total Equity = CFO / Total Debt = 1. In general terms, what do these ratios & ratios like these tell you? 2. In general, would you like these ratios to be higher or lower? Why? 3. Compute these ratios for Years 1 & 2 in the columns above: 4. Trend Analysis: In general terms, did the ratios generally get better or worse? 5. Industry Analysis: Why would it be valuable to evaluate how a firms CFO ratios (such as the ones above) compare with the industry averages for the ratios? 6. What are some risks or concerns if these ratios are TOO LOW? 7. What are some actions that could INCREASE these ratios? 8. What are some risks or concerns if these ratios are TOO HIGH? 9. What are some actions that could DECREASE these ratios? 10. Why would these ratios be commonly considered as key ratios at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence these ratios?

B3 fx Balance Sheet Common Size Analysis \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & F & G & 1 & J & K \\ \hline 3 & 1 & Balance Sheet Common Size Analysis & Yr2\$value & Yr1value & Yr2CS & Yr1CS & CBY & & Combinded CS \& CBY \\ \hline 4 & 2 & B & C & D & E & F & G & & H \\ \hline 5 & 3 & & \multicolumn{2}{|c|}{ Dollar Values } & \multicolumn{2}{|c|}{ Common Size (CS) (\% of TA) } & & & \\ \hline 6 & 4 & & Year 2 & Year 1 & Year 2 (\%of TA) & Year 1 (\% of TA) & Common Base Year (CBY) & & Combined CS \& CBY \\ \hline 7 & 5 & Balance Sheet Accounts & enter date 2 here & enter date 1 here & enter date 2 here & enter date 1 here & Yr2\$value / Yr1\$value & & Yr2CS/Yr1CS \\ \hline 8 & 6 & Asset Accounts & & & & & & & \\ \hline 9 & 7 & & & & & & & & \\ \hline 10 & 8 & & & & & & & & \\ \hline 11 & 9 & & & & & & & & \\ \hline 12 & 10 & & & & & & & & \\ \hline 13 & 11 & & & & & & & & \\ \hline 14 & 12 & & & & & & & & \\ \hline 15 & 13 & & & & & & & & \\ \hline 16 & 14 & & & & & & & & \\ \hline 17 & 15 & & & & & & & & \\ \hline 18 & 16 & & & & & & & & \\ \hline 19 & 17 & & & & & & & & \\ \hline 20 & 18 & & & & & & & & \\ \hline 21 & 19 & & & & & & & & \\ \hline 22 & 20 & & & & & & & & \\ \hline 23 & 21 & & & & & & & & \\ \hline 24 & 22 & Note: Add or delete lines as appropriate & & & & & & & \\ \hline 25 & 23 & & & & & & & & \\ \hline 26 & 24 & & & & & & & & \\ \hline 27 & 25 & Liabilities, Debt, and Equity Accounts & & & & & & & \\ \hline & > & Balance Sheet Analysis & Analysis & as desired & + & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 3 & 1 & Income Statement Common Size Analysis & Yr2\$value & Yr1\$value & Yr2CS & Yr1CS & CBY & Combinded CS \& Cl \\ \hline 4 & 2 & B & C & D & E & F & G & H \\ \hline 5 & 3 & & \multicolumn{2}{|c|}{ Dollar Values } & \multicolumn{2}{|c|}{ Common Size (CS) (\% of Sales) } & & \\ \hline 6 & 4 & & Year 2 & Year 1 & Year 2 (\%of Sales) & Year 1 (\%of Sales) & Common Base Year (CBY) & Combined CS \& CB \\ \hline 7 & 5 & Income Statement Line Items & enter date 2 here & enter date 1 here & enter date 2 here & enter date 1 here & Yr2\$value / Yr1\$value & Yr2CS/Yr1CS \\ \hline 8 & 6 & & & & & & & \\ \hline 9 & 7 & & & & & & & \\ \hline 10 & 8 & & & & & & & \\ \hline 11 & 9 & & & & & & & \\ \hline 12 & 10 & & & & & & & \\ \hline 13 & 11 & & & & & & & \\ \hline 14 & 12 & & & & & & & \\ \hline 15 & 13 & & & & & & & \\ \hline 16 & 14 & & & & & & & \\ \hline 17 & 15 & & & & & & & \\ \hline 18 & 16 & & & & & & & \\ \hline 19 & 17 & & & & & & & \\ \hline 20 & 18 & & & & & & & \\ \hline 21 & 19 & & & & & & & \\ \hline 22 & 20 & & & & & & & \\ \hline 23 & 21 & & & & & & & \\ \hline 24 & 22 & & & & & & & \\ \hline 25 & 23 & 1 & & & & & & \\ \hline \end{tabular} Balance Sheet Analysis Income Statement Analysis Other as desired B3 fx Balance Sheet Common Size Analysis \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & F & G & 1 & J & K \\ \hline 3 & 1 & Balance Sheet Common Size Analysis & Yr2\$value & Yr1value & Yr2CS & Yr1CS & CBY & & Combinded CS \& CBY \\ \hline 4 & 2 & B & C & D & E & F & G & & H \\ \hline 5 & 3 & & \multicolumn{2}{|c|}{ Dollar Values } & \multicolumn{2}{|c|}{ Common Size (CS) (\% of TA) } & & & \\ \hline 6 & 4 & & Year 2 & Year 1 & Year 2 (\%of TA) & Year 1 (\% of TA) & Common Base Year (CBY) & & Combined CS \& CBY \\ \hline 7 & 5 & Balance Sheet Accounts & enter date 2 here & enter date 1 here & enter date 2 here & enter date 1 here & Yr2\$value / Yr1\$value & & Yr2CS/Yr1CS \\ \hline 8 & 6 & Asset Accounts & & & & & & & \\ \hline 9 & 7 & & & & & & & & \\ \hline 10 & 8 & & & & & & & & \\ \hline 11 & 9 & & & & & & & & \\ \hline 12 & 10 & & & & & & & & \\ \hline 13 & 11 & & & & & & & & \\ \hline 14 & 12 & & & & & & & & \\ \hline 15 & 13 & & & & & & & & \\ \hline 16 & 14 & & & & & & & & \\ \hline 17 & 15 & & & & & & & & \\ \hline 18 & 16 & & & & & & & & \\ \hline 19 & 17 & & & & & & & & \\ \hline 20 & 18 & & & & & & & & \\ \hline 21 & 19 & & & & & & & & \\ \hline 22 & 20 & & & & & & & & \\ \hline 23 & 21 & & & & & & & & \\ \hline 24 & 22 & Note: Add or delete lines as appropriate & & & & & & & \\ \hline 25 & 23 & & & & & & & & \\ \hline 26 & 24 & & & & & & & & \\ \hline 27 & 25 & Liabilities, Debt, and Equity Accounts & & & & & & & \\ \hline & > & Balance Sheet Analysis & Analysis & as desired & + & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 3 & 1 & Income Statement Common Size Analysis & Yr2\$value & Yr1\$value & Yr2CS & Yr1CS & CBY & Combinded CS \& Cl \\ \hline 4 & 2 & B & C & D & E & F & G & H \\ \hline 5 & 3 & & \multicolumn{2}{|c|}{ Dollar Values } & \multicolumn{2}{|c|}{ Common Size (CS) (\% of Sales) } & & \\ \hline 6 & 4 & & Year 2 & Year 1 & Year 2 (\%of Sales) & Year 1 (\%of Sales) & Common Base Year (CBY) & Combined CS \& CB \\ \hline 7 & 5 & Income Statement Line Items & enter date 2 here & enter date 1 here & enter date 2 here & enter date 1 here & Yr2\$value / Yr1\$value & Yr2CS/Yr1CS \\ \hline 8 & 6 & & & & & & & \\ \hline 9 & 7 & & & & & & & \\ \hline 10 & 8 & & & & & & & \\ \hline 11 & 9 & & & & & & & \\ \hline 12 & 10 & & & & & & & \\ \hline 13 & 11 & & & & & & & \\ \hline 14 & 12 & & & & & & & \\ \hline 15 & 13 & & & & & & & \\ \hline 16 & 14 & & & & & & & \\ \hline 17 & 15 & & & & & & & \\ \hline 18 & 16 & & & & & & & \\ \hline 19 & 17 & & & & & & & \\ \hline 20 & 18 & & & & & & & \\ \hline 21 & 19 & & & & & & & \\ \hline 22 & 20 & & & & & & & \\ \hline 23 & 21 & & & & & & & \\ \hline 24 & 22 & & & & & & & \\ \hline 25 & 23 & 1 & & & & & & \\ \hline \end{tabular} Balance Sheet Analysis Income Statement Analysis Other as desired