Finance...I need assistance with the highlighted yellow questions in the attached spreadsheet.

HOMEWORK SET 1 TEMPLATE Balance Sheet Cash Short-term investments Accounts receivable Inventories Total current assets Gross fixed assets Less: Accumulated depreciation Net fixed assets Total assets 2012 2013 $9,000 $7,282 48,600 20,000 351,200 632,160 715,200 1,287,360 $1,124,000 $1,946,802 491,000 1,202,950 146,200 263,160 $344,800 $939,790 $1,468,800 $2,886,592 2014e $14,000 $71,632 $878,000 $1,716,480 $2,680,112 $1,220,000 $383,160 $836,840 $3,516,952 Liabilities and Equity Accounts payable Notes payable Accruals Total current liabilities Long-term debt Common stock (100,000 shares) Retained earnings Total equity $145,600 $324,000 200,000 720,000 136,000 284,960 $481,600 $1,328,960 323,432 1,000,000 460,000 460,000 203,768 97,632 $663,768 $557,632 $359,800 $300,000 $380,000 $1,039,800 $500,000 $1,680,936 $296,216 $1,977,152 $1,468,800 $2,886,592 $3,516,952 Total liabilities and equity Income Statements Sales Cost of goods sold except depr. Depreciation and amortization Other expenses Total operating costs EBIT Interest expense EBT Taxes (40%) Net income Other Data Stock price Shares outstanding EPS DPS Tax rate Book value per share Lease payments 2012 2013 $3,432,000 $5,834,400 2,864,000 4,980,000 18,900 116,960 340,000 720,000 $3,222,900 $5,816,960 $209,100 $17,440 62,500 176,000 $146,600 ($158,560) 58,640 -63,424 $87,960 ($95,136) $21,824 2012 $8.50 100,000 $0.88 $0.22 40% $6.64 $40,000 2013 $6.00 100,000 ($0.95) 0.11 40% $5.58 $40,000 2014e $7,035,600 5,800,000 120,000 612,960 $6,532,960 $502,640 80,000 $422,640 169,056 $253,584 $373,584 2014e $12.17 250,000 $1.014 0.22 40% $7.909 $40,000 EBIT Taxes Depreciation NOPAT Operating Current Asset Operating Current Liabilities Net Op Working Capital $939,790 $836,840 Net Operating Capital Change in Net Long-term Capital Free Cash Flow 1,138,600 2,257,632 1,119,032 ($1,108,568) 2,705,520 447,888 ($146,304) Problems 3-4 Ratio Analysis Current Quick Inventory turnover Days sales outstanding (DSO) Fixed assets turnover Total assets turnover Debt ratio Liabilities-to-assets ratio TIE EBITDA coverage Profit margin Basic earning power ROA ROE Price/Earnings (P/E) Price/Cash flow Market/Book Equity Multiplier Du Pont 3. Calculate the 2014 current and quick ratios based on the projected balance sheet and income statement data. 2014 Quick Ratio = 0.9 What can you say about the company's liquidity position in 2014? 8. Use the extended DuPont equation to provide a summary and overview of company's financial condition projected for 2014. 2014 ROE = 15.1% What are the firm's major strengths and weaknesses? $2,608,480 $739,800 $1,868,680 $344,800 2. Suppose Congress changed the tax laws so that Berndt's depreciation expenses doubled. No changes in operations occurred. What would happen to reported profit and to net cash flow? 2.6 $1,926,802 $608,960 $1,317,842 2014e $502,640 ($201,056) 120,000 $301,584 Operating Long-term Assets ($146,304) 2014 Current Ratio = $1,075,400 $281,600 $793,800 2013 $17,440 ($6,976) 116,960 $10,464 Income Statements Sales Cost of goods sold except depr. Depreciation and amortization Other expenses Total operating costs EBIT Interest expense EBT Taxes (40%) Net income 2012 $3,432,000 2,864,000 37,800 340,000 $3,241,800 $190,200 62,500 $127,700 51,080 $76,620 2013 $5,834,400 4,980,000 233,920 720,000 $5,933,920 ($99,520) 176,000 ($275,520) -110,208 ($165,312) ($165,312) 1. What is the free cash flow for 2014? Free Cash Flow for 2014 = Free Cash Flow Calculation 2012 $209,100 ($83,640) 18,900 $125,460 2012 2.3 0.8 4.0 37.4 10.0 2.3 35.6% 54.8% 3.3 2.6 2.6% 14.24% 6.0% 13.3% 9.7 8.0 1.3 3.2 19% 2013 1.5 0.5 4.0 39.5 6.2 2.0 59.6% 80.7% 0.1 0.8 -1.6% 0.60% -3.3% -17.1% -6.3 27.5 1.1 6.3 -21% 2014e 2.578 0.927 3.449 45.550 8.407 2.000 23% 44% 6.283 5.522 3.6% 0.143 7.2% 12.8% 11.998 8.144 1.539 2.092 15% Industry Average 2.7 1 6.1 32 7 2.5 32.00% 50.00% 6.2 8 3.60% 17.80% 9.00% 17.90% 16.2 7.6 2.9 2014e $7,035,600 5,800,000 120,000 612,960 $6,532,960 $502,640 80,000 $422,640 0.4 169,056 $253,584 $68,608 12/8/2012 Mini Case Data Ratio Analysis Projections Current Input Data: Quick 2012 Year-end common stock price Year-end shares outstanding Tax rate Lease payments 2013 2014 $8.50 $6.00 $12.17 Inventory turnover 100,000 100,000 250,000 Days sales outstanding 40% 40% 40% $40,000 $40,000 $40,000 Fixed assets turnover Total assets turnover Debt ratio Liabilities-to-assets ratio Balance Sheets TIE EBITDA coverage Assets Profit margin 0 48600 20000 $9,000 $7,282 $14,000 Basic earning power $48,600 $20,000 $71,632 ROA Accounts receivable $351,200 $632,160 $878,000 ROE Inventories $715,200 $1,287,360 $1,716,480 Price/Earnings (P/E) Total current assets $1,124,000 $1,946,802 $2,680,112 Price/Cash flow Gross Fixed Assets Less Accumulated Dep. Net Fixed Assets Total Assets $491,000 $146,200 $344,800 $1,468,800 $1,202,950 $263,160 $939,790 $2,886,592 $1,220,000 $383,160 $836,840 $3,516,952 Market/Book Liabilities and Equity Accounts payable Notes payable Accruals Total current liabilities Long-term bonds Total liabilities Common stock (100,000 shares) Retained earnings Total common equity Total liabilities and equity $145,600 $200,000 $136,000 $481,600 $323,432 $805,032 $460,000 $203,768 $663,768 $1,468,800 $324,000 $720,000 $284,960 $1,328,960 $1,000,000 $2,328,960 $460,000 $97,632 $557,632 $2,886,592 $359,800 $300,000 $380,000 $1,039,800 $500,000 $1,539,800 $1,680,936 $296,216 $1,977,152 $3,516,952 0 $3,432,000 $2,864,000 $18,900 $340,000 $3,222,900 $209,100 $62,500 $146,600 $58,640 $87,960 $0.880 $0.220 $6.638 48600 $5,834,400 $4,980,000 $116,960 $720,000 $5,816,960 $17,440 $176,000 ($158,560) ($63,424) ($95,136) ($0.951) $0.110 $5.576 20000 $7,035,600 $5,800,000 $120,000 $612,960 $6,532,960 $502,640 $80,000 $422,640 $169,056 $253,584 $1.014 $0.220 $7.909 Cash and equivalents Short-term investments Income Statements Net sales Costs of Goods Sold Except Depr. Depreciation and amortization Other Expenses Total Operating Cost Earnings before interest and taxes (EBIT) Less interest Pre-tax earnings Taxes (40%) Net Income before preferred dividends EPS DPS Book Value Per Share Industry Average 2.7 1 6.1 32 7 2.5 32.00% 50.00% 6.2 8 3.60% 17.80% 9.00% 17.90% 16.2 7.6 2.9