30 Common-Size Financial Statement Analysis Using ExcelCompleting this activity will help you learn to:1. create Excel formulas using cell references.2. create absolute cell references to perform calculations efficiently and correctly.3. apply conditional formatting to highlight cells based on criteria that you specify.Case scenario: You are working at a small business that has been mostly stable. You want to analyze the financial statements of the past five years to examine thetrends over time and compare the pertentages of income statement items relative to overall sales. You also want to highlight cells according to whether the calculatedratio increases or decreases more than a specified amount.Required:Download spreadsheet CommonSizeCaseData-c9ccd3.xlsxYou will build a spreadsheet that performs common-size analysis using five years of a company's income statements. Specifically, you will create two sheets within theworkbook, one for the horizontal analysis and one for the vertical analysis. Further, you will use conditional formatting to visually differentiate between cells that reflectincreases and cells that reflect decreases over a specified percentage.HINT: STEP-BY-STEP WALKTHROUGH1. Horizontal analysis: Create a new sheet within your Excel workbook that you will use to perform the horizontal analysis, comparing each year's income statementitem to its corresponding item in year 1. ChartTextShapeMediaCommentanLine ItemsNet SalesLess: Cost of Goods SoldGross MarginLess: Operating ExpensesLess: TaxesYear 1Year 2Year 4$220,000.00Year 3$200,000.00Year 5$250,000.00$180,000.00$200,000.00($100,000.00) ($90,500.00) ($100,500.00) ($100,000.00) ($120,000.00)$99,500.00($60,000.00)($16,000.00)$23,500.00$100,000.00($50,000.00) ($55,000.00)($16,000.00) ($14,400.00)$34,000.00$130,000.00($70,000.00)($20,000.00)$40,000.00$89,500.00$120,000.00($65,000.00)($17,000.00)$38,000.00Net Income$20,100.00

Horizontal analysis is one of the financial statement analysis under each increase or decrease is…

HINT: STEP-BY-STEP WALKTHROUGH 1. Horizontal analysis: Create a new sheet within your Excel workbook that you will use to perform the horizontal analysis, comparing each year's income stateme item to its corresponding item in year 1.

HINT: STEP-BY-STEP WALKTHROUGH 1. Horizontal analysis: Create a new sheet within your Excel workbook that you will use to perform the horizontal analysis, comparing each year's income stateme item to its corresponding item in year 1.

Principles of Accounting Volume 2

19th Edition

ISBN:9781947172609

Author:OpenStax

Publisher:OpenStax

Chapter9: Responsibility Accounting And Decentralization

Section: Chapter Questions

Problem 5PB: Financial information for Lighthizer Trading Company for the fiscal year-ended September 30, 20xx,...

See similar textbooks

Similar questions

Financial information for Lighthizer Trading Company for the fiscal year-ended September 30, 20xx, was collected. As part of a management training session, you have been asked to prepare an income statement format that will be used to distribute to management. Subtotals and totals are included in the information, but you will need to calculate the values. A. In the correct format, prepare the income statement using this information: B. Calculate the profit margin, return on investment, and residual income. Assume an investment base of $42,000 and 8% cost of capital. C. Prepare a short response to accompany the income statement that explains why uncontrollable costs are included in the income statement.
Business ratio analysis of financial statements over several years is generally helpful for the following reasons, EXCEPT Seleccione una: a. helps to evaluate management performance b. helps to forecast next year stock price level c. helps to evaluate the overall situation of the business d. helps to detect problem areas e. helps to set realistic future objectives price level
Create a financial statement for your hypothetical new business venture. Using the attached Excel template, and based on surveying a specific market place, make a best-guess estimated statement exhibiting profit and loss. In business, accuracy counts, so make sure to check and recheck your numbers before submitting your financial statements. Your grade will be based on the completeness and accuracy of the Income Statement. This is all of the information that I have. The hypothetical business is for a residential moving company.
a) Conduct and explain all the horizontal and vertical analysis of the company’s financial statements to identify trends and patterns over the past two years. b) Clearly explain all the analysis a of the company’s financial performance, for shareholders and potential investors, using the trends identified in (a) above and in the context of market and other trends and expectations mentioned in the MDA section of the Annual Report. c) Briefly explain how the analysis at (b) would be modified if it was prepared for stakeholders other than shareholders and investors.
Conduct a report and explain at least 4 horizontal and vertical analysis each of the company’s financial statements to identify trends and patterns over the past two years. b) Clearly explain all the analysis a of the company’s financial performance, for shareholders and potential investors, using the trends identified in (a) above and in the context of market and other trends and expectations mentioned in the MDA section of the Annual Report. c) Briefly explain how the analysis at (b) would be modified if it was prepared for stakeholders other than shareholders and investors.
Your company is considering acquiring a private company (New Co., Inc.). The CFO has asked you to review the financial statements, look for key trends, and develop financial/operational questions to ask the management of New Co. when you meet next week. Calculate average collection period, total asset turnover, inventory turnover, and days in inventory. Calculate average collection period, total asset turnover, inventory turnover, and days in inventory for the four years. Assess the trends in activity of the firm, using your calculations in part 1, over the four year period. Calculate the gross profit margin, operating margin, and net profit margin. Assess the profitability trends of the firm, using your calculations in part 3, over the four- year period. Develop three or four questions to ask the management of New Co. to help your management team understand trends uncovered by the ratio analysis. New Co., Inc. Income Statement 12/31/22 12/31/21 12/31/20 12/31/19 Revenue…
You have just been hired as a financial analyst for Lydex Company, a manufacturer of safety helmets. Your boss has asked you to perform a comprehensive analysis of the company’s financial statements, including comparing Lydex’s performance to its major competitors. The company’s financial statements for the last two years are as follows: Lydex CompanyComparative Balance Sheet This Year Last Year Assets Current assets: Cash $ 910,000 $ 1,150,000 Marketable securities 0 300,000 Accounts receivable, net 2,500,000 1,600,000 Inventory 3,550,000 2,000,000 Prepaid expenses 250,000 190,000 Total current assets 7,210,000 5,240,000 Plant and equipment, net 9,420,000 9,000,000 Total assets $ 16,630,000 $ 14,240,000 Liabilities and Stockholders' Equity Liabilities: Current liabilities $ 3,960,000 $ 2,880,000 Note payable, 10% 3,640,000 3,040,000 Total liabilities 7,600,000 5,920,000…
You have just been hired as a financial analyst for Lydex Company, a manufacturer of safety helmets. Your boss has asked you to perform a comprehensive analysis of the company’s financial statements, including comparing Lydex’s performance to its major competitors. The company’s financial statements for the last two years are as follows: Lydex CompanyComparative Balance Sheet This Year Last Year Assets Current assets: Cash $ 980,000 $ 1,220,000 Marketable securities 0 300,000 Accounts receivable, net 2,780,000 1,880,000 Inventory 3,620,000 2,200,000 Prepaid expenses 260,000 200,000 Total current assets 7,640,000 5,800,000 Plant and equipment, net 9,560,000 9,070,000 Total assets $ 17,200,000 $ 14,870,000 Liabilities and Stockholders' Equity Liabilities: Current liabilities $ 4,030,000 $ 3,020,000 Note payable, 10% 3,680,000 3,080,000 Total liabilities 7,710,000 6,100,000 Stockholders' equity: Common stock, $75 par value…
You have just been hired as a financial analyst for Lydex Company, a manufacturer of safety helmets. Your boss has asked you to perform a comprehensive analysis of the company’s financial statements, including comparing Lydex’s performance to its major competitors. The company’s financial statements for the last two years are as follows: Lydex CompanyComparative Balance Sheet This Year Last Year Assets Current assets: Cash $ 870,000 $ 1,110,000 Marketable securities 0 300,000 Accounts receivable, net 2,340,000 1,440,000 Inventory 3,510,000 2,100,000 Prepaid expenses 240,000 180,000 Total current assets 6,960,000 5,130,000 Plant and equipment, net 9,340,000 8,960,000 Total assets $ 16,300,000 $ 14,090,000 Liabilities and Stockholders' Equity Liabilities: Current liabilities $ 3,920,000 $ 2,800,000 Note payable, 10% 3,600,000 3,000,000 Total liabilities 7,520,000 5,800,000 Stockholders' equity: Common stock, $70 par value…
You have just been hired as a financial analyst for Lydex Company, a manufacturer of safety helmets. Your boss has asked you to perform a comprehensive analysis of the company’s financial statements, including comparing Lydex’s performance to its major competitors. The company’s financial statements for the last two years are as follows: Lydex CompanyComparative Balance Sheet This Year Last Year Assets Current assets: Cash $ 870,000 $ 1,110,000 Marketable securities 0 300,000 Accounts receivable, net 2,340,000 1,440,000 Inventory 3,510,000 2,100,000 Prepaid expenses 240,000 180,000 Total current assets 6,960,000 5,130,000 Plant and equipment, net 9,340,000 8,960,000 Total assets $ 16,300,000 $ 14,090,000 Liabilities and Stockholders' Equity Liabilities: Current liabilities $ 3,920,000 $ 2,800,000 Note payable, 10% 3,600,000 3,000,000 Total liabilities 7,520,000 5,800,000 Stockholders' equity: Common stock, $70 par value…
You have just been hired as a financial analyst for Lydex Company, a manufacturer of safety helmets. Your boss has asked you to perform a comprehensive analysis of the company’s financial statements, including comparing Lydex’s performance to its major competitors. The company’s financial statements for the last two years are as follows: Lydex CompanyComparative Balance Sheet This Year Last Year Assets Current assets: Cash $ 870,000 $ 1,110,000 Marketable securities 0 300,000 Accounts receivable, net 2,340,000 1,440,000 Inventory 3,510,000 2,100,000 Prepaid expenses 240,000 180,000 Total current assets 6,960,000 5,130,000 Plant and equipment, net 9,340,000 8,960,000 Total assets $ 16,300,000 $ 14,090,000 Liabilities and Stockholders' Equity Liabilities: Current liabilities $ 3,920,000 $ 2,800,000 Note payable, 10% 3,600,000 3,000,000 Total liabilities 7,520,000 5,800,000 Stockholders' equity: Common stock, $70 par value…
You have just been hired as a financial analyst for Lydex Company, a manufacturer of safety helmets. Your boss has asked you to perform a comprehensive analysis of the company’s financial statements, including comparing Lydex’s performance to its major competitors. The company’s financial statements for the last two years are as follows: Lydex CompanyComparative Balance Sheet This Year Last Year Assets Current assets: Cash $ 960,000 $ 1,260,000 Marketable securities 0 300,000 Accounts receivable, net 2,700,000 1,800,000 Inventory 3,900,000 2,400,000 Prepaid expenses 240,000 180,000 Total current assets 7,800,000 5,940,000 Plant and equipment, net 9,300,000 8,940,000 Total assets $ 17,100,000 $ 14,880,000 Liabilities and Stockholders' Equity Liabilities: Current liabilities $ 3,900,000 $ 2,760,000 Note payable, 10% 3,600,000 3,000,000 Total liabilities 7,500,000 5,760,000 Stockholders' equity: Common stock, $78 par value…