or Ton Advanced Problems for Spreadsheet Application the asian many che uily bar the luit theice pran bur wacho The BOE Bound by dividing net income by basil againy Calculus them time Use an equal whichring of rich company for the butuary wage thur s bus graph to dhiplay the change in the ROE sset the pears for each of the firms The bar graph should where's each company grouped together each prut and rich year displayed sneak unct Whereis ranier In a spreadsheet, enter the income isdemand dies and the bal- ance sheet data for Proctor & Gimble and Johnwon & Hehason for the put three years Ignore minority interest for this exercise Compare the temp companies using commean-sire statements for each of the three years tice Figures 14 3 and 14.4). Then calculate the financial ration for Proctor & Gumble and Johnson & Johnson and complete the bry ration in Title 147 for the most recent three years. Be sure to lock up the outstanding shares for each company. Which ration have changed the mass over the year! For the Income statement and balance sheet, use the information from Yahoo and use only the revenues and expenses from Yahoo and calculate the EDIT and net income from the provided numbers. You will have a difference with the reported net income in Yahoo, For inventory turnover, bir the operating es- penses as the coul of goods sold. Mini-Cape Cranston Dispensers, Inc.: Part 2 improving the company's financial performance To the Chapter 13 mini-car, you learned that pelithe to its competitors, She bur already reviewed Cranston Diremen, Inc. manufactures specialized Cramton's working capital management policies and pump and spray containers for a variety of products in will continue ber analysis with a thorough review of the cosmetics, household cleaning supplies, and phar. the financial wabreath nouccutical industries. She had gathered financial Malements for the three For most of 2009 and 2010, the price of most renal years. Her assistant also found a ratio Cranston's shares was falling while shares of other analysis for fiend food and 1007 and updated industry companies in the industry were rising The CFO benchmarks from published sources has charged Susan McNulty, the recently appointed Continued treasurer, with diagnosing Cranston's problems andChapter 14 . Financial Ratios and Firm Performance Income Statements $1.740.00 ADDrum 51.784.00 42.172.00 $ 904,00 Count of goods wold Gras profit [maron) Selling general, and 5 590100 $ 478.00 Administrathe copies $ 20 5 100 00 1 14700 Depreciation $ 323.00 EHIT 34.70 14.30 6 297.50 284.70 $398 50 Famings barbare tor 85.10 5 11955 89.19 $ 278.95 $ 208,11 $ 198.59 Net income Balance Shouts 2010 2009 ASLIS 2010 2008 LIABILITIES Current Busch Current liabilities Accounts payable $ 332.00 $ 285,00 $ MAM $ 341.00 $ 276.00 5 23500 Accrued expenses $ 343.00 $ 335.00 short-term debt $ 503.00 $ 491.00 Accounts receivable 5 721.00 $ 612.00 4 320.00 Total current $1,178.00 51,114.00 Inventory $ 595/00 $ 512.00 5 38800 Long-term debt $ 398.00 $ 324.00 Other liabilities $ 239.00 Total current assets $ 154 00 51.658.00 $1,430.00 $ 944.00 $ 1470 Total liabilities $1,815.00 $1,592.00 $1,07500 Net fixed assets 51,822.00 $1,691.00 |$1,572.00 OWNERS' EQUITY Common equity $1.665.00 $1,529.00 $1,410 TOTAL LIABILITIES TOTAL ASSETS AND OWNER'S $3,480 00 $3,121.00 52.51600 EQUITY $3.480.00 $3,121.00 $2,51600 Additional Information 2010 2009 Stock price per share 2008 530.00 $38.00 Dividend per share $32.00 $ 100 5 0 84 Shares outstanding (millions) $ 0.70 143 143 143Questions Mini-Cast 1. Following are Cranston's common-vine income statements and balance sheets for 2009 and 200. Prepare common. size statements for 2010. 2. Complete the moog table of financial ration for Crandon. Income Statements Sales Cou of gooda sold 100 09% 10 0 0PM Gross profit Selling, penerel, and administrative expenses Depreciation 14.954 EHIT Interest capente (net of interms income) Earnings before taxes Taxes 2.79% Net income 7.20%% Balance Shouts ASSETS 1010 2009 LIABILITIES 2010 2009 Currentlyists Current liabilities 9234 Accrued payables 10 73% Cash and cash items Short Brim debt 15.79% Total current 20.5716 1272% HizbMitica 35 69% 25,40% Accounts receivable Long-term debt 11.49% 15.42% Other liabilities Inventory fe g.. deferred tours Total liabilities 51.01%% 37.52% Total current assets OWNERS EQUITY Common equity 20.15%% 21.50%% 54-18% Net fixed assets Total owners' equity 48.99% 57.27% TOTAL LIABILITIES AND OWNERS 100.005 EQUITY 1001009% 100.00% TOTALASSETS ContinandIndustry 2. 10 Liquidity 128 1.10 Current matic 0.83 0.39 Cwith ratio 0.26 Financial leverage ration Pa 34.83 35.04 Asset management 5.20 Imemory tumover 78 30 TO.19 86 04 Dayi colas in Inventory 6.81 Receivables lumover 53.60 Days sales in receivables LIO LBO Total masel Minorw Profabilly malios Fatum on botul quite 78915 15/48% Aidum on equa 1:361% 13.78 Market Camings per share (EPS $ 1.46 $ 1.39 Not meaningful Price taminge (RE 26-1 23.04 21.00 Earnings growth rate 18.00% PEG 5.415 1.32 1.17 Book value $10.60 $10,08 Not meaningful Markovbook 3.68 3.18 4.26 3. Use the common-size statements and the ratio analysis that you have prepared to comment whether they are better or worse than the 2010 ea Cranston's industry averages. a. liquidity 4. Express Cranston's ROE in terms of the DuPont b. solvency Identity. Which ratios are contributing to G asset management Cranston's below average ROE? d profitability 5. Based on your analyses In Questions I through 4. c. market performance why do you think Cranston's recent stock perfor- Indicate whether the ratios are improving or mance has been disappointing? deteriorating over the three- year period and