Fluent Essays

- APA FORMAT(  paragraphs are single-spaced ) - NO PLAGIARISM  - NEED PLAGIARISM REPORT - INTEXT CITATION  - QUESTION IS ATTACHED and SOURCE TOO  - 1” margins 12 font size Times New Roman font style Portrait orientation Double spaced No paragraph indention in business writing; use only one space between paragraphs. Must be written thoroughly and in complete sentences. The writing report should be free of errors. The writing report should be written using APA guidelines Please review the presentation on Financial Modeling and Forecasting. Please review the Microsoft Corporation Case Study (Financial Statement) and Submit a paper explaining the components of the spreadsheet and the findings. What is your financial recommendation for forecasting regarding future profits, liabilities, assets, a deficient, etc? Splash Microsoft Financial Data - FY12 Q2 This file contains downloadable content that corresponds to Microsoft's Investor Relations portal at http://www.microsoft.com/investor. Click a link below to navigate to that section of the workbook. Each sheet contains a link back to this main page. Current Financial Statements Trended Historical Financial Statements Income Statement Income Statement Balance Sheet Financial Operating Segment History Cash Flow Unearned Revenue Segment Revenue & Operating Income Yearly Income Statements Income Statement MICROSOFT CORPORATION Back to Main INCOME STATEMENTS (In millions, except per share amounts) Three Months Ended December 31, Six Months Ended December 31, 2011 2010 2011 2010 Revenue $ 20,885 $ 19,953 $ 38,257 $ 36,148 Operating expenses: Cost of revenue 5,638 4,833 9,415 7,972 Research and development 2,371 2,185 4,700 4,381 Sales and marketing 3,762 3,825 6,662 6,631 General and administrative 1,120 945 2,283 1,883 Total operating expenses 12,891 11,788 23,060 20,867 Operating income 7,994 8,165 15,197 15,281 Other income 245 332 348 446 Income before income taxes 8,239 8,497 15,545 15,727 Provision for income taxes 1,615 1,863 3,183 3,683 Net income $ 6,624 $ 6,634 $ 12,362 $ 12,044 Earnings per share: Basic $ 0.79 $ 0.78 $ 1.47 $ 1.41 Diluted $ 0.78 $ 0.77 $ 1.46 $ 1.39 Weighted average shares outstanding: Basic 8,402 8,497 8,397 8,555 Diluted 8,465 8,570 8,489 8,646 Cash dividends declared per common share $ 0.20 $ 0.16 $ 0.40 $ 0.32 Balance Sheet MICROSOFT CORPORATION Back to Main BALANCE SHEETS (In millions) December 31, 2011 June 30, 2011(1) Assets Current assets: Cash and cash equivalents $ 10,610 $ 9,610 Short-term investments (including securities loaned of $831 and $1,181) 41,126 43,162 Total cash, cash equivalents, and short-term investments 51,736 52,772 Accounts receivable, net of allowance for doubtful accounts of $321 and $333 13,643 14,987 Inventories 1,351 1,372 Deferred income taxes 2,169 2,467 Other 3,614 3,320 Total current assets 72,513 74,918 Property and equipment, net of accumulated depreciation of $10,546 and $9,829 8,010 8,162 Equity and other investments 7,550 10,865 Goodwill 19,670 12,581 Intangible assets, net 2,581 744 Other long-term assets 1,919 1,434 Total assets $ 112,243 $ 108,704 Liabilities and stockholders' equity Current liabilities: Accounts payable $ 3,884 $ 4,197 Accrued compensation 2,677 3,575 Income taxes 921 580 Short-term unearned revenue 13,985 15,722 Securities lending payable 849 1,208 Other 3,057 3,492 Total current liabilities 25,373 28,774 Long-term debt 11,932 11,921 Long-term unearned revenue 1,349 1,398 Deferred income taxes 1,082 1,456 Other long-term liabilities 8,386 8,072 Total liabilities 48,122 51,621 Commitments and contingencies Stockholders' equity: Common stock and paid-in capital - shares authorized 24,000; outstanding 8,382 and 8,376 63,902 63,415 Retained earnings (deficit), including accumulated other comprehensive income of $826 and $1,863 219 (6,332) Total stockholders' equity 64,121 57,083 Total liabilities and stockholders' equity $ 112,243 $ 108,704 (1) Derived from audited financial statements. Cash Flow MICROSOFT CORPORATION Back to Main CASH FLOW STATEMENTS (In millions) Three Months Ended December 31, Six Months Ended December 31, 2011 2010 2011 2010 Operations Net income $ 6,624 $ 6,634 $ 12,362 $ 12,044 Adjustments to reconcile net income to net cash from operations: Depreciation, amortization, and other 678 663 1,404 1,357 Stock-based compensation expense 575 553 1,133 1,081 Net recognized gains on investments and derivatives (112) (226) (142) (255) Excess tax benefits from stock-based compensation (4) (4) (74) (9) Deferred income taxes 14 (117) 416 (265) Deferral of unearned revenue 7,544 6,834 13,683 12,715 Recognition of unearned revenue (8,057) (7,301) (15,710) (14,163) Changes in operating assets and liabilities: Accounts receivable (3,652) (3,270) 1,081 404 Inventories 891 380 (29) (88) Other current assets 605 (77) 865 131 Other long-term assets 30 118 (45) 180 Accounts payable 176 216 (266) (184) Other current liabilities 394 (500) (599) (1,411) Other long-term liabilities 156 283 276 843 Net cash from operations 5,862 4,186 14,355 12,380 Financing Short-term debt repayments, maturities of 90 days or less, net 0 (1,000) 0 (186) Proceeds from issuance of debt, maturities longer than 90 days 0 0 0 4,721 Repayments of debt, maturities longer than 90 days 0 0 0 (814) Common stock issued 208 660 544 837 Common stock repurchased (1,042) (5,052) (2,976) (9,451) Common stock cash dividends paid (1,683) (1,363) (3,024) (2,481) Excess tax benefits from stock-based compensation 4 4 74 9 Other 0 0 0 (25) Net cash used in financing (2,513) (6,751) (5,382) (7,390) Investing Additions to property and equipment (498) (491) (934) (1,055) Acquisition of companies, net of cash acquired, and purchases of intangible and other assets (8,627) (69) (9,502) (69) Purchases of investments (10,047) (5,896) (21,346) (13,313) Maturities of investments 6,061 1,836 8,886 2,706 Sales of investments 7,835 2,603 15,371 4,030 Securities lending payable (292) 447 (358) 1,174 Net cash used in investing (5,568) (1,570) (7,883) (6,527) Effect of exchange rates on cash and cash equivalents (52) (3) (90) 55 Net change in cash and cash equivalents (2,271) (4,138) 1,000 (1,482) Cash and cash equivalents, beginning of period 12,881 8,161 9,610 5,505 Cash and cash equivalents, end of period $ 10,610 $ 4,023 $ 10,610 $ 4,023 Segment Revenue & OI MICROSOFT CORPORATION Back to Main SEGMENT REVENUE AND OPERATING INCOME (LOSS) (In millions) Three Months Ended December 31, Six Months Ended December 31, 2011 2010 2011 2010 Revenue Windows & Windows Live Division $ 4,736 $ 5,056 $ 9,604 $ 9,843 Server and Tools 4,772 4,288 9,022 8,149 Online Services Division 784 713 1,425 1,260 Microsoft Business Division 6,279 6,110 11,886 11,312 Entertainment and Devices Division 4,237 3,698 6,198 5,493 Unallocated and other 77 88 122 91 Consolidated $ 20,885 $ 19,953 $ 38,257 $ 36,148 Operating income (loss) Windows & Windows Live Division $ 2,850 $ 3,214 $ 6,101 $ 6,502 Server and Tools 1,996 1,711 3,593 3,248 Online Services Division (458) (559) (971) (1,132) Microsoft Business Division 4,152 4,087 7,839 7,570 Entertainment and Devices Division 528 666 877 1,050 Corporate-level activity (1,074) (954) (2,242) (1,957) Consolidated $ 7,994 $ 8,165 $ 15,197 $ 15,281 Historical Income Statement Microsoft Corporation Back to Main Quarterly Income Statements (In millions, except earnings per share) Q1-921 Q2-921 Q3-921 Q4-921 Q1-931 Q2-931 Q3-931 Q4-931 Q1-941 Q2-941 Q3-941 Q4-941 Q1-951 Q2-951 Q3-951 Q4-951 Q1-961 Q2-961 Q3-961 Q4-961 Q1-971 Q2-971 Q3-971 Q4-971 Q1-981 Q2-981 Q3-981 Q4-981 Q1-991 Q2-991 Q3-991 Q4-991 Q1-001 Q2-001 Q3-001 Q4-001 Q1-011 Q2-011 Q3-011 Q4-011 Q1-023 Q2-023 Q3-023 Q4-023 Q1-033 Q2-033 Q3-033 Q4-033 Q1-043 Q2-043 Q3-043 Q4-043 Q1-053 Q2-053 Q3-053 Q4-053 Q1-063 Q2-063 Q3-063 Q4-063 Q1-072 Q2-072 Q3-072 Q4-072 Q1-082 Q2-082 Q3-082 Q4-082 Q1-09 Q2-09 Q3-09 Q4-09 Q1-10 Q2-10 Q3-10 Q4-10 Q1-11 Q2-11 Q3-11 Q4-11 Q1-12 Q2-12 Revenue $ 584 $ 686 $ 686 $ 821 $ 824 $ 945 $ 969 $ 1,048 $ 996 $ 1,146 $ 1,261 $ 1,311 $ 1,270 $ 1,516 $ 1,627 $ 1,662 $ 2,085 $ 2,287 $ 2,311 $ 2,367 $ 2,405 $ 2,808 $ 3,365 $ 3,358 $ 3,334 $ 3,792 $ 3,984 $ 4,152 $ 4,193 $ 5,195 $ 4,595 $ 5,764 $ 5,384 $ 6,112 $ 5,656 $ 5,804 $ 5,766 $ 6,550 $ 6,403 $ 6,577 $ 6,126 $ 7,741 $ 7,245 $ 7,253 $ 7,746 $ 8,541 $ 7,835 $ 8,065 $ 8,215 $ 10,153 $ 9,175 $ 9,292 $ 9,189 $ 10,818 $ 9,620 $ 10,161 $ 9,741 $ 11,837 $ 10,900 $ 11,804 $ 10,811 $ 12,542 $ 14,398 $ 13,371 $ 13,762 $ 16,367 $ 14,454 $ 15,837 $ 15,061 $ 16,629 $ 13,648 $ 13,099 $ 12,920 $ 19,022 $ 14,503 $ 16,039 $ 16,195 $ 19,953 $ 16,428 $ 17,367 $ 17,372 $ 20,885 Operating expenses: Cost of revenue 126 139 139 177 168 191 202 224 228 260 284 305 284 331 355 376 522 566 541 516 482 558 583 547 534 613 640 673 649 788 708 669 712 756 752 782 825 864 899 867 978 1,691 1,567 1,463 1,344 2,137 1,274 1,304 1,480 2,344 1,411 1,481 1,405 1,875 1,363 1,388 1,253 2,239 2,028 2,130 1,696 3,620 2,140 3,237 2,675 3,543 2,514 2,866 2,848 3,907 2,814 2,586 2,842 3,628 2,755 3,170 3,139 4,833 3,897 3,708 3,777 5,638 Research and development 4 74 86 90 102 105 111 116 138 134 150 156 170 178 199 219 264 278 305 337 406 417 449 474 523 899 648 611 739 651 715 664 940 813 898 974 1,087 956 990 1,069 1,364 1,398 1,595 1,474 1,832 1,707 1,515 1,692 1,681 1,611 2,971 1,538 1,659 1,530 1,421 1,482 1,664 1,515 1,591 1,617 1,861 1,786 1,637 1,750 1,948 1,837 1,885 2,035 2,407 2,283 2,290 2,212 2,225 2,065 2,079 2,220 2,350 2,196 2,185 2,269 2,393 2,329 2,371 Sales and marketing 160 193 184 221 237 288 283 278 256 279 300 300 320 404 436 404 514 554 572 545 518 639 639 615 692 764 731 700 685 798 842 913 922 1,013 1,010 1,181 1,038 1,290 1,198 1,359 1,457 1,676 1,449 1,670 1,415 2,159 1,700 2,288 1,505 2,467 1,928 2,409 1,664 2,122 2,070 2,707 1,945 2,689 2,362 2,822 2,234 3,038 2,914 3,355 2,683 3,420 3,274 3,883 3,044 3,662 2,981 3,192 2,790 3,619 3,203 3,602 2,806 3,825 3,393 3,916 2,900 3,762 General and administrative 5 21 22 22 25 25 29 30 35 35 42 41 48 51 62 68 86 63 76 87 90 86 81 101 94 95 106 104 128 101 154 152 308 148 514 185 203 170 212 239 236 286 885 343 329 252 497 425 1,252 471 896 3,020 610 1,096 651 1,376 1,413 982 661 1,005 1,110 664 814 983 868 718 1,066 2,341 1,002 887 831 1,203 1,109 741 1,183 1,152 987 938 945 1,160 1,179 1,163 1,120 Other expenses (income) 3 2 3 3 1 (2) 2 6 4 2 4 6 2 5 5 4 4 (23) (4) 42 49 46 84 80 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Total operating expenses 384 442 438 528 536 617 633 681 657 733 785 829 835 1,001 1,083 1,134 1,381 1,478 1,533 1,599 1,552 1,773 1,881 1,859 2,220 2,131 2,086 2,240 2,086 2,455 2,366 2,830 2,595 3,181 2,921 3,253 2,989 3,356 3,405 3,826 4,119 5,847 4,833 5,294 4,718 6,308 5,091 6,525 5,067 8,678 7,897 6,159 5,695 6,069 6,291 7,172 5,695 7,180 7,012 7,923 6,380 9,109 7,787 9,408 7,913 9,914 10,164 10,158 9,062 10,690 9,210 9,112 8,438 10,509 9,330 10,109 9,079 11,788 10,719 11,196 10,169 12,891 Operating income 200 244 248 293 288 328 336 367 339 413 476 482 435 515 544 528 704 809 778 768 853 1,035 1,484 1,499 1,114 1,661 1,898 1,912 2,107 2,740 2,229 2,934 2,789 2,931 2,735 2,551 2,777 3,194 2,998 2,751 2,007 1,894 2,412 1,959 3,028 2,233 2,744 1,540 3,148 1,475 1,278 3,133 3,494 4,749 3,329 2,989 4,046 4,657 3,888 3,881 4,431 3,433 6,611 3,963 5,849 6,453 4,290 5,679 5,999 5,939 4,438 3,987 4,482 8,513 5,173 5,930 7,116 8,165 5,709 6,171 7,203 7,994 Other income (expense) 12 14 14 16 19 19 21 23 23 25 26 28 36 42 48 65 66 76 86 92 92 105 119 127 88 109 159 176 396 311 720 454 531 760 878 1,100 1,075 723 660 (2,653) (1,010) 516 728 (631) 19 363 453 674 753 837 1,001 571 279 420 496 872 506 480 427 377 610 372 360 321 367 367 520 289 (8) (301) (388) 155 283 370 168 94 114 332 316 148 103 245 Noncontinuing items 0 0 0 0 0 0 0 0 0 0 (120) 30 0 0 0 (46) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Income before income taxes 212 258 262 309 307 347 357 390 362 438 382 540 471 557 592 547 770 885 864 860 945 1,140 1,603 1,626 1,202 1,770 2,057 2,088 2,503 3,051 2,949 3,388 3,320 3,691 3,613 3,651 3,852 3,917 3,658 98 997 2,410 3,140 1,328 3,047 2,596 3,197 2,214 3,901 2,312 2,279 3,704 3,773 5,169 3,825 3,861 4,552 5,137 4,315 4,258 5,041 3,805 6,971 4,284 6,216 6,820 4,810 5,968 5,991 5,638 4,050 4,142 4,765 8,883 5,341 6,024 7,230 8,497 6,025 6,319 7,306 8,239 Provision for income taxes 68 83 83 99 98 111 114 125 123 149 126 178 155 184 196 179 271 310 302 301 331 399 561 569 539 637 720 731 820 1,068 1,032 1,186 1,129 1,255 1,228 1,242 1,271 1,293 1,207 33 319 771 1,005 425 1,006 731 1,055 731 1,287 763 964 1,014 1,245 1,706 1,262 161 1,411 1,484 1,338 1,430 1,563 1,179 2,045 1,249 1,927 2,113 422 1,671 1,618 1,464 1,073 1,097 1,191 2,221 1,335 1,506 1,820 1,863 793 445 1,568 1,615 Income before accounting change 144 175 179 210 209 236 243 265 239 289 256 362 316 373 396 368 499 575 562 559 614 741 1,042 1,057 663 1,133 1,337 1,357 1,683 1,983 1,917 2,202 2,191 2,436 2,385 2,409 2,581 2,624 2,451 65 678 1,639 2,135 903 2,041 1,865 2,142 1,483 2,614 1,549 1,315 2,690 2,528 3,463 2,563 3,700 3,141 3,653 2,977 2,828 3,478 2,626 4,926 3,035 4,289 4,707 4,388 4,297 4,373 4,174 2,977 3,045 3,574 6,662 4,006 4,518 5,410 6,634 5,232 5,874 5,738 6,624 Cumulative effect of accounting change 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 (375) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Net income $ 144 $ 175 $ 179 $ 210 $ 209 $ 236 $ 243 $ 265 $ 239 $ 289 $ 256 $ 362 $ 316 $ 373 $ 396 $ 368 $ 499 $ 575 $ 562 $ 559 $ 614 $ 741 $ 1,042 $ 1,057 $ 663 $ 1,133 $ 1,337 $ 1,357 $ 1,683 $ 1,983 $ 1,917 $ 2,202 $ 2,191 $ 2,436 $ 2,385 $ 2,409 $ 2,206 $ 2,624 $ 2,451 $ 65 $ 678 $ 1,639 $ 2,135 $ 903 $ 2,041 $ 1,865 $ 2,142 $ 1,483 $ 2,614 $ 1,549 $ 1,315 $ 2,690 $ 2,528 $ 3,463 $ 2,563 $ 3,700 $ 3,141 $ 3,653 $ 2,977 $ 2,828 $ 3,478 $ 2,626 $ 4,926 $ 3,035 $ 4,289 $ 4,707 $ 4,388 $ 4,297 $ 4,373 $ 4,174 $ 2,977 $ 3,045 $ 3,574 $ 6,662 $ 4,006 $ 4,518 $ 5,410 $ 6,634 $ 5,232 $ 5,874 $ 5,738 $ 6,624 Preferred stock dividends 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 (1) (7) (7) (7) (7) (7) (7) (7) (7) (7) (7) (7) (6) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Net income available for common shareholders $ 144 $ 175 $ 179 $ 210 $ 209 $ 236 $ 243 $ 265 $ 239 $ 289 $ 256 $ 362 $ 316 $ 373 $ 396 $ 368 $ 499 $ 575 $ 562 $ 559 $ 614 $ 740 $ 1,035 $ 1,050 $ 656 $ 1,126 $ 1,330 $ 1,350 $ 1,676 $ 1,976 $ 1,910 $ 2,195 $ 2,184 $ 2,430 $ 2,385 $ 2,409 $ 2,206 $ 2,624 $ 2,451 $ 65 $ 678 $ 1,639 $ 2,135 $ 903 $ 2,041 $ 1,865 $ 2,142 $ 1,483 $ 2,614 $ 1,549 $ 1,315 $ 2,690 $ 2,528 $ 3,463 $ 2,563 $ 3,700 $ 3,141 $ 3,653 $ 2,977 $ 2,828 $ 3,478 $ 2,626 $ 4,926 $ 3,035 $ 4,289 $ 4,707 $ 4,388 $ 4,297 $ 4,373 $ 4,174 $ 2,977 $ 3,045 $ 3,574 $ 6,662 $ 4,006 $ 4,518 $ 5,410 $ 6,634 $ 5,232 $ 5,874 $ 5,738 $ 6,624 Basic earnings per share before accounting change $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.03 $ 0.03 $ 0.03 $ 0.03 $ 0.03 $ 0.03 $ 0.04 $ 0.03 $ 0.04 $ 0.04 $ 0.04 $ 0.05 $ 0.06 $ 0.06 $ 0.06 $ 0.06 $ 0.08 $ 0.11 $ 0.11 $ 0.07 $ 0.12 $ 0.14 $ 0.14 $ 0.17 $ 0.20 $ 0.19 $ 0.22 $ 0.21 $ 0.24 $ 0.23 $ 0.23 $ 0.24 $ 0.25 $ 0.23 $ 0.01 $ 0.06 $ 0.15 $ 0.20 $ 0.08 $ 0.19 $ 0.17 $ 0.20 $ 0.14 $ 0.24 $ 0.14 $ 0.12 $ 0.25 $ 0.23 $ 0.32 $ 0.24 $ 0.34 $ 0.29 $ 0.35 $ 0.29 $ 0.28 $ 0.35 $ 0.27 $ 0.51 $ 0.32 $ 0.46 $ 0.50 $ 0.47 $ 0.46 $ 0.48 $ 0.47 $ 0.33 $ 0.34 $ 0.40 $ 0.75 $ 0.46 $ 0.52 $ 0.63 $ 0.78 $ 0.62 $ 0.70 $ 0.68 $ 0.79 Diluted earnings per share before accounting change $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.03 $ 0.02 $ 0.03 $ 0.03 $ 0.04 $ 0.03 $ 0.04 $ 0.04 $ 0.04 $ 0.05 $ 0.06 $ 0.05 $ 0.05 $ 0.06 $ 0.07 $ 0.10 $ 0.10 $ 0.06 $ 0.11 $ 0.12 $ 0.13 $ 0.15 $ 0.18 $ 0.17 $ 0.20 $ 0.20 $ 0.22 $ 0.22 $ 0.22 $ 0.23 $ 0.24 $ 0.22 $ 0.01 $ 0.06 $ 0.15 $ 0.19 $ 0.08 $ 0.19 $ 0.17 $ 0.20 $ 0.14 $ 0.24 $ 0.14 $ 0.12 $ 0.25 $ 0.23 $ 0.32 $ 0.23 $ 0.34 $ 0.29 $ 0.34 $ 0.29 $ 0.28 $ 0.35 $ 0.26 $ 0.50 $ 0.31 $ 0.45 $ 0.50 $ 0.47 $ 0.46 $ 0.48 $ 0.47 $ 0.33 $ 0.34 $ 0.40 $ 0.74 $ 0.45 $ 0.51 $ 0.62 $ 0.77 $ 0.61 $ 0.69 $ 0.68 $ 0.78 Basic earnings per share $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.03 $ 0.03 $ 0.03 $ 0.03 $ 0.03 $ 0.03 $ 0.04 $ 0.03 $ 0.04 $ 0.04 $ 0.04 $ 0.05 $ 0.06 $ 0.06 $ 0.06 $ 0.06 $ 0.08 $ 0.11 $ 0.11 $ 0.07 $ 0.12 $ 0.14 $ 0.14 $ 0.17 $ 0.20 $ 0.19 $ 0.22 $ 0.21 $ 0.24 $ 0.23 $ 0.23 $ 0.21 $ 0.25 $ 0.23 $ 0.01 $ 0.06 $ 0.15 $ 0.20 $ 0.08 $ 0.19 $ 0.17 $ 0.20 $ 0.14 $ 0.24 $ 0.14 $ 0.12 $ 0.25 $ 0.23 $ 0.32 $ 0.24 $ 0.34 $ 0.29 $ 0.35 $ 0.29 $ 0.28 $ 0.35 $ 0.27 $ 0.51 $ 0.32 $ 0.46 $ 0.50 $ 0.47 $ 0.46 $ 0.48 $ 0.47 $ 0.33 $ 0.34 $ 0.40 $ 0.75 $ 0.46 $ 0.52 $ 0.63 $ 0.78 $ 0.62 $ 0.70 $ 0.68 $ 0.79 Diluted earnings per share $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.02 $ 0.03 $ 0.02 $ 0.03 $ 0.03 $ 0.04 $ 0.03 $ 0.04 $ 0.04 $ 0.04 $ 0.05 $ 0.06 $ 0.05 $ 0.05 $ 0.06 $ 0.07 $ 0.10 $ 0.10 $ 0.06 $ 0.11 $ 0.12 $ 0.13 $ 0.15 $ 0.18 $ 0.17 $ 0.20 $ 0.20 $ 0.22 $ 0.22 $ 0.22 $ 0.20 $ 0.24 $ 0.22 $ 0.01 $ 0.06 $ 0.15 $ 0.19 $ 0.08 $ 0.19 $ 0.17 $ 0.20 $ 0.14 $ 0.24 $ 0.14 $ 0.12 $ 0.25 $ 0.23 $ 0.32 $ 0.23 $ 0.34 $ 0.29 $ 0.34 $ 0.29 $ 0.28 $ 0.35 $ 0.26 $ 0.50 $ 0.31 $ 0.45 $ 0.50 $ 0.47 $ 0.46 $ 0.48 $ 0.47 $ 0.33 $ 0.34 $ 0.40 $ 0.74 $ 0.45 $ 0.51 $ 0.62 $ 0.77 $ 0.61 $ 0.69 $ 0.68 $ 0.78 1 Not restated for adoption of the current accounting guidance on stock-based compensation and no recast of gains (losses) on foreign currency remeasurement 2 Recast of gains (losses) on foreign currency remeasurement 3 No recast of gains (losses) on foreign currency remeasurement 4 Includes expensed acquired in-process technology 5 Includes employee severance Fin Operating Segment History Microsoft Corporation Back to Main Segment Revenue and Operating Income (Loss) (In millions) Revenue Q1-11 Q2-11 Q3-11 Q4-11 Fiscal Year 2011 Q1-12 Q2-12 Q3-12 Q4-12 Fiscal Year 2012 Windows & Windows Live Division $ 4,787 $ 5,056 $ 4,447 $ 4,743 $ 19,033 $ 4,868 $ 4,736 $ 9,604 Server and Tools 3,861 4,288 4,007 4,524 16,680 4,250 4,772 9,022 Online Services Division 547 713 667 680 2,607 641 784 1,425 Microsoft Business Division 5,202 6,110 5,329 5,873 22,514 5,607 6,279 11,886 Entertainment and Devices Division 1,795 3,698 1,935 1,487 8,915 1,961 4,237 6,198 Unallocated and other 3 88 43 60 194 45 77 122 Consolidated $ 16,195 $ 19,953 $ 16,428 $ 17,367 $ 69,943 $ 17,372 $ 20,885 $ 38,257 Operating Income (Loss) Q1-11 Q2-11 Q3-11 Q4-11 Fiscal Year 2011 Q1-12 Q2-12 Q3-12 Q4-12 Fiscal Year 2012 Windows & Windows Live Division $ 3,288 $ 3,214 $ 2,788 $ 2,904 $ 12,194 $ 3,251 $ 2,850 $ 6,101 Server and Tools 1,537 1,711 1,349 1,684 6,281 1,597 1,996 3,593 Online Services Division (573) (559) (775) (744) (2,651) (513) (458) (971) Microsoft Business Division 3,483 4,087 3,308 3,755 14,633 3,687 4,152 7,839 Entertainment and Devices Division 384 666 213 24 1,287 349 528 877 Corporate-level activity (1,003) (954) (1,174) (1,452) (4,583) (1,168) (1,074) (2,242) Consolidated $ 7,116 $ 8,165 $ 5,709 $ 6,171 $ 27,161 $ 7,203 $ 7,994 $ 15,197 Unearned Revenue Unearned Revenue by Business Back to Main Q1-061 Q2-061 Q3-061 Q4-061 Q1-07 Q2-07 Q3-07 Q4-07 Q1-08 Q2-08 Q3-08 Q4-08 Q1-09 Q2-09 Q3-09 Q4-09 Q1-10 Q2-10 Q3-10 Q4-10 Q1-112 Q2-112 Q3-112 Q4-112 Q1-12 Q2-12 Unearned Revenue ($ in millions) Windows & Windows Live Division $ 2,636 $ 2,661 $ 2,658 $ 2,851 $ 2,804 $ 3,953 $ 2,684 $ 2,875 $ 2,673 $ 2,620 $ 2,524 $ 2,738 $ 2,402 $ 2,178 $ 2,015 $ 2,345 $ 3,638 $ 1,736 $ 1,498 $ 1,701 $ 1,570 $ 1,467 $ 1,370 $ 1,782 $ 1,628 $ 1,525 Server and Tools 2,269 2,298 2,394 2,910 2,648 2,702 2,825 3,652 3,449 3,746 3,890 5,007 4,424 4,303 4,104 4,732 4,424 4,227 4,242 5,282 4,812 4,785 4,706 6,315 5,720 5,612 Microsoft Business Division 3,537 3,527 3,476 4,783 4,301 4,863 4,341 5,771 5,080 5,359 5,253 7,101 6,204 6,077 5,593 6,508 5,920 5,680 5,657 7,004 6,748 6,324 6,013 8,187 7,428 7,085 Other 364 352 373 358 344 343 433 348 370 453 472 451 447 508 600 699 786 885 863 843 789 841 930 836 882 1,112 Total $ 8,806 $ 8,838 $ 8,901 $ 10,902 $ 10,097 $ 11,861 $ 10,283 $ 12,646 $ 11,572 $ 12,178 $ 12,139 $ 15,297 $ 13,477 $ 13,066 $ 12,312 $ 14,284 $ 14,768 $ 12,528 $ 12,260 $ 14,830 $ 13,919 $ 13,417 $ 13,019 $ 17,120 $ 15,658 $ 15,334 Unearned Revenue (in %) Windows & Windows Live Division 30% 30% 30% 26% 28% 33% 26% 23% 23% 22% 21% 18% 18% 17% 16% 16% 25% 14% 12% 11% 11% 11% 11% 10% 10% 10% Server and Tools 26% 26% 27% 27% 26% 23% 28% 29% 30% 31% 32% 33% 33% 33% 33% 33% 30% 34% 35% 36% 35% 36% 36% 37% 37% 37% Microsoft Business Division 40% 40% 39% 44% 43% 41% 42% 45% 44% 43% 43% 46% 46% 46% 46% 46% 40% 45% 46% 47% 48% 47% 46% 48% 47% 46% Other 4% 4% 4% 3% 3% 3% 4% 3% 3% 4% 4% 3% 3% 4% 5% 5% 5% 7% 7% 6% 6% 6% 7% 5% 6% 7% Total 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 1 FY '06 amounts have been restated for FY '07 business segments. 2 FY '11 amounts have been recast for the FY '12 movement of Forefront Protection for Office from Server and Tools to the Microsoft Business Division. Yearly Income Statements Microsoft Corporation Back to Main Yearly Income Statements (In millions, except earnings per share) FY921 FY931 FY941 FY951 FY961 FY971 FY981 FY991 FY001 FY011 FY023 FY033 FY042 FY052 FY062 FY072 FY082 FY09 FY10 FY11 FY12 Revenue $ 2,777 $ 3,786 $ 4,714 $ 6,075 $ 9,050 $ 11,936 $ 15,262 $ 19,747 $ 22,956 $ 25,296 $ 28,365 $ 32,187 $ 36,835 $ 39,788 $ 44,282 $ 51,122 $ 60,420 $ 58,437 $ 62,484 $ 69,943 Operating expenses: Cost of revenue 581 785 1,077 1,346 2,145 2,170 2,460 2,814 3,002 3,455 5,699 6,059 6,596 6,031 7,650 10,693 11,598 12,155 12,395 15,577 Research and development 4 352 470 610 860 1,326 1,863 2,897 2,970 3,772 4,379 6,299 6,595 7,735 6,097 6,584 7,121 8,164 9,010 8,714 9,043 Sales and marketing 758 1,086 1,135 1,564 2,185 2,411 2,887 3,238 4,126 4,885 6,252 7,562 8,121 8,548 9,910 11,541 13,260 12,879 13,214 13,940 General and administrative 5 90 119 166 267 316 362 433 715 1,050 857 1,843 2,426 5,275 4,536 3,758 3,329 5,127 4,030 4,063 4,222 Other expenses 11 7 16 16 19 259 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Total operating expenses 1,792 2,467 3,004 4,053 5,991 7,065 8,677 9,737 11,950 13,576 20,093 22,642 27,727 25,212 27,902 32,684 38,149 38,074 38,386 42,782 Operating income 985 1,319 1,710 2,022 3,059 4,871 6,585 10,010 11,006 11,720 8,272 9,545 9,108 14,576 16,380 18,438 22,271 20,363 24,098 27,161 Other income (expense) 56 82 102 191 320 443 532 1,881 3,269 (195) (397) 1,509 3,088 2,052 1,882 1,663 1,543 (542) 915 910 Noncontinuing items 0 0 (90) (46) 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Income before income taxes 1,041 1,401 1,722 2,167 3,379 5,314 7,117 11,891 14,275 11,525 7,875 11,054 12,196 16,628 18,262 20,101 23,814 19,821 25,013 28,071 Provision for income taxes 333 448 576 714 1,184 1,860 2,627 4,106 4,854 3,804 2,520 3,523 4,028 4,374 5,663 6,036 6,133 5,252 6,253 4,921 Net income before accounting change 708 953 1,146 1,453 2,195 3,454 4,490 7,785 9,421 7,721 5,355 7,531 8,168 12,254 12,599 14,065 17,681 14,569 18,760 23,150 Cummulative effect of accounting change 0 0 0 0 0 0 0 0 0 (375) 0 0 0 0 0 0 0 0 0 0 Net Income 708 953 1,146 1,453 2,195 3,454 4,490 7,785 9,421 7,346 5,355 7,531 8,168 12,254 12,599 14,065 17,681 14,569 18,760 23,150 Preferred stock dividends 0 0 0 0 0 (15) (28) (28) (13) 0 0 0 0 0 0 0 0 0 0 0 Net income available for common shareholders $ 708 $ 953 $ 1,146 $ 1,453 $ 2,195 $ 3,439 $ 4,462 $ 7,757 $ 9,408 $ 7,346 $ 5,355 $ 7,531 $ 8,168 $ 12,254 $ 12,599 $ 14,065 $ 17,681 $ 14,569 $ 18,760 $ 23,150 Basic earnings per share before accounting change $ 0.08 $ 0.11 $ 0.13 $ 0.16 $ 0.23 $ 0.36 $ 0.46 $ 0.77 $ 0.91 $ 0.72 $ 0.50 $ 0.70 $ 0.76 $ 1.13 $ 1.21 $ 1.44 $ 1.90 $ 1.63 $ 2.13 $ 2.73 Diluted earnings per share before accounting change $ 0.08 $ 0.10 $ 0.12 $ 0.14 $ 0.21 $ 0.33 $ 0.42 $ 0.71 $ 0.85 $ 0.69 $ 0.48 $ 0.69 $ 0.75 $ 1.12 $ 1.20 $ 1.42 $ 1.87 $ 1.62 $ 2.10 $ 2.69 Basic earnings per share $ 0.08 $ 0.11 $ 0.13 $ 0.16 $ 0.23 $ 0.36 $ 0.46 $ 0.77 $ 0.91 $ 0.69 $ 0.50 $ 0.70 $ 0.76 $ 1.13 $ 1.21 $ 1.44 $ 1.90 $ 1.63 $ 2.13 $ 2.73 Diluted earnings per share $ 0.08 $ 0.10 $ 0.12 $ 0.14 $ 0.21 $ 0.33 $ 0.42 $ 0.71 $ 0.85 $ 0.66 $ 0.48 $ 0.69 $ 0.75 $ 1.12 $ 1.20 $ 1.42 $ 1.87 $ 1.62 $ 2.10 $ 2.69 1 Not restated for adoption of the current accounting guidance on stock-based compensation and no recast of gains (losses) on foreign currency remeasurement 2 Recast of gains (losses) on foreign currency remeasurement 3 No recast of gains (losses) on foreign currency remeasurement 4 Includes expensed acquired in-process technology 5 Includes employee severance Financial Modeling & Forecasting Jason MacMorran www.pncpa.com http://www.pncpa.com/� Presentation Outline I. Introduction and Learning Objectives II. Definitions and Standards Overview III. Uses for Financial Models IV. Basics of Financial Modeling V. Basics of Financial Analysis VI. Sensitivity and Scenario Analysis VII. Conclusion Learning Objectives Understand accounting standards related to prospective information. Discuss uses and applications for financial models, including uses as decision making tools. Understand basic design and creation of a financial model. Understand uses for financial analysis and sensitivity analysis. Definitions and Standards Overview Definitions and Standards Overview The words projection, forecast, pro forma, model, etc., are often used interchangeably relative to prospective information. In an accounting context, they have different meanings. Generally speaking, the projection, forecast, pro forma or budget will be the ‘output’, and the financial model will include the ‘inputs’ and ‘output’. This section is not intended to be authoritative or a detailed look at standards, but a general overview of what to look for and where to find it! Key Accounting Terms Forecast – presents an entity’s expected financial position, results of operations, and cash flows, based on responsible party’s assumptions reflecting conditions it expects to exist and actions it expects to take. Projection – presents an entity’s financial position, results of operations, and cash flows, based on one or more hypothetical (what ifs) assumptions provided by a responsible party. Hypothetical Assumption - an assumption used to present a condition or course of action that is not necessarily expected to occur, but is consistent with the purpose of the projection (i.e. expansion scenario). Key Accounting Terms Financial Analysis – practitioner develops assumptions, analyzes results, and recommends a course of action. Partial Presentation – a presentation of prospective financial information that excludes one or more of the items required for prospective financial statements. Responsible Party – person or persons responsible for assumptions underlying the prospective financial statements. Types of Presentations Prospective Information Prospective Financial Statements Forecast Projection Prospective, but not Financial Statements Partial presentations Financial analyses Not Prospective Information Pro forma, based on historical amounts Expired budgets Uses of Prospective Information General Use – use by persons with whom the responsible party is not directly negotiating, for example: Offering of debt or equity securities under SEC regulations. Offering of tax-exempt bonds. Limited Use – use only by the entity with whom the responsibly party is negotiating, for example: Private placement. Negotiating bank financing. Merger negotiations. Internal Use – solely for use by the responsible party. Uses of Prospective Information Type of Prospective Presentation Appropriate Uses General Use Limited Use Internal Use Forecast Yes Yes Yes Projection No Yes Yes Partial Presentation No Yes Yes Financial Analysis No Yes Yes Types of Engagements Third-party Use: Special disclaimer on current year budgets – practitioners may not be required to apply any procedures if they make certain disclaimers. Compilation – assembling prospective statements in conformity with presentation guidelines and issuing report. Agreed-upon procedures – varies by engagement, and can be very limited or quite extensive. Examination – evaluating assumptions and presentation of financial information and issuing report. Internal Use: Assembly – no type of assurance, does not require report. Independence Independence is required in examination and agreed-upon procedures engagements, but not in compilation or internal use engagements because no assurance is expressed. Exceptions Litigation and valuation projects often have exceptions to procedure and presentation rules, so long as they are properly disclaimed. Key Finance Terms Net Present Value – present value of expected future cash flows minus initial investment. Internal Rate of Return – discount rate at which investment has zero net present value. Resources Accounting AICPA Attestation Standards AICPA Guide for Prospective Financial Information AICPA Practice Aid 06-2 Preparing Financial Models PPC’s Guide to Forecasts and Projections Finance Brealey & Myers Principles of Corporate Finance Uses for Financial Models Background Financial models should tell a story. What is the business going to do? How is it going to do it? How is it reflected in the financials? Financial models capture the future operating, investing and financing activities that determine future profitability, financial position, and risk. Financial models should be comprehensive, internally consistent, and externally reasonable. Background Financial models can integrate elements of accounting, finance, economics, corporate psychology, and business philosophy. Accounting based models – compilations for investors / creditors. Finance / economics based models – decision making (net present value, internal rate of return, etc.). Psychology / Philosophy – In a ‘decision making’ model, how will competitors react to your planned actions? Does the plan involve risks you know you’re unwilling to take? Uses for Financial Models Financing (debt or equity) Buy vs. Lease Valuation Budgeting Business Plans Strategic Plans Expansion Merger / Acquisition Lost Profits Business Interruption Litigation Support Start-ups Contraction / Closure Financial Models in Everyday Life A ‘financial model’ does not have to be complex! Simple situations call for simple financial models: Buy vs. lease of vehicle Impact of change in interest rate on borrowing Payback period on an investment Complex situations call for complex financial models: Merger and acquisition pro-forma and future cost savings Entering new markets / launching new products Litigation Basics of Financial Modeling Basics of Financial Modeling Define the need Basic organization Sample construction Other layout considerations Define the Need What is the desired goal of the financial model? Launching a new product? Integrating a potential acquisition? Refinancing debt? Who is the expected user? Management? Investors? Bankers? What is the expected use? Internal? External? The above issues will govern the sophistication and reporting requirements of a financial model. Define the Need Presentation / engagement will depend on purpose and audience. May require complete financial statements over a defined time period. May require limited information over a defined time period. May require ‘one-time’ sources and uses of funds. Complete Financial Statements Most difficult and time consuming to prepare, but also most instructive to user: Balance Sheet – measures future liquidity and leverage Income Statement – measures future operating results Statement of Cash Flows – outlines future cash needs for growth (investment and borrowing) and returns to investors Collectively, complete financial statements can help measure return on investment and potential risks. Limited Information Most common, including: Prepare a twelve month budget Six months of start-up expenses Amortize a loan over five years Do not necessarily have to be financial statements! Sources and Uses of Funds Where is money coming from (bank debt, equity, etc). What is it going to be spent on (equipment, refinance, operating costs, etc). Often seen in refinancing and bond offerings. Contribution Cap % Sources Cash on Hand 500$ 0.6% Bank Revolver 0 0.0% Senior Bank Note 20,000 23.4% Subordinated Bank Note 5,000 5.8% Owner Contribution 10,000 11.7% New Equity 50,000 58.5% Total Sources 85,500$ 100.0% Uses Purchase new equipment 10,000$ 11.7% Operating costs 25,000 29.2% Office manager 40,000 46.8% Refinance Existing Debt 0 0.0% Working Capital 10,000 11.7% Cash 500 0.6% Total Uses 85,500$ 100.0% SOURCES AND USES Time Period Generally, time period should match the business life cycle. 5 year projection for a 20 year real estate deal may not be helpful. 20 year projection in a rapidly changing industry may not be relevant. How to handle changes in the economy? For litigation or damage oriented models, time period should correspond to period of damage. Basic Organization Most financial models include the following basic elements: Identification of the problem to be solved (buy vs. lease, expansion, new location, etc.) Key assumptions (sales growth, margins, capital expenditures, impact of competitors, etc.) Output / results (financial statements, net present value, go / no go decision, etc.) Sample Construction Problem - Client / Employer wants to expand an existing product into a new geographic market (new location). Which question is better: Will the project be profitable? Will the project provide an adequate return on investment? Accounting based financial statements will show profitability. Net present value / internal rate of return analysis will show return on investment. Sample Construction - Scope ‘Problem’ and expected use/users will define scope of financial model. In the case of expansion / new location, the following factors should be considered: Will be a long-term project Will likely require significant investment (equity and /or debt) Will likely require consideration of competitor reactions Based on this ‘problem’ and expected use/users, output should be complete presentation over a long-term, with consideration of ‘return’ on the investment. Sample Construction - Assumptions What are key assumptions? Will vary by client and industry May be impacted by current economic environment in short-term Critical points: Assumptions should be defined separately (i.e. a separate ‘tab’ in Excel workbook); often referred to as ‘projection drivers’ Assumptions should be reasonable and logical Assumptions should be supported by historical trends, industry trends, economic data, or other data (will discuss later) Bad assumptions = bad decisions! Sample Construction - Assumptions Sample Company Projection Assumptions Growth Rates Line Item Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Revenues 5% 5% 5% 5% 5% Salaries 5% 5% 5% 5% 5% Benefits 5% 5% 5% 5% 5% Supplies 5% 5% 5% 5% 5% Licenses 5% 5% 5% 5% 5% Utilities 5% 5% 5% 5% 5% Repairs and maintenance 5% 5% 5% 5% 5% Insurance 5% 5% 5% 5% 5% Telephone 5% 5% 5% 5% 5% Management fees 5% 5% 5% 5% 5% Miscellaneous 5% 5% 5% 5% 5% Accounts Receivable Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Assumed days outstanding 70 70 70 70 70 70 Accounts receivable turnover 5.21 5.21 5.21 5.21 5.21 5.21 Projected Net Sales $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469 Projected accounts receivable $ 182,793 $ 191,933 $ 201,529 $ 211,606 $ 222,186 $ 233,295 Accounts Payable Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Assumed days outstanding 14 14 14 14 14 14 Accounts payable turnover 26.07 26.07 26.07 26.07 26.07 26.07 Projected Operating Expenses, less Int & Depr $ 718,244 $ 751,156 $ 785,714 $ 822,000 $ 860,100 $ 900,105 Projected accounts payable $ 27,549 $ 28,811 $ 30,137 $ 31,529 $ 32,990 $ 34,525 Sample Construction – Income Statement Easiest place to start: Core assumptions relate to operations (growth rates, margins, etc.) Operations not ‘dependent’ on balance sheet or cash flows When appropriate, separate fixed and variable costs. If model is very detailed, consider separate worksheets for departments, revenues, cost of revenues, operating expenses, etc. Separating the model into smaller parts helps to catch errors! Sample Construction – Income Statement Sample Company Projected Income Statements Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Revenues $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469 Operating Expenses Salaries 286,043 300,345 315,362 331,131 347,687 365,071 Benefits 42,906 45,051 47,304 49,669 52,153 54,760 Supplies 187,585 196,964 206,812 217,153 228,011 239,411 Licenses 8,000 8,400 8,820 9,261 9,724 10,210 Lease 60,000 60,000 60,000 60,000 60,000 60,000 Utilities 12,000 12,600 13,230 13,892 14,586 15,315 Repairs and maintenance 59,710 62,696 65,830 69,122 72,578 76,207 Insurance 28,000 29,400 30,870 32,414 34,034 35,736 Telephone 3,000 3,150 3,308 3,473 3,647 3,829 Interest 3,375 2,758 2,113 1,440 736 - Management fees 30,000 31,500 33,075 34,729 36,465 38,288 Miscellaneous 1,000 1,050 1,103 1,158 1,216 1,276 Depreciation 140,533 140,533 140,533 150,533 150,533 - Total Operating Expenses 862,152 894,447 928,360 973,972 1,011,368 900,105 Operating Income 90,983 106,344 122,471 129,400 147,173 316,364 Less: Provision for Income Taxes (22,746) (26,586) (30,618) (32,350) (36,793) (79,091) Net Income $ 68,237 $ 79,758 $ 91,853 $ 97,050 $ 110,380 $ 237,273 Sample Construction – Balance Sheet Often more difficult to model, mostly because cash balances are ‘iterative’ (turn on Excel feature). Working capital (receivables, inventory, payables) projected from assumptions, such as days outstanding / turnover ratios. Capital expenditures need to support expected level of operations. Financing (debt / equity) will depend on capital needs, working capital requirements, etc. Retained earnings will roll from net income. Sample Construction – Balance Sheet Sample Company Projected Balance Sheets Year 1 Year 2 Year 3 Year 4 Year 5 Terminal ASSETS Current Assets Cash $ 366,367 $ 564,455 $ 773,599 $ 946,853 $ 1,182,298 $ 1,409,996 Accounts receivable 182,793 191,933 201,529 211,606 222,186 233,295 Total Current Assets 549,160 756,387 975,128 1,158,459 1,404,484 1,643,292 Fixed Assets Fixed assets, at cost 748,450 748,450 748,450 798,450 798,450 798,450 Accumulated depreciation (140,533) (281,066) (421,599) (572,132) (722,665) (722,665) Total Fixed Assets, net 607,917 467,384 326,851 226,318 75,785 75,785 Total Assets 1,157,077 1,223,771 1,301,979 1,384,777 1,480,269 1,719,077 LIABILITIES & EQUITY Liabilities Accounts payable 27,549 28,811 30,137 31,529 32,990 34,525 Notes payable 61,291 46,964 31,993 16,349 (0) (0) Total Liabilities 88,840 75,776 62,130 47,877 32,990 34,525 Equity Capital contributions 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 Retained earnings 68,237 147,996 239,849 336,899 447,279 684,552 Total Equity 1,068,237 1,147,996 1,239,849 1,336,899 1,447,279 1,684,552 Total Liabilities and Equity $ 1,157,077 $ 1,223,771 $ 1,301,979 $ 1,384,777 $ 1,480,269 $ 1,719,077 Sample Construction – Cash Flow Connect the parts from the Balance Sheet and Income Statement. Consider a ‘T=0’ time period for initial investments (capital expenditure, debt financing, equity financing, etc). Is there a minimum ‘days cash on hand’ to start with? This will influence financing requirements. Cash balances are ‘iterative’ (turn on Excel feature). Sample Construction – Cash Flow Sample Company Projected Cash Flows T=0 Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Cash Flows from Operating Activities Net Income $ - $ 68,237 $ 79,758 $ 91,853 $ 97,050 $ 110,380 $ 237,273 Adjust for non-cash items Depreciation - 140,533 140,533 140,533 150,533 150,533 - Adjust for changes in: (Increase) decrease in accounts receivable (174,089) (8,704) (9,140) (9,597) (10,076) (10,580) (11,109) Increase (decrease) in accounts payable 26,347 1,202 1,262 1,326 1,392 1,461 1,534 Net Cash Provided (Used) by Operations (147,742) 201,268 212,414 224,115 238,899 251,794 227,698 Cash Flows from Investing Activities Capital expenditures (748,450) - - - (50,000) - - Net Cash Used in Investing (748,450) - - - (50,000) - - Cash Flows from Financing Activities Loan proceeds 75,000 - - - - - - Member contributions 1,000,000 - - - - - - Principal payments - (13,709) (14,326) (14,971) (15,645) (16,349) - Net Cash Provided (Used) by Financing 1,075,000 (13,709) (14,326) (14,971) (15,645) (16,349) - Net Increase in Cash 178,808 187,559 198,088 209,144 173,254 235,445 227,698 Cash, Beginning of Year - 178,808 366,367 564,455 773,599 946,853 1,182,298 Cash, End of Year $ 178,808 $ 366,367 $ 564,455 $ 773,599 $ 946,853 $ 1,182,298 $ 1,409,996 Sample Construction – Iterative Calculations Sample Construction – Cash Flows Most overlooked in financial modeling, but most important. Statement of Cash Flows shows: Timing of capital expenditures for growth Additional borrowing needs Ability to provide return on investment Cash flows available to investors (free cash flows) is a core element in financial decision making, and is essential to a net present value analysis or an internal rate of return analysis. Sample Construction – Decision Making Sample Company Decision Making T=0 Year 1 Year 2 Year 3 Year 4 Year 5 Terminal EBIT N/A $ 94,358 $ 109,103 $ 124,584 $ 130,840 $ 147,909 $ 316,364 Less: tax on EBIT (23,590) (27,276) (31,146) (32,710) (36,977) (79,091) After-tax EBIT 70,769 81,827 93,438 98,130 110,932 237,273 Add: depreciation 140,533 140,533 140,533 150,533 150,533 - Less: capital expenditures - - - (50,000) - - Less: working capital requirements (7,502) (7,877) (8,271) (8,685) (9,119) (9,575) Free Cash Flows to Debt and Equity $ 203,799 $ 214,483 $ 225,700 $ 189,978 $ 252,346 $ 227,698 Free Cash Flows to Debt and Equity $ (1,075,000) $ 203,799 $ 214,483 $ 225,700 $ 189,978 $ 252,346 $ 1,138,490 Present Value (1,075,000) 186,042 163,162 143,080 100,362 111,091 501,203 Net Present Value $ 129,942 Required Rate of Return 20% Other Layout Consideration Have a summary tab that provides the ‘answer’ concisely. Have designated ‘input only’ tabs and clearly delineate variables. Link and cross-link worksheets Use Excel formulas: If/then Average and median Lookup Forecast Trend Basics of Financial Analysis Why Perform Financial Analysis? Projection assumptions can be supported by historical financial analysis. Basic financial analysis tools include: Common size financial statements Ratio analysis Trend analysis Industry comparatives Financial analysis tools and techniques can: Isolate trends (positive and negative). Help identify strengths and weaknesses. Common Size Financial Statements Income Statement line items as a percentage of revenues: Identify changes in cost of sales, gross profits, and operating expense margins over time. Balance Sheet line items as a percentage of total assets: Identify changes in (and composition of) current assets and liabilities, fixed assets, debt, and other balances sheet items over time. Are margins and compositions expected to stay the same in the future? Why have margins changed? Were the changes expected? Ratio Analysis Ratio analysis can assist with understanding and projecting: Growth Cost control Asset turnover Profitability Risk How these ratios have changed (or not) over time. How do ratios compare to benchmarks? Integra Information (www.integrainfo.com) RMA Statement Studies (www.statementstudies.org) Trade associations http://www.integrainfo.com/� http://www.statementstudies.org/� Ratio Analysis Growth Ratios Growth in revenues Growth in expenses Growth in earnings Cost Control Ratios Often common size income statement / margins Turnover Ratios Receivable turnover Inventory turnover Payable turnover Total asset turnover Ratio Analysis - Example Sample Company Projection Assumptions Growth Rates Line Item Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Revenues 5% 5% 5% 5% 5% Salaries 5% 5% 5% 5% 5% Benefits 5% 5% 5% 5% 5% Supplies 5% 5% 5% 5% 5% Licenses 5% 5% 5% 5% 5% Utilities 5% 5% 5% 5% 5% Repairs and maintenance 5% 5% 5% 5% 5% Insurance 5% 5% 5% 5% 5% Telephone 5% 5% 5% 5% 5% Management fees 5% 5% 5% 5% 5% Miscellaneous 5% 5% 5% 5% 5% Accounts Receivable Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Assumed days outstanding 70 70 70 70 70 70 Accounts receivable turnover 5.21 5.21 5.21 5.21 5.21 5.21 Projected Net Sales $ 953,135 $ 1,000,792 $ 1,050,831 $ 1,103,373 $ 1,158,542 $ 1,216,469 Projected accounts receivable $ 182,793 $ 191,933 $ 201,529 $ 211,606 $ 222,186 $ 233,295 Accounts Payable Year 1 Year 2 Year 3 Year 4 Year 5 Terminal Assumed days outstanding 14 14 14 14 14 14 Accounts payable turnover 26.07 26.07 26.07 26.07 26.07 26.07 Projected Operating Expenses, less Int & Depr $ 718,244 $ 751,156 $ 785,714 $ 822,000 $ 860,100 $ 900,105 Projected accounts payable $ 27,549 $ 28,811 $ 30,137 $ 31,529 $ 32,990 $ 34,525 Ratio Analysis Profitability Ratios Return on Assets (ROA) Return on Equity (ROE) Return on Investment (ROI) Risk Ratios Leverage Interest coverage Current ratio Financial Analysis Beware the pitfalls: Ratios can be complicated by accounting methods: How do comparable companies report inventory, depreciation, etc. GAAP allows for different treatments, and different accounting treatments can skew ratio output. Ratios are ‘industry dependent’: CPA firms use different ratios than manufacturing firms Be cautious of ‘rules of thumb’ Financial analysis tools are diagnostic; they do a better job of raising questions than providing answers! Sensitivity and Scenario Analysis Sensitivity Analysis Sensitivity Analysis How does the projection respond to different ‘shocks’? Important to know which variables and assumptions are most influential in your model. Scenario Analysis Run multiple scenarios: Measure outcomes of events with different influences. Often used to see best case and worst case. Used to establish a range of cash flows for the company, but does not necessarily increase confidence in ‘decision’. Monte Carlo Analysis Monte Carlo analysis: Measures outcomes of events with random influences and assigns probabilities based on frequency. Can run tens of thousands of potential scenarios in seconds. Does not give you ‘THE’ answer, but gives confidence in the range of answers for a set of variables. Conclusion Parting Thoughts Be sure to define the problem and expected use. Support the assumptions. Consider alternative scenarios. Provide the right output for the expected user. Questions Contact Information Jason MacMorran, CPA/ABV, CVA, CFF, MS Postlethwaite & Netterville, APAC 8550 United Plaza Blvd., Suite 1001 Baton Rouge, LA 70809 225.408.4766 [email protected] mailto:[email protected]� Slide Number 1 Presentation Outline Learning Objectives Definitions and Standards Overview Definitions and Standards Overview Key Accounting Terms Key Accounting Terms Types of Presentations Uses of Prospective Information Uses of Prospective Information Types of Engagements Independence Exceptions Key Finance Terms Resources Uses for Financial Models Background Background Uses for Financial Models Financial Models in Everyday Life Basics of Financial Modeling Basics of Financial Modeling Define the Need Define the Need Complete Financial Statements Limited Information Sources and Uses of Funds Time Period Basic Organization Sample Construction Sample Construction - Scope Sample Construction - Assumptions Sample Construction - Assumptions Sample Construction – Income Statement Sample Construction – Income Statement Sample Construction – Balance Sheet Sample Construction – Balance Sheet Sample Construction – Cash Flow Sample Construction – Cash Flow Sample Construction – Iterative Calculations Sample Construction – Cash Flows Sample Construction – Decision Making Other Layout Consideration Basics of Financial Analysis Why Perform Financial Analysis? Common Size Financial Statements Ratio Analysis Ratio Analysis Ratio Analysis - Example Ratio Analysis Financial Analysis Sensitivity and Scenario Analysis Sensitivity Analysis Scenario Analysis Monte Carlo Analysis Conclusion Parting Thoughts Questions Contact Information