Line ItemsYear 1Year 2Year 3Year 4Year 5Net Sales$150,000.00$140,000.00$160,000.00$130,000.00$170,000.00Less: Cost of Goods Sold($80,000.00)($75,500.00)($90,500.00)($70,000.00)($75,000.00)Gross Margin$0.00$0.00$0.00$0.00$0.00Less: Operating Expenses($40,000.00)($45,000.00)($41,500.00)($11,000.00)($37,500.00)($55,000.00)Less: Taxes($12,000.00)($10,400.00)($10,500.00)($15,000.00)Net Income$0.00$0.00$0.00$0.00$0.00 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 percentages 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-52d586.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.Copy column A from the Financial Statement Data sheet to Column A in your new sheet. Also copy the information in row 1 to row 1 in your new sheet so that thebasic structure of the table will be the same in the new sheet but it will have no data.a. Now create formulas in your new sheet using the data in the Financial Statement Data sheet that will reflect the correct percentages as described in theprevious question. Instead of creating every formula one by one, how could you create just one formula in cell B2 that can be copied and pasted to all cells?Note that there are negative values in the Financial Statement Data sheet. Choose the correct formula.I. ='Financial Statement Data'!$B2/'Financial Statement Data'!B2II. ='Financial Statement Data'!B2/'Financial Statement Data'!$B2III. ='Financial Statement Data'!B2/'Financial Statement Data'!B$2IV. ='Financial Statement Data'!B2/'Financial Statement Data'!$B$2II - Vb. Copy and paste that one formula to all the relevant cells in your new sheet. Round your answers to two decimal places.Line ItemsYear 1Year 2Year 3Year 4Year 5Net Sales%%%%%Less: Cost of Goods Sold%%%%%Gross Margin%%%%Less: Operating Expenses%%%%%Less: Taxes%%%%Net Income%%%

Common size financial statement analysis is one of the analysis done on financial statements in the…

b. Copy and paste that one formula to all the relevant cells in your new sheet. Round your answers to two decimal places. Line Items Year 1 Year 2 Year 3 Year 4 Year 5 Net Sales % % % % Less: Cost of Goods Sold % % % Gross Margin % % % % % Less: Operating Expenses % % % % Less: Taxes % % % % Net Income % % % % %

b. Copy and paste that one formula to all the relevant cells in your new sheet. Round your answers to two decimal places. Line Items Year 1 Year 2 Year 3 Year 4 Year 5 Net Sales % % % % Less: Cost of Goods Sold % % % Gross Margin % % % % % Less: Operating Expenses % % % % Less: Taxes % % % % Net Income % % % % %

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.
To get an overall picture of each company's capital structure, it is helpful to look at a the Key Ratios screen and then select the Financial Health tab. Common size balance sheet data are provided over a 10-year period. What, if any, are the major trends that emerge when you're looking at those data? Do those companies tend to have relatively high or relatively low levels of debt? Do these companies have significant levels of current liabilities? Have their capital structures changed over time? Use online resources to work on this chapter's questions. Please note that website information changes over time, and these changes may limit your ability to answer some of these questions. This chapter provides an overview of the effects of leverage and describes the process that firms use to determine their optimal capital structure. The chapter also indicates that capital structures tend to vary across industries and across countries. If you are interested in exploring these differences in more detail, the Morningstar website provides information about the capital structures of each of the companies it follows. The following discussion questions demonstrate how we can use this information to evaluate the capital structures for four restaurant companies: Cheesecake Factory (CAKE), Chipotle Mexican Grill (CMG), Ruby Tuesday (RT), and O'Charley's Inc. (CHUX).
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
You have been asked by your CEO to evaluate, analyse and calculate commonly used ratios relating to a company’s profitability, liquidity, solvency and management efficiency. Requirement: ⦁ Complete the balance sheet and sales data (fill in the blanks), using the following financial data: Debt/net worth 60%Acid test ratio 1.2Asset turnover 1.5 timesDay sales outstanding in accounts receivable 40 daysGross profit margin 30%Inventory turnover 6 times Balance sheet Cash ________ Accounts payable ________Accounts receivable ________ Common stock RM15,000Inventories ________ Retained earnings RM22,000Plant & equipment ________ Total assets ________ Total liabilities ________& capitalSales ________Cost of goods sold ________ ⦁ Explain how do analysts use ratios to analyse a firm’s leverage? Which ratios convey more important information to a credit analyst those revolving around the levels of indebtedness or those measuring the ability to service debt? What is the relationship between…
Jorge Martinez is thinking about buyingan existing printing business and hasbeen carefully studying the records of thebusiness to get a good handle on its historicalfinancial performance. Jorge heardthat you are taking a class in entrepreneurshipand asks you, “What suggestionsdo you have for me to make the best use ofthis financial information (i.e., three yearsof audited income statements, balancesheets, and statements of cash flow)?”What suggestions would you give Jorge formaking the maximum use of the financialstatements?
Natalie and Curtis have comparative balance sheets and income statements for Cookie & Coffee Creations, Inc. They have been told that they can use these financial statements to prepare horizontal and vertical analyses, to calculate financial ratios, to analyze how their business is doing, and to make some decisions they have been considering. Below, you are provided with the balance sheet and income statement of Cookie & Coffee Creations Inc. for its first year of operations; the year ended October 31, 2021. Review the calculations below, and then review the additional case information to calculate the ratios.(REFER TO ATTACHED IMAGE) Review the additional case information below. Natalie and Curtis are thinking about borrowing an additional $20,000 to buy more equipment. The loan would be repaid over a 4-year period. The terms of the loan provide for equal semi-annual installment payments of $2,500 on May 1 and November 1 of each year, plus interest of 5% on the outstanding…
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 $ 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…
This question requires an analysis of financial statements from annual reports of companies. Complete the analysis by incorporating the three dimensions of ratios which are liquidity, profitability, and solvency. As an investor, why you prefer this company to invest in? based on ratios and horizontal & vertical analysis of company the company that i’ve been made, justify your answer.
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…