finance analysis

profilespousesy
dell_valuation.xlsx
Home>Business & Finance homework help>finance analysis

Historic Financials

Income Statement Annuals
Fiscal Period Jan-00 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan 8-Jan 9-Jan 10-Jan 11-Jan 12-Jan 13-Jan
Revenue $ 25,265 $ 31,888 $ 31,168 $ 35,404 $ 41,444 $ 49,205 $ 55,908 $ 57,420 $ 61,133 $ 61,101 $ 52,902 $ 61,494 $ 62,071 $ 56,940
COGS 20,047 25,445 25,661 29,055 33,892 40,190 45,958 47,904 49,462 50,144 43,641 50,098 48,260 44,754
Gross Profit 5,218 6,443 5,507 6,349 7,552 9,015 9,950 9,516 11,671 10,957 9,261 11,396 13,811 12,186
SGA 2,387 3,193 2,784 3,050 3,544 4,298 5,140 5,948 7,538 7,102 6,465 7,302 8,524 8,102
RD 374 482 452 455 464 463 463 498 610 665 624 661 856 1072
EBIT 2,457 2,768 2,271 2,844 3,544 4,254 4,347 3,070 3,523 3,190 2,172 3,433 4,431 3,012
Net Interest Expense/Other Income 6 (426) 540 (183) (180) (191) (227) (275) (304) (134) 148 83 191 171
Pretax Income 2,451 3,194 1,731 3,027 3,724 4,445 4,574 3,345 3,827 3,324 2,024 3,350 4,240 2,841
Tax 785 1,017 485 905 1,079 1,402 1,002 762 880 846 591 715 748 469
Net Income $ 1,666 $ 2,177 $ 1,246 $ 2,122 $ 2,645 $ 3,043 $ 3,572 $ 2,583 $ 2,947 $ 2,478 $ 1,433 $ 2,635 $ 3,492 $ 2,372
Earnings Per Share ($) 0.65 0.84 0.48 0.82 1.03 1.22 1.52 1.14 1.32 1.27 0.73 1.35 1.88 1.35
Shares Outstanding 2,552 2,587 2,607 2,579 2,560 2,484 2,354 2,272 2,241 1,944 1962 1955 1853 1755
Balance Sheet Annuals (Year End)
Fiscal Period Jan-00 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan 8-Jan 9-Jan 10-Jan 11-Jan 12-Jan 13-Jan
Cash and Equiv $ 4,132 $ 5,438 $ 3,914 $ 4,638 $ 5,152 $ 9,807 $ 9,058 $ 10,298 $ 7,972 $ 9,092 $ 11,008 $ 14,365 $ 14,818 $ 12,777
Accts Rec. 2,608 2,895 2,269 2,586 3,635 4,414 5,452 6,152 7,693 4,731 5,837 6,493 6,476 6,629
Inventory 391 400 278 306 327 459 576 660 1,180 867 1,051 1,301 1,404 1,382
Other Current Assets 550 758 1,416 1,394 1,519 2,217 2,620 2,829 3,035 6,328 6,349 6,862 6,750 7,180
Total Current Assets 7,681 9,491 7,877 8,924 10,633 16,897 17,706 19,939 19,880 21,018 24,245 29,021 29,448 27,968
Net PPE 2,608 2,895 2,269 2,586 3,635 4,414 5,452 6,152 7,693 4,731 2,181 1,953 2,124 2,126
Intangibles 391 400 278 306 327 459 576 660 1,180 867 5,768 5,860 7,695 12,678
Other Long-Term Assets 2,721 2,948 4,832 5,633 7,161 4,627 3,398 3,287 2,585 1,611 1,458 1,765 5,266 4,768
Total Assets $ 11,471 $ 13,435 $ 13,535 $ 15,470 $ 19,311 $ 23,215 $ 23,109 $ 25,635 $ 27,561 $ 26,500 $ 33,652 $ 38,599 $ 44,533 $ 47,540
Liabilibities & Equity
Account Payable $ 5,192 $ 6,543 $ 7,519 $ 8,933 $ 10,896 $ 14,136 $ 15,927 $ 17,791 $ 16,040 $ 12,097 $ 15,257 $ 15,474 $ 14,264 $ 14,060
Current Portion of Long-Term Debt -- -- -- -- -- -- -- -- -- 113 663 851 2,867 3,843
Other Current Liab. -- -- -- -- -- -- -- -- 2,486 2,649 3,040 3,158 4,870 5,536
Total Current Liabilities 5,192 6,543 7,519 8,933 10,896 14,136 15,927 17,791 18,526 14,859 18,960 19,483 22,001 23,439
Long-Term Debt 508 509 520 506 505 505 504 569 362 1,898 3,417 5,146 6,387 5,242
Other Long-Term Liab. 463 761 802 1,158 1,630 2,089 2,549 2,836 4,844 5,472 5,634 6,204 7,228 8,179
Total Liabilities 6,163 7,813 8,841 10,597 13,031 16,730 18,980 21,196 23,732 22,229 28,011 30,833 35,616 36,860
Total Equity 5,308 5,622 4,694 4,873 6,280 6,485 4,129 4,439 3,829 4,271 5,641 7,766 8,917 10,680
Total Liabilities & Equity $ 11,471 $ 13,435 $ 13,535 $ 15,470 $ 19,311 $ 23,215 $ 23,109 $ 25,635 $ 27,561 $ 26,500 $ 33,652 $ 38,599 $ 44,533 $ 47,540
Jan-00 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan 7-Jan 8-Jan 9-Jan 10-Jan 11-Jan 12-Jan 13-Jan 14-Jan 15-Jan 16-Jan 17-Jan 18-Jan
Liquidity
Current Ratio 1.48 1.45 1.05 1.00 0.98 1.20 1.11 1.12 1.07 1.41 1.28 1.49 1.34 1.19 1.28 1.37 1.46 1.54 1.62
Quick Ratio 1.40 1.39 1.01 0.96 0.95 1.16 1.08 1.08 1.01 1.36 1.22 1.42 1.27 1.13 1.22 1.31 1.40 1.48 1.56
Activity
Receivables Turnover 9.69 11.01 13.74 13.69 11.40 11.15 10.25 9.33 7.95 12.92 9.06 9.47 9.58 8.59 8.59 8.59 8.59 8.59 8.59
Average Collection Period 37.68 33.14 26.57 26.66 32.01 32.74 35.59 39.11 45.93 28.26 40.27 38.54 38.08 42.49 42.49 42.49 42.49 42.49 42.49
Inventory Turnover 51.27 63.61 92.31 94.95 103.65 87.56 79.79 72.58 41.92 57.84 41.52 38.51 34.37 32.38 32.38 32.38 32.38 32.38 32.38
Average Inv Processing Period 7.12 5.74 3.95 3.84 3.52 4.17 4.57 5.03 8.71 6.31 8.79 9.48 10.62 11.27 11.27 11.27 11.27 11.27 11.27
Payables Turnover 3.86 3.89 3.41 3.25 3.11 2.84 2.89 2.69 3.08 4.15 2.86 3.24 3.38 3.18 3.18 3.18 3.18 3.18 3.18
Payables Payment Period 94.53 93.86 106.95 112.22 117.34 128.38 126.49 135.56 118.37 88.05 127.60 112.74 107.88 114.67 114.67 114.67 114.67 114.67 114.67
Cash Conversion Cycle (5.58) 2.89 11.93 9.39 18.31 (8.08) (11.11) (23.87) (30.52) (1.96) (45.81) (35.69) (35.43) (39.79) (39.79) (39.79) (39.79) (39.79) (39.79)
Total Asset Turnover 2.20 2.37 2.30 2.29 2.15 2.12 2.42 2.24 2.22 2.31 1.57 1.59 1.39 1.20 1.17 1.15 1.13 1.11 1.09
Fixed Asset Turnover 9.69 11.01 13.74 13.69 11.40 11.15 10.25 9.33 7.95 12.92 24.26 31.49 29.22 26.78 26.78 26.78 26.78 26.78 26.78
Debt
Total Debt Ratio 0.54 0.58 0.65 0.69 0.67 0.72 0.82 0.83 0.86 0.84 0.83 0.80 0.80 0.78 0.74 0.71 0.68 0.66 0.63
Profitability
Gross Profit Margin 0.21 0.20 0.18 0.18 0.18 0.18 0.18 0.17 0.19 0.18 0.18 0.19 0.22 0.21 0.21 0.21 0.21 0.21 0.21
Operating Profit Margin 0.10 0.09 0.07 0.08 0.09 0.09 0.08 0.05 0.06 0.05 0.04 0.06 0.07 0.05 0.05 0.05 0.05 0.05 0.05
Net Profit Margin 0.07 0.07 0.04 0.06 0.06 0.06 0.06 0.04 0.05 0.04 0.03 0.04 0.06 0.04 0.04 0.04 0.04 0.04 0.04
Return on Assets 0.15 0.16 0.09 0.14 0.14 0.13 0.15 0.10 0.11 0.09 0.04 0.07 0.08 0.05 0.05 0.05 0.05 0.05 0.05
Return on Equity 0.31 0.39 0.27 0.44 0.42 0.47 0.87 0.58 0.77 0.58 0.25 0.34 0.39 0.22 0.19 0.17 0.15 0.14 0.12
Dupont Analysis
ROE = 0.31 0.39 0.27 0.44 0.42 0.47 0.87 0.58 0.77 0.58 0.25 0.34 0.39 0.22 0.19 0.17 0.15 0.14 0.12
Profit Margin on Sales 0.07 0.07 0.04 0.06 0.06 0.06 0.06 0.04 0.05 0.04 0.03 0.04 0.06 0.04 0.04 0.04 0.04 0.04 0.04
Total Asset Turnover 2.20 2.37 2.30 2.29 2.15 2.12 2.42 2.24 2.22 2.31 1.57 1.59 1.39 1.20 1.17 1.15 1.13 1.11 1.09
Equity Multiplier 2.16 2.39 2.88 3.17 3.08 3.58 5.60 5.77 7.20 6.20 5.97 4.97 4.99 4.45 3.87 3.46 3.15 2.91 2.72

Forecast Assumptions

Forecast Assumptions
Historical Forecast Forecast Forecast Forecast Forecast
2013 2014 2015 2016 2017 2018
OPERATIONS
Revenue Growth (0.08) 0.05 0.05 0.05 0.05 0.05
COGS/Sales 0.79 0.79 0.79 0.79 0.79 0.79
R&D/Sales 0.02 0.02 0.02 0.02 0.02 0.02
SG&A/Sales 0.14 0.14 0.14 0.14 0.14 0.14
Net Interest Expense/Other Income 171.00 171.00 171.00 171.00 171.00 171.00
Effective Tax Rate 0.17 0.17 0.17 0.17 0.17 0.17
Current Assets
Cash 12,777.00 12,777.00 12,777.00 12,777.00 12,777.00 12,777.00
Receivables/sales 0.12 0.12 0.12 0.12 0.12 0.12
Inventories/sales 0.02 0.02 0.02 0.02 0.02 0.02
Other Current Assets/Sales 0.13 0.13 0.13 0.13 0.13 0.13
Long-term Assets
Net PP&E 0.04 0.04 0.04 0.04 0.04 0.04
Intangibles 12,678.00 12,678.00 12,678.00 12,678.00 12,678.00 12,678.00
Other LTA 4,768.00 4,768.00 4,768.00 4,768.00 4,768.00 4,768.00
Current Liabilities
Accounts payable/sales 0.25 0.25 0.25 0.25 0.25 0.25
Current Portion of LTD 3,843.00 3,843.00 3,843.00 3,843.00 3,843.00 3,843.00
Other Current Liabilities/Sales 0.10 0.10 0.10 0.10 0.10 0.10
Long Term Debt 5,242.00 5,242.00 5,242.00 5,242.00 5,242.00 5,242.00
Other Long-Term Liabilities 8,179.00 8,179.00 8,179.00 8,179.00 8,179.00 8,179.00

Pro Formas

Income Statement Historic Pro-Forma
Fiscal Period 13-Jan 14-Jan 15-Jan 16-Jan 17-Jan 18-Jan
Revenue $ 56,940 $ 59,787 $ 62,776 $ 65,915 $ 69,211 $ 72,671
COGS 44,754 46,992 49,341 51,808 54,399 57,119
Gross Profit 12,186 12,795 13,435 14,107 14,812 15,553
SGA 8,102 8,507 8,932 9,379 9,848 10,340
RD 1072 1126 1182 1241 1303 1368
EBIT 3,012 3,163 3,321 3,487 3,661 3,844
Net Interest Expense/Other Income 171 171 171 171 171 171
Pretax Income 2,841 2,992 3,150 3,316 3,490 3,673
Tax 469 494 520 547 576 606
Net Income $ 2,372 $ 2,498 $ 2,630 $ 2,768 $ 2,914 $ 3,067
Balance Sheet
Fiscal Period 13-Jan 14-Jan 15-Jan 16-Jan 17-Jan 18-Jan
Cash and Equiv $ 12,777 $ 12,777 $ 12,777 $ 12,777 $ 12,777 $ 12,777
Surplus Cash 2,612 5,361 8,255 11,301 14,506
Accts Rec. 6,629 6,960 7,308 7,674 8,058 8,460
Inventory 1,382 1,451 1,524 1,600 1,680 1,764
Other Current Assets 7,180 7,539 7,916 8,312 8,727 9,164
Total Current Assets 27,968 31,339 34,886 38,618 42,543 46,671
Net PPE 2,126 2,232 2,344 2,461 2,584 2,713
Intangibles 12,678 12,678 12,678 12,678 12,678 12,678
Other Long-Term Assets 4,768 4,768 4,768 4,768 4,768 4,768
Total Assets $ 47,540 $ 51,018 $ 54,676 $ 58,525 $ 62,573 $ 66,831 152541
9939
Liabilibities & Equity 162480
Account Payable $ 14,060 $ 14,763 $ 15,501 $ 16,276 $ 17,090 $ 17,945 108320
Current Portion of Long-Term Debt 3,843 3,843 3,843 3,843 3,843 3,843 54160
Other Current Liab. 5,536 5,813 6,103 6,409 6,729 7,065
Total Current Liabilities 23,439 24,419 25,448 26,528 27,662 28,853
380.25
Long-Term Debt 5,242 5,242 5,242 5,242 5,242 5,242 126.75
Other Long-Term Liab. 8,179 8,179 8,179 8,179 8,179 8,179
Total Liabilities 36,860 37,840 38,869 39,949 41,083 42,274
Total Equity 10,680 13,178 15,808 18,576 21,490 24,557
Total Liabilities & Equity $ 47,540 $ 51,018 $ 54,676 $ 58,525 $ 62,573 $ 66,831
1150.5
Check Figure $ - $ - $ - $ - $ -