financial analysis with microsoft excel 7th edition mayes solutions manual

Financial Analysis with Microsoft Excel 7th Edition Mayes Solutions Manual Full Download: http://alibabadownload.com/pro...

3 downloads 116 Views
Financial Analysis with Microsoft Excel 7th Edition Mayes Solutions Manual Full Download: http://alibabadownload.com/product/financial-analysis-with-microsoft-excel-7th-edition-mayes-solutions-manual/

Mike Owjai Manufacturing Income Statements For the Years 2013 and 2014 Sales Cost of Goods Gross Profit Depreciation Selling & Admin. Expense Lease Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Earnings per Share

Alternative Solutions:

2014 $2,625,000 1,704,975 920,025 63,000 652,350 48,750 155,925 84,000 71,925 25,174 $46,751

2013 $2,422,500 1,621,875 800,625 53,250 626,250 48,750 72,375 51,000 21,375 7,481 $13,894

35.00% 75,000 $0.62

35.00% 60,000 $0.23

This sample only, Download all chapters at: alibabadownload.com

Sales Retained Earnings

Note: To switch between the original so and those for parts d and e just choose t appropriate problem from the drop

Obviously, the changes propagate to the worksheets as well, so you can switch to to see how they change.

$ $

2,625,000 448,751

switch between the original solution e for parts d and e just choose the ate problem from the drop-down list.

y, the changes propagate to the other ets as well, so you can switch to them w they change.

Original Part D Part E

1 Revenue 2,625,000 2,850,000 2,625,000

Tax Rate 35% 35% 40%

Mike Owjai Manufacturing Balance Sheet For the Year Ended December 31, 2014 2014

2013

Assets Cash Marketable Securities Accounts Receivable Inventory Total Current Assets Gross Fixed Assets Accumulated Depreciation Net Plant & Equipment Total Assets

$39,000 1,826 315,000 386,250 742,076 2,010,000 426,750 1,583,250 $2,325,326

$30,750 15,750 279,000 315,000 640,500 1,627,500 363,750 1,263,750 $1,904,250

Liabilities & Owner's Equity Accounts Payable Accrued Expenses Total Current Liabilities Long-term Debt Total Liabilities Common Stock ($1.00 par) Additional Paid-in-Capital Retained Earnings Total Owner's Equity Total Liab. & Owner's Equity

378,750 26,250 405,000 878,325 1,283,325 75,000 518,250 448,751 1,042,001 $2,325,326

217,500 22,500 240,000 795,750 1,035,750 60,000 406,500 402,000 868,500 $1,904,250

Mike Owjai Manufacturing Common-size Income Statements For the Years 2013 and 2014 Sales Cost of Goods Gross Profit Depreciation Selling & Admin. Expense Lease Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares

2014 100.00% 64.95% 35.05% 2.40% 24.85% 1.86% 5.94% 3.20% 2.74% 0.96% 1.78%

2013 100.00% 66.95% 33.05% 2.20% 25.85% 2.01% 2.99% 2.11% 0.88% 0.31% 0.57%

35.00% 75,000

35.00% 60,000

Mike Owjai Manufacturing Common-size Balance Sheet For the Year Ended December 31, 2014 2014

2013

Assets Cash Marketable Securities Accounts Receivable Inventory Total Current Assets Gross Fixed Assets Accumulated Depreciation Net Plant & Equipment Total Assets

1.68% 0.08% 13.55% 16.61% 31.91% 86.44% 18.35% 68.09% 100.00%

1.61% 0.83% 14.65% 16.54% 33.64% 85.47% 19.10% 66.36% 100.00%

Liabilities & Owner's Equity Accounts Payable Accrued Expenses Total Current Liabilities Long-term Debt Total Liabilities Common Stock ($2 par) Additional Paid-in-Capital Retained Earnings Total Owner's Equity Total Liab. & Owner's Equity

16.29% 1.13% 17.42% 37.77% 55.19% 3.23% 22.29% 19.30% 44.81% 100.00%

11.42% 1.18% 12.60% 41.79% 54.39% 3.15% 21.35% 21.11% 45.61% 100.00%

Mike Owjai Manufacturing Statement of Cash Flows For the Year Ended December 31, 2014 Cash Flows from Operations Net Income $ 46,751 Depreciation Expense 63,000 Change in Marketable Securities 13,924 Change in Accounts Receivable (36,000) Change in Inventory (71,250) Change in Accounts Payable 161,250 Change in Accrued Expenses 3,750 Total Cash Flows from Operations 181,425 Cash Flows from Investing Change in Gross Fixed Assets (382,500) Total Cash Flows from Investing -382,500 Cash Flows from Financing Change in Long-term Debt 82,575 Change in Common Stock ($2 par) 15,000 Change in Additional Paid-in-Capital 111,750 Total Cash Flows from Financing 209,325 Net Change in Cash Balance $ 8,250

Mike Owjai Manufacturing Statement of Cash Flows For the Year Ended December 31, 2014 Cash Flows from Operations % of Sales Net Income 1.78% Depreciation Expense 2.40% Change in Marketable Securities 0.53% Change in Accounts Receivable -1.37% Change in Inventory -2.71% Change in Accounts Payable 6.14% Change in Accrued Expenses 0.14% Total Cash Flows from Operations Cash Flows from Investing Change in Gross Fixed Assets -14.57% Total Cash Flows from Investing Cash Flows from Financing Change in Long-term Debt 3.15% Change in Common Stock ($2 par) 0.57% Change in Additional Paid-in-Capital 4.26% Total Cash Flows from Financing Net Change in Cash Balance

Denominator

6.91%

-14.57%

7.97% 0.31%

1

Winter Park Web Design Income Statements For the Years 2013 and 2014 2014 Sales Cost of Goods Sold Gross Profit Depreciation Expense Selling & Admin Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Outstanding Earnings per Share Dividends per Share Addition to RE per Share

$

280,419 154,229 126,191 5,295 825 120,071 1,020 119,051 44,049 75,002

37.0% 7,500 $10.00 $6.00 $4.00

2013 $

225,000 123,750 101,250 4,646 720 95,884 810 95,074 33,276 61,798

35.0% 7,500 $8.24 $5.00 $3.24

Note: Cells with a green background are the missing cells in the problem.

Winter Park Web Design Balance Sheets For the Years 2013 and 2014 Cash Accounts receivable Inventories Total Current Assets Gross fixed assets Accumulated depreciation Net Fixed Assets Total assets

$

Accounts payable Notes payable Total Current Liabilities Long-term debt Total Liabilities Common stock Additional paid in capital Retained earnings Total Equity Total Liabilities & Equity

$

Notes: Shares Outstanding

2014 15,225 $ 18,756 12,911 46,892 43,530 10,845 32,685 79,577

2013 11,250 16,500 11,325 39,075 13,275 5,550 7,725 46,800

12,302 3,000 15,302 10,673 25,974 7,500 750 45,353 53,603 79,577

10,277 4,500 14,777 8,423 23,199 7,500 750 15,351 23,601 46,800

7,500

$

7,500

Winter Park Web Design Statement of Cash Flows For the Year 2014 Cash Flows from Operations Net Income Depreciation Expense Change in Accounts Receivable Change in Inventories Change in Accounts Payable Total Cash Flows from Operations Cash Flows from Investing

$

75,002 5,295 (2,256) (1,586) 2,025

Change in fixed assets Total Cash Flows from Investing Cash Flows from Financing

(30,255)

Change in Notes Payable Change in Long-Term Debt Change in Common Stock Change in Paid-In Capital Cash Dividends Total Cash Flows from Financing Net Change in Cash Balance

(1,500) 2,250 (45,000)

Check answer against Balance Sheet Beginning Cash From Balance Sheet Ending Cash From Balance Sheet Net Change in Cash Balance

$

78,480

$

(30,255)

$ $

(44,250) 3,975

$

3,975

11,250 15,225

Winter Park Web Design Income Statements For the Years 2013 and 2014 2014 Sales Cost of Goods Sold Gross Profit Depreciation Expense Selling & Admin Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income

100.00% 55.00% 45.00% 1.89% 0.29% 42.82% 0.36% 42.45% 15.71% 26.75%

2013 100.00% 55.00% 45.00% 2.07% 0.32% 42.62% 0.36% 42.26% 14.79% 27.47%

Winter Park Web Design Balance Sheets For the Years 2013 and 2014 Cash Accounts receivable Inventories Total Current Assets Gross fixed assets Accumulated depreciation Net Fixed Assets Total assets

2014 19.13% 23.57% 16.22% 58.93% 54.70% 13.63% 41.07% 100.00%

2013 24.04% 35.26% 24.20% 83.49% 28.37% 11.86% 16.51% 100.00%

Accounts payable Notes payable Total Current Liabilities Long-term debt Total Liabilities Common stock Additional paid in capital Retained earnings Total Equity Total Liabilities & Equity

15.46% 3.77% 19.23% 13.41% 32.64% 9.42% 0.94% 56.99% 67.36% 100.00%

21.96% 9.62% 31.57% 18.00% 49.57% 16.03% 1.60% 32.80% 50.43% 100.00%

Notes: Shares Outstanding

7,500

7,500

Automatic Data Processing, Inc. Income Statements For the Years 2010 to 2014 USD in Million except per share data Jun-14 Jun-13 Revenue 12,206 11,310 Cost of revenue 7,221 6,650 Gross profit 4,985 4,660 Total operating expenses 2,762 2,663 Operating income 2,223 1,997 Interest Expense 6 9 Other income (expense) 58 96 Income before taxes 2,275 2,084 Provision for income taxes 772 720 Net income from continuing operations 1,503 1,364 Net income from discontinuing ops 13 42 Net income 1,516 1,406 Net income available to common shareholders 1,516 1,406 Earnings per share Basic Diluted Weighted average shares outstanding Basic Diluted

Jun-12 10,665 6,240 4,425 2,466 1,959 8 171 2,122 734 1,388

Jun-11 9,880 5,732 4,148 2,323 1,825 9 117 1,933 678 1,255

1,388 1,388

1,255 1,255

Jun-10 8,928 5,030 3,898 2,127 1,771 9 101 1,863 656 1,207 4 1,211 1,211

3.17 3.14

2.91 2.89

2.85 2.82

2.54 2.52

2.42 2.40

479 483

483 487

487 492

494 498

500 504

Note: Both MSN and Yahoo! have recently made it difficult to copy and paste financial statements into Excel. Therefore, I got this data from Morningstar Direct. There may be slight errors due to rounding.

Automatic Data Processing, Inc. Income Statements For the Years 2010 to 2014 USD in Million except per share data Jun-14 Jun-13 Jun-12 Assets Cash and cash equivalents Short-term investments Total cash Receivables Other current assets Total current assets Gross property, plant and equipment Accumulated Depreciation Net property, plant and equipment Equity and other investments Goodwill Intangible assets Other long-term assets Total non-current assets Total assets Liabilities and stockholders' equity Short-term debt Accounts payable Taxes payable Accrued liabilities Deferred revenues Other current liabilities Total current liabilities Non-current liabilities Long-term debt Deferred taxes liabilities Deferred revenues Other long-term liabilities Total non-current liabilities Total liabilities Stockholders' equity Common stock Additional paid-in capital Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity

Jun-11

Jun-10

1,984 2,032 4,016 1,716 20,101 25,833 2,126 (1,348) 778 54 3,114 632 1,641 6,219 32,052

1,699 28 1,727 1,519 22,955 26,201 2,043 (1,314) 729 314 3,053 643 1,329 6,068 32,269

1,548 30 1,578 1,323 22,254 25,155 1,957 (1,251) 706 87 3,155 711 1,000 5,659 30,814

1,389 36 1,425 1,288 25,869 28,582 1,952 (1,236) 716 98 3,074 716 1,051 5,655 34,237

1,643 28 1,671 1,037 19,609 22,317 1,829 (1,155) 674 104 2,383 542 842 4,545 26,862

2,173 170 20 2,022 333 19,190 23,908

157 40 1,811 316 22,409 24,733

168 39 1,654 334 21,045 23,240

153 29 1,489 351 24,765 26,787

150 60 1,220 322 18,301 20,053

12 289 513 660 1,474 25,382

15 234 493 603 1,345 26,078

17 391 468 586 1,462 24,702

34 374 477 556 1,441 28,228

40 306 457 528 1,331 21,384

64 545 13,633 (7,750) 178 6,670 32,052

64 457 13,020 (7,367) 15 6,189 32,267

64 486 12,438 (7,105) 230 6,113 30,815

64 490 11,804 (6,714) 367 6,011 34,239

64 493 11,252 (6,540) 210 5,479 26,863

Automatic Data Processing, Inc. Income Statements For the Years 2010 to 2014 USD in Million except per share data Jun-14 Jun-13 Revenue 100.00% 100.00% Cost of revenue 59.16% 58.80% Gross profit 40.84% 41.20% Total operating expenses 22.63% 23.55% Operating income 18.21% 17.66% Interest Expense 0.05% 0.08% Other income (expense) 0.48% 0.85% Income before taxes 18.64% 18.43% Provision for income taxes 6.32% 6.37% Net income from continuing operations 12.31% 12.06% Net income from discontinuing ops 0.11% 0.37% Net income 12.42% 12.43% Net income available to common shareholders 12.42% 12.43% Earnings per share Basic Diluted Weighted average shares outstanding Basic Diluted

Jun-12 100.00% 58.51% 41.49% 23.12% 18.37% 0.08% 1.60% 19.90% 6.88% 13.01% 0.00% 13.01% 13.01%

Jun-11 100.00% 58.02% 41.98% 23.51% 18.47% 0.09% 1.18% 19.56% 6.86% 12.70% 0.00% 12.70% 12.70%

Jun-10 100.00% 56.34% 43.66% 23.82% 19.84% 0.10% 1.13% 20.87% 7.35% 13.52% 0.04% 13.56% 13.56%

3.17 3.14

2.91 2.89

2.85 2.82

2.54 2.52

2.42 2.40

479 483

483 487

487 492

494 498

500 504

Note: Both MSN and Yahoo! have recently made it difficult to copy and paste financial statements into Excel. Therefore, I got this data from Morningstar Direct. There may be slight errors due to rounding.

Automatic Data Processing, Inc. Income Statements For the Years 2010 to 2014 USD in Million except per share data Jun-14 Jun-13 Jun-12 Assets Cash and cash equivalents Short-term investments Total cash Receivables Other current assets Total current assets Gross property, plant and equipment Accumulated Depreciation Net property, plant and equipment Equity and other investments Goodwill Intangible assets Other long-term assets Total non-current assets Total assets Liabilities and stockholders' equity Short-term debt Accounts payable Taxes payable Accrued liabilities Deferred revenues Other current liabilities Total current liabilities Non-current liabilities Long-term debt Deferred taxes liabilities Deferred revenues Other long-term liabilities Total non-current liabilities Total liabilities Stockholders' equity Common stock Additional paid-in capital Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity

Jun-11

Jun-10

6.19% 6.34% 12.53% 5.35% 62.71% 80.60% 6.63% -4.21% 2.43% 0.17% 9.72% 1.97% 5.12% 19.40% 100.00%

5.27% 0.09% 5.35% 4.71% 71.14% 81.20% 6.33% -4.07% 2.26% 0.97% 9.46% 1.99% 4.12% 18.80% 100.00%

5.02% 0.10% 5.12% 4.29% 72.22% 81.63% 6.35% -4.06% 2.29% 0.28% 10.24% 2.31% 3.25% 18.37% 100.00%

4.06% 0.11% 4.16% 3.76% 75.56% 83.48% 5.70% -3.61% 2.09% 0.29% 8.98% 2.09% 3.07% 16.52% 100.00%

6.12% 0.10% 6.22% 3.86% 73.00% 83.08% 6.81% -4.30% 2.51% 0.39% 8.87% 2.02% 3.13% 16.92% 100.00%

6.78% 0.53% 0.06% 6.31% 1.04% 59.87% 74.59%

0.00% 0.49% 0.12% 5.61% 0.98% 69.44% 76.65%

0.00% 0.55% 0.13% 5.37% 1.08% 68.30% 75.42%

0.00% 0.45% 0.08% 4.35% 1.03% 72.33% 78.24%

0.00% 0.56% 0.22% 4.54% 1.20% 68.13% 74.65%

0.04% 0.90% 1.60% 2.06% 4.60% 79.19%

0.05% 0.73% 1.53% 1.87% 4.17% 80.81%

0.06% 1.27% 1.52% 1.90% 4.74% 80.16%

0.10% 1.09% 1.39% 1.62% 4.21% 82.45%

0.15% 1.14% 1.70% 1.97% 4.95% 79.61%

0.20% 1.70% 42.53% -24.18% 0.56% 20.81% 100.00%

0.20% 1.42% 40.35% -22.83% 0.05% 19.18% 99.99%

0.21% 1.58% 40.36% -23.06% 0.75% 19.84% 100.00%

0.19% 1.43% 34.48% -19.61% 1.07% 17.56% 100.01%

0.24% 1.84% 41.89% -24.35% 0.78% 20.40% 100.00%

Elvis Products International Income Statement For the Year Ended Dec. 31, 2014 2014 2013 Sales 3,850,000 3,432,000 Cost of Goods Sold 3,250,000 2,864,000 Gross Profit 600,000 568,000 Selling and G&A Expenses 330,300 240,000 Fixed Expenses 100,000 100,000 Depreciation Expense 20,000 18,900 EBIT 149,700 209,100 Interest Expense 76,000 62,500 Earnings Before Taxes 73,700 146,600 Taxes 29,480 58,640 Net Income 44,220 87,960 Notes: Tax Rate

40%

Note: The year in cell B4 is the one which all of years in this workbook depend on. To change all dates, change B4 to another year. This will also be reflected in the text in these workbooks.

ell B4 is the one which orkbook depend on. change B4 to another be reflected in the text

Elvis Products International Income Statement For the Year Ended Dec. 31, 2014 ($ in 000's) 2014 2013 Sales 3,850.00 3,432.00 Cost of Goods Sold 3,250.00 2,864.00 Gross Profit 600.00 568.00 Selling and G&A Expenses 330.30 240.00 Fixed Expenses 100.00 100.00 Depreciation Expense 20.00 18.90 EBIT 149.70 209.10 Interest Expense 76.00 62.50 Earnings Before Taxes 73.70 146.60 Taxes 29.48 58.64 Net Income 44.22 87.96 Notes: Tax Rate

40%

Note: All of the values on this sheet reference the original values from the Income Statement. The only purpose of this sheet is to show the effect of the custom number format.

l of the values on this sheet e the original values from the Statement. The only purpose of this o show the effect of the custom

Elvis Products International Common-size Income Statement For the Year Ended Dec. 31, 2014 2014 Sales 100.00% Cost of Goods Sold 84.42% Gross Profit 15.58% Selling and G&A Expenses 8.58% Fixed Expenses 2.60% Depreciation Expense 0.52% EBIT 3.89% Interest Expense 1.97% Earnings Before Taxes 1.91% Taxes 0.77% Net Income 1.15% Notes: Tax Rate

40%

2013 100.00% 83.45% 16.55% 6.99% 2.91% 0.55% 6.09% 1.82% 4.27% 1.71% 2.56%

Contribution -0.97% -1.59% 0.32% 0.03% -0.15% 0.94% -1.41%

Elvis Products International Pro-forma Income Statement For the Year Ended Dec. 31, 2014 2015* 2014 Sales 4,300.00 3,850.00 Cost of Goods Sold 3,609.11 3,250.00 Gross Profit 690.89 600.00 Selling and G&A Expenses 334.80 330.30 Fixed Expenses 100.00 100.00 Depreciation Expense 25.00 20.00 EBIT 231.09 149.70 Interest Expense 76.00 76.00 Earnings Before Taxes 155.09 73.70 Taxes 62.04 29.48 Net Income 93.05 44.22 * Forecast Notes: Tax Rate Additional Depreciation

40% 5.00

40%

2013 3,432.00 2,864.00 568.00 240.00 100.00 18.90 209.10 62.50 146.60 58.64 87.96

Note that firm's capital expenditures are expected to rise by $50,000 in the new year. This will lead to an increase of $5,000 in accumulated depreciation.

ital expenditures by $50,000 in the lead to an increase ulated depreciation.

Elvis Products International Balance Sheet As of Dec. 31, 2014 Assets Cash and Equivalents Accounts Receivable Inventory Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owner's Equity Accounts Payable Short-term Notes Payable Other Current Liabilities Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owner's Equity

2014 52,000 402,000 836,000 1,290,000 527,000 166,200

2013 57,600 351,200 715,200 1,124,000 491,000 146,200

360,800

344,800

1,650,800

1,468,800

175,200 225,000 140,000 540,200 424,612 964,812 460,000 225,988 685,988 1,650,800

145,600 200,000 136,000 481,600 323,432 805,032 460,000 203,768 663,768 1,468,800

Elvis Products International Balance Sheet As of Dec. 31, 2014 ($ in 000's) Assets 2014 Cash and Equivalents 52.00 Accounts Receivable 402.00 Inventory 836.00 Total Current Assets 1,290.00 Plant & Equipment 527.00 Accumulated Depreciation 166.20 Net Fixed Assets 360.80 Total Assets 1,650.80 Liabilities and Owner's Equity Accounts Payable 175.20 Short-term Notes Payable 225.00 Other Current Liabilities 140.00 Total Current Liabilities 540.20 Long-term Debt 424.61 Total Liabilities 964.81 Common Stock 460.00 Retained Earnings 225.99 Total Shareholder's Equity 685.99 Total Liabilities and Owner's Equity 1,650.80

2013 57.60 351.20 715.20 1,124.00 491.00 146.20

344.80 1,468.80 145.60 200.00 136.00 481.60 323.43 805.03 460.00 203.77 663.77 1,468.80

Note: All of the values on this s reference the original values fro Balance Sheet. The only purpos sheet is to show the effect of th number format.

Note: All of the values on this sheet reference the original values from the Balance Sheet. The only purpose of this sheet is to show the effect of the custom number format.

Elvis Products International Common-size Balance Sheet As of Dec. 31, 2014 Assets Cash and Equivalents Accounts Receivable Inventory Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owner's Equity Accounts Payable Short-term Notes Payable Other Current Liabilities Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owner's Equity

2014 3.15% 24.35% 50.64% 78.14% 31.92% 10.07%

2013 3.92% 23.91% 48.69% 76.53% 33.43% 9.95%

21.86%

23.47%

100.00%

100.00%

10.61% 13.63% 8.48% 32.72% 25.72% 58.45% 27.87% 13.69% 41.55% 100.00%

9.91% 13.62% 9.26% 32.79% 22.02% 54.81% 31.32% 13.87% 45.19% 100.00%

Elvis Products International Pro-forma Balance Sheet As of Dec. 31, 2014 Assets 2015* Cash and Equivalents 52.00 Accounts Receivable 444.51 Inventory 914.90 Total Current Assets 1,411.40 Plant & Equipment 577.00 Accumulated Depreciation 191.20 Net Fixed Assets 385.80 Total Assets 1,797.20 Liabilities and Owner's Equity Accounts Payable 189.05 Short-term Notes Payable 225.00 Other Current Liabilities 163.38 Total Current Liabilities 577.43 Long-term Debt 424.61 Total Liabilities 1,002.04 Common Stock 460.00 Retained Earnings 297.04 Total Shareholder's Equity 757.04 Total Liabilities and Owner's Equity 1,759.08

2014 52.00 402.00 836.00 1,290.00 527.00 166.20

2013 57.60 351.20 715.20 1,124.00 491.00 146.20

360.80

344.80

1,650.80

1,468.80

175.20 225.00 140.00 540.20 424.61 964.81 460.00 225.99 685.99 1,650.80

145.60 200.00 136.00 481.60 323.43 805.03 460.00 203.77 663.77 1,468.80

*Forecast Discretionary Financing Needed

38.12 Deficit

Notes: Net Addition to Plant & Equipment Life of New Equipment in Years New Depreciation (Straight Line)

50.00 10 5.00

Note that firm's capital expenditure are expected to rise by $50,000 in new year. This will lead to an incre of $5,000 in accumulated deprecia

Note that firm's capital expenditures are expected to rise by $50,000 in the new year. This will lead to an increase of $5,000 in accumulated depreciation.

Elvis Products International Statement of Cash Flows For the Year Ended Dec. 31, 2014 ($ in 000's) Cash Flows from Operations Net Income 44.22 Depreciation Expense 20.00 Change in Accounts Receivable -50.80 Change in Inventories -120.80 Change in Accounts Payable 29.60 Change in Other Current Liabilities 4.00 Total Cash Flows from Operations Cash Flows from Investing Change in Plant & Equipment -36.00 Total Cash Flows from Investing Cash Flows from Financing Change in Short-term Notes Payable 25.00 Change in Long-term Debt 101.18 Change in Common Stock 0.00 Cash Dividends Paid to Shareholders -22.00 Total Cash Flows from Financing Net Change in Cash Balance

-73.78

-36.00

104.18 -5.60

Elvis Products International Common-size Statement of Cash Flows For the Year Ended Dec. 31, 2014 Cash Flows from Operations % of Sales Net Income 1.15% Depreciation Expense 0.52% Change in Accounts Receivable -1.32% Change in Inventories -3.14% Change in Accounts Payable 0.77% Change in Other Current Liabilities 0.10% Total Cash Flows from Operations Cash Flows from Investing Change in Plant & Equipment -0.94% Total Cash Flows from Investing Cash Flows from Financing Change in Short-term Notes Payable 0.65% Change in Long-term Debt 2.63% Change in Common Stock 0.00% Cash Dividends Paid to Shareholders -0.57% Total Cash Flows from Financing Net Change in Cash Balance

Denominator

-1.92%

-0.94%

2.71% -0.15%

1

Elvis Products International Ratio Analysis for 2013 and 2014 Industry Ratio 2014 2013 2014 Analysis Liquidity Ratios Current Ratio 2.39x 2.33x 2.70x Ok Quick Ratio 0.84x 0.85x 1.00x Bad Efficiency Ratios Inventory Turnover 3.89x 4.00x 7.00x Bad A/R Turnover 9.58x 9.77x 10.70x Bad Average Collection Period 37.59 days 36.84 days 33.64 days Bad Fixed Asset Turnover 10.67x 9.95x 11.20x Ok Total Asset Turnover 2.33x 2.34x 2.60x Bad Leverage Ratios Total Debt Ratio 58.45% 54.81% 50.00% Bad Long-term Debt Ratio 25.72% 22.02% 20.00% Bad LTD to Total Capitalization 38.23% 32.76% 28.57% Bad Debt to Equity 1.41x 1.21x 1.00x Bad LTD to Equity 61.90% 48.73% 40.00% Bad Coverage Ratios Times Interest Earned 1.97x 3.35x 2.50x Bad Cash Coverage Ratio 2.23x 3.65x 2.80x Bad Profitability Ratios Gross Profit Margin 15.58% 16.55% 17.50% Bad Operating Profit Margin 3.89% 6.09% 6.25% Bad Net Profit Margin 1.15% 2.56% 3.50% Bad Return on Total Assets 2.68% 5.99% 9.10% Bad Return on Equity 6.45% 13.25% 18.20% Bad Return on Common Equity 6.45% 13.25% 18.20% Bad DuPont ROE Market Value of Equity Z-Score - Publicly Traded Z-Score - Privately Held

6.45% 884,400 3.92 3.35

13.25% 1,759,200 4.84 3.55

Stock Valuation Notes: Number of Shares Assumed P/E Ratio Stock Price

100,000 20 $8.84

100,000 20 $17.59

18.20%

Elvis Products International Economic Profit Calculations 2014 2013 Tax Rate 40% 40% NOPAT 89,820 125,460 Total Operating Capital 1,335,600 1,187,200 After-tax Cost of Capital 13% 13% Dollar Cost of Capital 173,628 154,336 Economic Profit (83,808) (28,876)

Sales 1,890,532 2,098,490 2,350,308 3,432,000 3,850,000

EPI Sales for 2010 to 2014 Sales

Year 2010 2011 2012 2013 2014

5,000,000 4,000,000 3,000,000 2,000,000 1,000,000 0 2009

2010

2011

2012

Year

2013

2014

2015

Chart Title

EPI Sales for 2010 to 2014

Sales 1,890,532 2,098,490 2,350,308 3,432,000 3,850,000 4,300,000 4,825,244 5,350,489

EPI Sales for 2010 to 2014 with Trend Sales

Year 2010 2011 2012 2013 2014 2015 2016 2017

6,000,000 5,000,000 4,000,000 3,000,000 2,000,000 1,000,000 0

y = 525,244.60x - 1,054,067,869.20

2008

2010

2012

2014

2016

2018

Year

Actual Equation Using LinEst Slope Intercept 525,244.60 ##############

This is an example using LinEst as an array-entered function.

525,244.60 ##############

This is an example of pulling out just the specific parameter that you wa

Year 2015 2016 2017

Sales 4,300,000 4,825,244 5,350,489

These are forecasts using the LinEst() and Index() functions.

Chart Title

EPI Sales for 2010 to 2014 with Trend

as an array-entered function.

just the specific parameter that you want using the Index() function.

Est() and Index() functions.

Cost of Goods vs Sales 2010 to 2014 4,000,000

Cost of Goods

3,000,000

2,000,000

1,000,000 1,000,000

2,000,000

3,000,000

Sales 2

4,000,000

Year 2010 2011 2012 2013 2014

Sales 1,890,532 2,098,490 2,350,308 3,432,000 3,850,000

Cost of Goods 1,570,200 1,695,694 1,992,400 2,864,000 3,250,000

Cost of Goods

Cost of Goods vs Sales 2010 to 2014 4,000,000 3,000,000 2,000,000 1,000,000 1,000,000

2,000,000

3,000,000

Sales

4,000,000

Chart Title

Cost of Goods vs Sales

SUMMARY OUTPUT Regression Statistics Multiple R 99.91% R Square 99.83% Adjusted R Square 99.77% Standard Error 35523.08 Observations 5 ANOVA df Regression Residual Total

Intercept Sales

1 3 4

SS 2,205,960,110,240 3,785,666,909 2,209,745,777,149

Coefficients (63,680.8247) 0.8583

Standard Error 58,134.6760 0.0205

MS 2,205,960,110,240 1,261,888,970

t Stat (1.0954) 41.8108

F 1,748

P-value 0.3534 0.0000

Significance F 0.0000

Lower 95% Upper 95% (248,691.3095) 121,329.6601 0.7929 0.9236

Year 2010 2011 2012 2013 2014 2015*

Sales 1,890,532 2,098,490 2,350,308 3,432,000 3,850,000 4,300,000

Chart Title Cost of Goods vs Sales

Cost of Goods 1,570,200 1,695,694 1,992,400 2,864,000 3,250,000 3,626,855

Cost of Goods vs Sales 2010 to 2014 Cost of Goods

4,000,000

y = 0.8583x - 63681 R² = 0.9983 3,000,000

2,000,000 1,000,000 1,000,000

2,000,000

3,000,000

Sales *Forecast

4,000,000

Winter Park Web Design Income Statements For the Years 2013 and 2014 2014 Sales Cost of Goods Sold Gross Profit Depreciation Expense Selling & Admin Expense Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Outstanding Earnings per Share Dividends per Share Addition to RE per Share

$

280,419 154,229

2013 $

225,000 123,750

825

4,646 720

1,020

810

37.0%

35.0%

$6.00

$5.00

Note: Cells with a green background are the missing cells in the problem.

Winter Park Web Design Balance Sheets For the Years 2013 and 2014 Cash Accounts receivable Inventories Total Current Assets Gross fixed assets Accumulated depreciation Net Fixed Assets Total assets

$

Accounts payable Notes payable Total Current Liabilities Long-term debt Total Liabilities Common stock Additional paid in capital Retained earnings Total Equity Total Liabilities & Equity

$

Notes: Shares Outstanding

2014 15,225 $ 18,756

2013 11,250 11,325

43,530 10,845 32,685 32,685

-

12,302 4,500 4,500 10,673 10,673 7,500

4,500 750 15,351

7,500

Winter Park Web Design Statement of Cash Flows For the Year 2014 Cash Flows from Operations Net Income Depreciation Expense Change in Accounts Receivable Change in Inventories Change in Accounts Payable Total Cash Flows from Operations Cash Flows from Investing

5,295 (2,256) (1,586) 2,025

Change in fixed assets Total Cash Flows from Investing Cash Flows from Financing

(30,255)

Change in Notes Payable Change in Long-Term Debt Change in Common Stock Change in Paid-In Capital Cash Dividends Total Cash Flows from Financing Net Change in Cash Balance

(1,500) 2,250 -

Check answer against Balance Sheet Beginning Cash From Balance Sheet Ending Cash From Balance Sheet Net Change in Cash Balance

$

(30,255)

Financial Analysis with Microsoft Excel 7th Edition Mayes Solutions Manual Full Download: http://alibabadownload.com/product/financial-analysis-with-microsoft-excel-7th-edition-mayes-solutions-manual/

This sample only, Download all chapters at: alibabadownload.com