Create an Excel spreadsheet for the financial statements on page 144-146 (Tables IC 4.1, IC 4.2 and IC 4.3). Note for Table IC 4.3 only

Create an Excel spreadsheet for the financial statements on page 144-146 (Tables IC 4.1, IC 4.2 and IC 4.3). Note for Table IC 4.3 only input the industry averages. The individual year data will be calculated in Step 2.
Using cell references calculate the ratios for 2017, 2018 and 2019E.

image text in transcribed

Balance Sheets 2019E 2018 2017 Assets Cash Accounts receivable Inventories Total current assets Gross fixed assets Less accumulated depreciation Net fixed assets Total assets $ 85,632 878,000 1,716,480 $2,680,112 1,197,160 380,120 $ 817,040 $3,497,152 $ 7,282 632,160 1,287,360 $1,926,802 1,202,950 263,160 $ 939,790 $2,866,592 $ 57,600 351,200 715,200 $ 1,124,000 491,000 146,200 $ 344,800 $ 1,468,800 Liabilities and Equity Accounts payable Accruals Notes payable Total current liabilities Long-term debt Common stock Retained earnings Total equity Total liabilities and equity $ 436,800 408,000 300,000 $1,144,800 400,000 1,721,176 231,176 $1,952,352 $3,497,152 $ 524,160 489,600 636,808 $1,650,568 723,432 460,000 32,592 $ 492,592 $2,866,592 $ 145,600 136,000 200,000 $ 481,600 323,432 460,000 203,768 $ 663,768 $ 1,468,800 Note: E indicates estimated. The 2019 data are forecasts. Income Statements 2019E 2018 Sales $7,035,600 5,875,992 550,000 $ 6,034,000 5,528,000 519,988 2017 $ 3,432,000 2,864,000 358,672 Cost of goods sold Other expenses Total operating costs excluding depreciation and amortization EBITDA Depreciation and amortization EBIT $6,425,992 $ 609,608 116,960 $ 492,648 $ 3,222,672 $ 209,328 18,900 Interest expense $ 6,047,988 ($ 13,988) 116,960 ($ 130,948) 136,012 ($ 266,960) (106,784) ($ 160,176) $ 190,428 43,828 $ 146,600 EBT 70,008 $ 422,640 169,056 $ 253,584 Taxes (40%) 58,640 Net income $ 87,960 $ 0.880 $ 0.220 $ 6.638 $ 8.50 EPS $ 1.014 ($ 1.602) DPS $ 0.220 $ 0.110 Book value per share $ 7.809 $ 4.926 Stock price $ 12.17 $ 2.25 Shares outstanding 250,000 100,000 Tax rate 40.00% 40.00% Lease payments $40,000 $ 40,000 0 Sinking fund payments 0 Note: E indicates estimated. The 2019 data are forecasts. The firm had sufficient taxable income in 2016 and 2017 to obtain its full tax refund in 2018. 100,000 40.00% $ 40,000 0 Ratio Analysis 2019E 2018 2017 Current Industry Average 2.7 x 1.2x 2.3x 0.4% 0.8 X 1.0X 4.7 X 4.8 X 6.1 x 38.2 37.4 32.0 6.4x 10.0X 7.0X 2.1X 2.3 X 2.6X Quick Inventory turnover Days sales outstanding (DSO) Fixed assets turnover Total assets turnover Debt-to-capital ratio TIE Operating margin Profit margin Basic earning power ROA 73.4% 44.1% 40.0% -1.0X 4.3% 6.2 X -2.2% 5.5% 7.3% -2.7% 2.6% 3.5% -4.6% 13.0% 19.1% -5.6% 6.0% 9.1% ROE -32.5% 13.3% 18.2% ROIC -4.2% 9.6% 14.5% -1.4x 9.7x Price/earnings Market/book 14.2x 0.5X 1.3X 2.4x Book value per share $4.93 $6.64 n.a. Note: E indicates estimated. The 2019 data are forecasts. Calculation is based on a 365-day year. Balance Sheets 2019E 2018 2017 Assets Cash Accounts receivable Inventories Total current assets Gross fixed assets Less accumulated depreciation Net fixed assets Total assets $ 85,632 878,000 1,716,480 $2,680,112 1,197,160 380,120 $ 817,040 $3,497,152 $ 7,282 632,160 1,287,360 $1,926,802 1,202,950 263,160 $ 939,790 $2,866,592 $ 57,600 351,200 715,200 $ 1,124,000 491,000 146,200 $ 344,800 $ 1,468,800 Liabilities and Equity Accounts payable Accruals Notes payable Total current liabilities Long-term debt Common stock Retained earnings Total equity Total liabilities and equity $ 436,800 408,000 300,000 $1,144,800 400,000 1,721,176 231,176 $1,952,352 $3,497,152 $ 524,160 489,600 636,808 $1,650,568 723,432 460,000 32,592 $ 492,592 $2,866,592 $ 145,600 136,000 200,000 $ 481,600 323,432 460,000 203,768 $ 663,768 $ 1,468,800 Note: E indicates estimated. The 2019 data are forecasts. Income Statements 2019E 2018 Sales $7,035,600 5,875,992 550,000 $ 6,034,000 5,528,000 519,988 2017 $ 3,432,000 2,864,000 358,672 Cost of goods sold Other expenses Total operating costs excluding depreciation and amortization EBITDA Depreciation and amortization EBIT $6,425,992 $ 609,608 116,960 $ 492,648 $ 3,222,672 $ 209,328 18,900 Interest expense $ 6,047,988 ($ 13,988) 116,960 ($ 130,948) 136,012 ($ 266,960) (106,784) ($ 160,176) $ 190,428 43,828 $ 146,600 EBT 70,008 $ 422,640 169,056 $ 253,584 Taxes (40%) 58,640 Net income $ 87,960 $ 0.880 $ 0.220 $ 6.638 $ 8.50 EPS $ 1.014 ($ 1.602) DPS $ 0.220 $ 0.110 Book value per share $ 7.809 $ 4.926 Stock price $ 12.17 $ 2.25 Shares outstanding 250,000 100,000 Tax rate 40.00% 40.00% Lease payments $40,000 $ 40,000 0 Sinking fund payments 0 Note: E indicates estimated. The 2019 data are forecasts. The firm had sufficient taxable income in 2016 and 2017 to obtain its full tax refund in 2018. 100,000 40.00% $ 40,000 0 Ratio Analysis 2019E 2018 2017 Current Industry Average 2.7 x 1.2x 2.3x 0.4% 0.8 X 1.0X 4.7 X 4.8 X 6.1 x 38.2 37.4 32.0 6.4x 10.0X 7.0X 2.1X 2.3 X 2.6X Quick Inventory turnover Days sales outstanding (DSO) Fixed assets turnover Total assets turnover Debt-to-capital ratio TIE Operating margin Profit margin Basic earning power ROA 73.4% 44.1% 40.0% -1.0X 4.3% 6.2 X -2.2% 5.5% 7.3% -2.7% 2.6% 3.5% -4.6% 13.0% 19.1% -5.6% 6.0% 9.1% ROE -32.5% 13.3% 18.2% ROIC -4.2% 9.6% 14.5% -1.4x 9.7x Price/earnings Market/book 14.2x 0.5X 1.3X 2.4x Book value per share $4.93 $6.64 n.a. Note: E indicates estimated. The 2019 data are forecasts. Calculation is based on a 365-day year