How do you forecast the income statement, balance sheet and cash flow using pro forma? I need help with this excel file attached, the last 3 tabs in the file.

These spreadsheet exhibits relate to the case Teuer Furniture (A): Discounted Cash Flow Valuation, Case #KEL778. 2014 by the Kellogg School of Management at Northwestern University. This case was prepared by Professor Mitchell A. Petersen. Teuer Furniture is a fictional company whose profile was created based on data from real industry leaders. Cases are developed solely as the basis for class discussion. Cases are not intended to serve as endorsements, sources of primary data, or illustrations of effective or ineffective management. To order copies or request permission to reproduce materials, call 800545-7685 (or 617-783-7600 outside the United States or Canada) or e-mail custserv@hbsp.harvard.edu. No part of this publication may be reproduced, stored in a retrieval system, used in a spreadsheet, or transmitted in any form or by any means electronic, mechanical, photocopying, recording, or otherwisewithout the permission of Kellogg Case Publishing. Exhibit 1: Teuer Furnitures Showrooms and Square Footage Stores Opened 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 Total Stores 1 3 4 5 6 2 1 2 2 3 2 2 2 1 4 8 13 19 21 22 24 26 29 31 33 35 SQ FT Opened 17,200 17,600 18,500 21,100 22,100 15,500 14,500 14,700 15,400 16,300 16,000 16,000 15,000 Total SQ FT 17,200 70,000 144,000 249,500 382,100 413,100 427,600 457,000 487,800 536,700 568,700 600,700 630,700 40 700,000 35 600,000 30 500,000 25 400,000 20 300,000 15 200,000 10 100,000 5 0 2003 2005 2007 2009 Number of Showrooms 2011 2013 Total Square Feet 0 2015 Exhibit 2: Economic Indicators: GDP and Furniture Sales Growth CPI index Inflation (CPI) GDP (T$) GDP growth Furniture sales ($B) Furniture sales growth 2002 180.9 10,642 94.44 2003 184.3 1.9% 11,142 4.7% 96.7 2.4% 2004 190.3 3.3% 11,853 6.4% 103.8 7.3% 2005 196.8 3.4% 12,623 6.5% 109.1 5.2% 2006 201.8 2.5% 13,377 6.0% 112.8 3.4% 2007 210.0 4.1% 14,029 4.9% 111.1 -1.5% 2008 210.2 0.1% 14,292 1.9% 99.6 -10.4% 2009 215.9 2.7% 13,974 -2.2% 86.3 -13.4% 2010 219.2 1.5% 14,499 3.8% 87.3 1.2% 2011 225.7 3.0% 15,076 4.0% 90.1 3.2% 2012 229.6 1.7% 15,685 4.0% 93.1 3.4% 10.0% 5.0% 0.0% 2003 2005 2007 2009 2011 2013 2015 -5.0% -10.0% -15.0% GDP Growth Furniture Sales Growth Inflation (CPI) 2017 2013 233.5 1.7% 16,238 3.5% 95.9 3.0% 2014 237.7 1.8% 17,049 5.0% 98.6 2.8% 2015 242.3 1.9% 18,012 5.6% 101.2 2.6% 2016 247.4 2.1% 19,021 5.6% 103.6 2.4% 2017 252.1 1.9% 20,078 5.6% 105.9 2.2% 2018 256.6 1.8% 21,101 5.1% 108.0 2.0% 2019 261.3 1.8% 110.2 2.0% Exhibit 3: Teuer Furniture Income Statement 2003 Sales Cost of goods sold Selling, general and admin Advertising Depreciation Lease Corporate expense Taxes Net income Parameters: Corporate tax rate 40% 2004 2,057 -1,463 -410 -203 -72 -250 -185 0 -526 2005 10,415 -7,057 -1,812 -1,019 -307 -1,080 -729 0 -1,588 2006 26,701 -17,424 -4,958 -2,411 -669 -2,330 -1,602 0 -2,693 2007 51,540 -30,730 -9,230 -4,588 -1,264 -4,240 -3,092 0 -1,604 2008 79,191 -43,834 -13,576 -6,585 -2,075 -6,770 -4,751 0 1,600 2009 90,680 -47,290 -14,679 -7,532 -2,188 -7,380 -5,441 -543 5,626 2010 111,451 -49,068 -16,471 -8,367 -2,032 -7,712 -5,573 -8,892 13,337 2011 134,093 -55,487 -18,698 -9,545 -1,820 -8,450 -6,705 -13,356 20,033 2012 148,218 -61,955 -21,253 -10,744 -1,450 -9,320 -7,411 -14,434 21,651 Exhibit 4: Teuer Furniture Balance Sheet 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 Accounts Receivable Inventory Total Current Assets PPE Total Assets 0 671 671 358 1,029 751 3,151 3,902 1,461 5,364 3,479 8,216 11,695 2,969 14,663 8,675 15,125 23,800 5,270 29,070 16,946 20,908 37,853 8,065 45,918 25,272 21,715 46,987 6,914 53,900 29,059 24,868 53,927 5,119 59,046 36,625 26,808 63,433 3,839 67,272 42,421 29,216 71,637 3,142 74,779 48,200 33,343 81,543 3,920 85,463 Liabilities and Equity Accounts Payable Accrued Expenses Total Current Liabilities Debt Equity Total Liabilities and Equity 236 29 265 0 764 1,029 1,158 145 1,303 0 4,061 5,364 2,781 358 3,139 0 11,525 14,663 5,119 658 5,777 0 23,293 29,070 7,228 958 8,186 0 37,732 45,918 7,767 1,055 8,822 0 45,079 53,900 8,277 1,203 9,480 0 49,565 59,046 9,133 1,359 10,493 0 56,779 67,272 9,986 1,519 11,505 0 63,274 74,779 11,364 1,682 13,046 0 72,417 85,463 Assets Balance sheet check Exhibit 5: Teuer Furniture Income Statement for Individual Showrooms # 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Sales 2,057 3,754 5,224 6,148 6,341 5,503 5,636 5,871 6,034 6,235 6,430 6,618 6,799 6,971 7,133 7,299 CGS 1,463 2,309 3,018 2,394 2,753 2,311 2,194 2,150 2,416 2,486 2,564 2,639 2,711 2,779 2,844 2,910 SGA 410 640 857 856 815 765 767 687 791 848 874 900 925 948 970 993 203 334 399 434 422 423 391 401 457 447 461 474 487 499 511 523 72 72 72 72 72 0 0 0 65 65 65 65 65 250 250 250 250 250 250 302 302 302 302 302 302 340 340 340 340 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sales 6,661 11,907 15,158 16,488 15,446 16,187 17,279 17,869 18,464 19,041 19,599 20,134 20,643 21,123 21,615 CGS 4,748 7,428 7,894 7,216 6,031 6,675 6,691 7,176 7,362 7,592 7,815 8,028 8,231 8,422 8,618 SGA 2003 Avg Size 1 17,200 Advertising Depreciation Lease 2004 3 17,600 2019 1,171 2,148 2,631 2,323 2,239 2,224 2,395 2,319 2,511 2,589 2,665 2,738 2,807 2,873 2,939 Advertising 685 971 1,266 1,213 1,235 1,241 1,245 1,287 1,323 1,364 1,404 1,442 1,479 1,513 1,548 Depreciation 235 235 235 235 235 0 0 0 187 187 187 187 187 830 830 830 830 830 830 1,003 1,003 1,003 1,003 1,003 1,003 1,130 1,130 1,130 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sales 9,570 16,651 19,785 20,951 22,847 24,451 24,499 25,315 26,108 26,872 27,605 28,303 28,962 29,636 CGS 6,978 10,917 11,430 8,499 9,387 9,857 9,042 10,094 10,410 10,715 11,007 11,285 11,548 11,817 SGA 1,953 2,977 3,371 3,039 3,021 3,228 3,693 3,443 3,550 3,654 3,754 3,849 3,939 4,030 1,041 1,488 1,587 1,598 1,605 1,645 1,793 1,813 1,870 1,925 1,977 2,027 2,074 2,123 363 363 363 363 363 0 0 301 301 301 301 301 1,250 1,250 1,250 1,250 1,250 1,250 1,510 1,510 1,510 1,510 1,510 1,510 1,701 1 2 3 4 5 6 7 8 9 10 11 12 13 Sales 13,583 21,142 23,592 29,702 34,105 34,824 35,984 37,110 38,197 39,239 40,231 41,167 42,125 CGS 9,526 11,229 13,594 9,902 13,259 13,932 14,348 14,797 15,230 15,646 16,041 16,415 16,797 2,767 4,016 4,237 3,780 4,895 4,946 4,893 5,047 5,194 5,336 5,471 5,598 5,729 1,400 1,784 1,964 2,014 2,327 2,287 2,577 2,658 2,736 2,811 2,882 2,949 3,017 490 490 490 490 490 2,151 2,151 2,151 2,151 2,151 2,151 2,422 Lease 2005 4 18,500 Advertising Depreciation Lease 2006 5 21,100 SGA Advertising Depreciation 594 2007 6 22,100 594 594 594 594 0 1,910 Lease 1,910 1,910 1,910 1,910 1,910 1,701 1 2 3 4 5 6 7 8 9 10 11 12 Sales 15,435 22,001 30,466 39,628 43,541 45,542 46,967 48,343 49,662 50,917 52,102 53,315 CGS 11,205 14,801 16,422 15,232 17,856 18,159 18,727 19,276 19,802 20,302 20,775 21,258 SGA 3,050 3,878 5,471 5,166 5,917 6,193 6,387 6,574 6,754 6,924 7,085 7,250 1,578 1,986 2,458 2,748 3,109 3,262 3,364 3,463 3,557 3,647 3,732 3,819 812 812 812 812 812 655 655 655 655 2,530 2,530 2,530 2,530 2,530 2,849 2,849 2,849 2,849 Advertising Depreciation Lease 2008 2 15,500 2,530 2,849 2,849 1 2 3 4 5 6 7 8 9 10 11 Sales 3,187 5,176 7,244 9,257 10,461 10,921 11,241 11,547 11,839 12,115 12,397 CGS 2,053 3,467 4,421 3,764 4,171 4,354 4,482 4,604 4,721 4,830 4,943 SGA 522 951 1,225 1,243 1,423 1,485 1,529 1,570 1,610 1,647 1,686 Advertising 326 499 598 675 749 782 805 827 848 868 888 Depreciation 185 185 185 185 185 152 152 152 610 610 610 610 610 610 687 687 687 687 687 1 2 3 4 5 6 7 8 9 10 Sales 1,437 2,507 3,540 4,480 5,053 5,264 5,408 5,545 5,674 5,806 CGS 1,021 1,652 1,887 1,786 2,015 2,099 2,156 2,211 2,262 2,315 SGA 257 480 615 609 687 716 735 754 772 790 158 227 303 321 362 377 387 397 406 416 64 64 280 315 315 315 315 Lease 2009 1 14,500 Advertising Depreciation 79 2010 2 14,700 79 79 79 79 280 Lease 280 280 280 280 1 2 3 4 5 6 7 8 9 Sales 3,008 5,300 7,286 9,202 10,359 10,771 11,044 11,301 11,564 CGS 2,226 3,526 4,096 3,669 4,130 4,295 4,403 4,506 4,611 SGA 623 1,028 1,259 1,251 1,409 1,465 1,502 1,537 1,573 Advertising 353 473 596 659 742 772 791 809 828 Depreciation 150 150 150 150 150 565 565 565 565 565 565 636 636 1 2 3 4 5 6 7 8 Sales 3,354 5,878 8,065 10,166 11,421 11,853 12,129 12,412 CGS 2,357 3,742 4,533 4,054 4,554 4,726 4,836 4,949 SGA 701 1,077 1,394 1,383 1,553 1,612 1,649 1,688 Advertising 358 522 659 728 818 849 869 889 Depreciation 160 160 160 160 160 610 610 610 610 610 610 687 687 1 2 3 4 5 6 7 Sales 5,379 9,409 12,885 16,210 18,176 18,827 19,265 CGS 3,812 5,989 7,243 6,464 7,248 7,507 7,682 SGA 1,039 1,723 2,227 2,204 2,472 2,560 2,620 Advertising 569 836 1,053 1,161 1,302 1,349 1,380 Depreciation 259 259 259 259 259 1,000 1,000 1,000 1,000 1,000 1,000 1,126 1 2 3 4 5 6 Sales 3,622 6,324 8,644 10,853 12,145 12,580 CGS 2,567 4,026 4,859 4,327 4,843 5,016 SGA 700 1,158 1,494 1,476 1,652 1,711 Advertising 383 562 707 777 870 901 Depreciation 172 172 172 172 172 668 668 668 668 668 1 2 3 4 5 Sales 3,731 6,502 8,868 11,114 12,437 CGS 2,644 4,138 4,985 4,431 4,959 SGA 721 1,191 1,533 1,511 1,691 Advertising 395 578 725 796 891 Depreciation 175 175 175 175 175 682 682 682 682 682 Lease 2011 2 15,400 Lease 2012 3 16,300 Lease 2013 2 16,000 Lease 2014 2 16,000 Lease 2015 2 15,000 123 636 668 1 2 3 4 Sales 3,597 6,256 8,516 10,672 CGS 2,549 3,982 4,787 4,255 SGA 695 1,146 1,472 1,451 Advertising 381 556 696 764 Depreciation 167 167 167 167 652 652 652 652 Lease Parameters: Lease rate (2013) Lease inflation 20.88 2.0% Exhibit 6: Teuer Furniture Balance Sheet for Individual Showrooms # 2003 Avg Size 1 17,200 Cap expenditure 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 358 Accts receivable 324 751 1,187 1,665 1,975 2,040 1,700 1,745 1,742 1,902 2,022 2,086 2,147 2,205 2,261 2,314 Inventory 671 1,090 1,330 1,206 1,167 980 972 1,082 1,027 1,090 1,219 1,255 1,289 1,322 1,353 1,384 Accts payable 236 396 448 405 403 350 345 370 369 390 419 431 443 454 465 475 Accr expenses 29 49 62 61 61 53 54 57 58 58 64 66 68 70 71 73 260 195 130 65 0 9 10 11 12 13 14 6,357 6,531 6,696 6,852 PPE 2004 3 17,600 1 2 3 4 5 6 7 8 2,292 Cap expenditure 0 3,870 5,029 5,526 5,276 5,599 5,612 5,691 5,989 6,176 1,175 Accts receivable Inventory 936 2,061 3,590 4,304 3,352 2,851 3,042 3,248 3,397 3,619 3,611 3,717 3,818 3,915 4,006 4,099 Accts payable 762 1,255 1,421 1,124 1,025 1,090 1,124 1,139 1,153 1,240 1,276 1,311 1,344 1,376 1,408 Accr expenses 95 158 193 173 162 162 175 177 179 190 196 201 206 211 216 936 748 561 374 187 0 8 9 10 11 12 PPE 2005 4 18,500 Cap expenditure 0 1 2 3 4 5 6 7 1,814 Accts receivable 13 1,506 3,140 5,508 6,296 6,231 7,149 7,751 7,950 8,212 8,469 8,717 8,954 9,181 9,394 Inventory 3,296 5,271 4,640 3,975 4,075 4,427 4,354 5,114 4,951 5,096 5,235 5,368 5,492 5,620 Accts payable 1,078 1,760 1,742 1,381 1,523 1,572 1,565 1,631 1,700 1,750 1,798 1,843 1,886 1,930 Accr expenses 138 212 240 203 240 244 251 259 261 268 275 282 289 296 1,506 1,205 904 603 301 0 0 1 2 3 4 5 6 7 8 9 10 11 12 4,433 6,593 7,974 10,040 10,908 11,761 11,672 12,037 12,390 12,728 13,050 13,353 PPE 2006 5 21,100 Cap expenditure 2,971 Accts receivable 2,450 Inventory 4,344 6,518 6,635 5,870 6,689 6,854 6,348 7,038 7,244 7,441 7,630 7,807 7,989 Accts payable 1,533 2,227 2,181 1,889 2,165 2,325 2,377 2,417 2,487 2,555 2,620 2,681 2,743 Accr expenses 193 272 300 291 337 343 362 370 2007 6 22,100 Cap expenditure 0 1 2 3 4 5 6 381 392 401 411 420 2,450 PPE 1,960 1,470 980 490 8 9 10 11 15,681 16,109 16,516 16,900 7 4,058 Accts receivable 3,277 4,817 6,829 9,849 12,400 13,920 14,773 15,235 Inventory 5,232 6,246 8,710 7,320 8,121 9,022 8,907 9,168 9,418 9,656 9,881 10,111 Accts payable 1,732 2,480 2,712 2,631 2,712 2,919 3,059 3,148 3,234 3,316 3,393 3,472 Accr expenses 213 293 362 381 436 435 469 482 2008 2 15,500 Cap expenditure 0 1 2 3 4 5 6 496 508 520 532 3,277 PPE 2,622 1,966 1,311 8 9 10 7 924 Accts receivable 761 1,048 1,758 2,212 2,905 3,393 3,542 3,646 3,746 3,840 3,930 1,028 1,711 2,147 1,727 1,911 2,071 2,132 2,190 2,245 2,297 2,351 Accts payable 349 547 662 586 690 711 732 752 771 789 807 Accr expenses 43 72 88 94 101 109 112 115 118 121 124 924 739 554 370 185 0 761 609 457 0 1 2 3 4 5 6 7 8 9 484 840 1,191 1,453 1,639 1,708 1,754 1,799 1,840 Inventory PPE 2009 1 14,500 Cap expenditure 394 Accts receivable 322 Inventory 488 882 881 823 958 998 1,026 1,051 1,076 1,101 Accts payable 171 261 327 281 329 343 352 361 369 378 54 55 Accr expenses 21 2010 2 14,700 Cap expenditure 34 43 44 50 53 394 PPE 315 236 158 79 0 0 1 2 3 4 6 58 258 7 752 Accts receivable 8 616 956 Accr expenses 2,363 2,985 3,360 3,494 3,582 3,666 1,787 1,913 1,745 1,964 2,043 2,094 2,143 2,193 348 Accts payable 1,787 1,013 Inventory 577 683 599 675 701 719 736 753 110 113 115 44 2 15,400 Cap expenditure 71 91 92 103 107 752 PPE 2011 5 57 322 602 451 301 150 0 0 1 2 3 4 5 616 6 7 798 Accts receivable 1,093 1,907 2,616 3,298 3,705 3,845 3,934 1,067 1,795 2,156 1,928 2,166 2,248 2,300 2,354 Accts payable 387 623 740 662 744 772 790 808 Accr expenses 47 76 99 101 114 118 121 124 798 638 479 319 160 0 0 1 2 3 4 5 6 1,745 3,052 4,180 5,258 5,896 6,107 Inventory PPE 2012 3 16,300 Cap expenditure 1,293 Accts receivable Inventory 1,708 Accr expenses 2,849 3,445 3,074 3,447 3,570 3,654 617 Accts payable 978 1,183 1,056 1,184 1,226 1,255 192 79 123 158 162 181 188 1,293 1,034 776 517 259 0 0 1 2 3 4 5 Accts receivable 1,175 2,051 2,804 3,520 3,940 Inventory 1,915 2,311 2,058 2,303 2,386 PPE 2013 2 16,000 Cap expenditure 860 Accts payable 657 794 707 791 819 Accr expenses 83 106 108 121 126 688 516 344 172 0 0 1 2 3 4 Accts receivable 1,210 2,109 2,877 3,605 Inventory 1,968 2,371 2,108 2,359 PPE 2014 860 2 Cap expenditure 16,000 876 Accts payable 676 814 724 810 Accr expenses 85 109 111 124 701 526 350 175 PPE 876 2015 2 1 2 3 Accts receivable 1,167 2,029 2,762 Inventory Cap expenditure 15,000 0 837 1,894 2,277 2,024 Accts payable 650 782 695 Accr expenses 82 104 106 670 502 335 PPE 837 Exhibit 7: Teuer Furniture Cash Flow Assets 2003 Sales - Costs - Taxes Net income - Capital expenditure + Depreciation - Increase in NWC Cash flow to assets 0 -358 0 -406 -764 2004 2,057 -2,583 0 -526 -1,175 72 -2,193 -3,823 2005 10,415 -12,003 0 -1,588 -1,814 307 -5,957 -9,052 2006 26,701 -29,394 0 -2,693 -2,971 669 -9,467 -14,461 2007 51,540 -53,144 0 -1,604 -4,058 1,264 -11,645 -16,043 2008 79,191 -77,592 0 1,600 -924 2,075 -8,498 -5,747 2009 90,680 -84,510 -543 5,626 -394 2,188 -6,281 1,139 2010 111,451 -89,222 -8,892 13,337 -752 2,032 -8,494 6,124 2011 134,093 -100,704 -13,356 20,033 -1,122 1,820 -7,192 13,539 2012 148,218 -112,132 -14,434 21,651 -2,229 1,450 -8,365 12,508 Exhibit 8: Forecasting Sales Revenue and Costs Sales growth CGS SGA Advertising Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 1 2 73.6% 72.9% 76.6% 73.8% 64.5% 60.5% 69.0% 70.4% 3 34.6% 29.2% 32.6% 28.8% 36.8% 35.6% 36.6% 4 19.5% 21.4% 22.2% 24.4% 26.1% 23.6% 5 15.1% 8.1% 7.8% 11.3% 6.3% 6 0.2% 3.6% 3.7% -1.3% 7 1.2% 3.4% -3.1% Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 8 1.0% 0.0% 9 -0.6% 70.2% 33.5% 22.9% 9.7% 1.5% 0.3% 1 71.1% 71.3% 72.9% 70.1% 72.6% 64.4% 71.0% 74.0% 70.3% 70.9% 2 61.5% 62.4% 65.6% 53.1% 67.3% 67.0% 65.9% 66.5% 3 57.8% 52.1% 57.8% 57.6% 53.9% 61.0% 53.3% 4 38.9% 43.8% 40.6% 33.3% 38.4% 40.7% 5 43.4% 39.0% 41.1% 38.9% 41.0% 6 42.0% 41.2% 40.3% 40.0% 7 38.9% 38.7% 40.2% 8 36.6% 40.2% 9 40.0% 63.7% 56.2% 39.9% Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 1 19.9% 17.6% 20.4% 20.4% 19.8% 16.4% 17.9% 20.7% 20.9% 19.3% 2 17.1% 18.0% 17.9% 19.0% 17.6% 18.4% 19.2% 19.4% 3 16.4% 17.4% 17.0% 18.0% 18.0% 16.9% 17.4% 4 13.9% 14.1% 14.5% 12.7% 13.0% 13.4% 5 12.9% 14.5% 13.2% 14.4% 13.6% 6 13.9% 13.7% 13.2% 14.2% 7 13.6% 13.9% 15.1% 8 11.7% 13.0% 9 13.1% 18.3% 17.3% 13.6% Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 1 9.9% 10.3% 10.9% 10.3% 10.2% 10.2% 11.0% 11.7% 10.7% 10.6% 2 8.9% 8.2% 8.9% 8.4% 9.0% 9.6% 9.1% 8.9% 3 7.6% 8.3% 8.0% 8.3% 8.1% 8.3% 8.6% 4 7.1% 7.4% 7.6% 6.8% 6.9% 7.3% 5 6.7% 8.0% 7.0% 6.8% 7.1% 6 7.7% 7.7% 6.7% 6.6% 7 6.9% 7.2% 7.3% 8 6.8% 7.2% 9 7.6% 8.9% 8.2% 7.2% Exhibit 9: Forecasting Investment Accounts Receivable Inventory Accounts Payable Accrued Expenses Construction costs Refresh cost Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 1 36.5% 34.4% 32.8% 32.6% 31.2% 32.9% 33.7% 31.8% 32.6% 32.4% 2 31.6% 32.5% 33.1% 31.2% 31.0% 34.0% 33.5% 33.7% 3 31.9% 33.2% 31.8% 33.8% 32.3% 30.5% 33.6% 4 32.1% 33.5% 29.7% 33.8% 31.3% 31.4% 5 32.2% 34.2% 31.3% 32.0% 32.0% 6 30.9% 34.6% 31.7% 33.8% 7 31.0% 32.5% 32.4% 8 29.7% 31.9% 9 31.5% Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 1 45.9% 43.4% 47.2% 45.6% 46.7% 50.1% 47.8% 45.5% 45.3% 47.6% 2 47.2% 48.3% 48.3% 58.0% 42.2% 49.4% 53.4% 50.7% 3 44.0% 54.5% 40.6% 48.8% 53.0% 48.6% 46.7% 4 50.4% 46.4% 46.8% 59.3% 48.1% 45.9% 5 42.4% 47.3% 43.4% 50.4% 45.5% 6 42.4% 45.6% 44.9% 49.2% 7 44.3% 48.5% 48.2% 8 50.3% 47.3% 9 42.5% Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 1 16.1% 16.1% 15.4% 16.1% 15.5% 17.0% 16.8% 15.6% 16.4% 16.3% 2 17.2% 16.9% 16.1% 19.8% 16.8% 15.8% 15.8% 16.4% 3 14.8% 18.0% 15.2% 16.0% 16.5% 15.0% 17.3% 4 16.9% 15.6% 16.2% 19.1% 17.3% 15.6% 5 14.6% 17.0% 16.2% 16.3% 15.2% 6 15.2% 16.3% 15.9% 16.7% 7 15.7% 16.8% 17.3% 8 17.2% 15.9% 9 15.3% Year 2003 2004 2005 2006 2007 2008 2009 2010 2011 Average 1 4.7% 5.1% 4.6% 4.6% 4.6% 5.1% 5.2% 4.5% 4.4% 4.8% 2 5.1% 5.1% 4.7% 4.7% 5.0% 5.0% 4.8% 4.7% 3 4.9% 4.9% 4.8% 4.8% 4.6% 4.8% 4.7% 4 4.7% 4.9% 4.4% 5.0% 4.8% 4.9% 5 4.9% 4.7% 5.2% 4.7% 4.8% 6 4.5% 4.7% 5.0% 4.7% 7 4.6% 4.8% 4.6% 8 5.2% 4.9% 9 4.7% Nominal 2003 20.8 2004 22.3 2005 24.5 2006 28.2 2007 30.6 2008 29.8 2009 27.2 2010 25.6 2011 25.9 2012 26.4 2013 26.9 2014 27.4 2015 27.9 Real 25.9 26.8 28.6 32.0 33.5 32.6 28.9 26.8 26.4 26.4 26.4 26.4 26.4 Nominal 2003 90.6% 2004 79.6% Average Real 74.0% 66.0% 70.0% Exhibit 10: Forecasting Parameters 1 Sales growth CGS (% sales) SGA (% sales, excluding dep & advertising) Advertising (% sales) Accounts Receivable (% sales) Inventory (% next year's CGS) Accounts Payable (% next year's CGS) Accrued expenses (next year's SGA adv) Depreciation (years) Refresh cost Corporate tax rate Corporate expenses Discount rate Long-term growth rate Shares outstanding (K) 70.9% 19.3% 10.6% 32.4% 47.6% 16.3% 4.8% 5 70% 40.0% 5.0% 12.1% 3.5% 9,945 2 70.2% 63.7% 18.3% 8.9% 3 33.5% 56.2% 17.3% 8.2% 4 22.9% 39.9% 13.6% 7.2% 5 9.7% 39.9% 13.6% 7.2% 6 1.5% 39.9% 13.6% 7.2% 7 0.3% 39.9% 13.6% 7.2% Teuer Furniture Pro Forma Income Statement Sales CGS SGA (exc adv & dep) Advertising Depreciation Lease (stores) Corp Expense (inc lease/adv) Taxes Net income Parameters: Corporate tax rate Corporate expenses 2012 148,218 -61,955 -21,253 -10,744 -1,450 -9,320 -7,411 -14,434 21,651 40% 5% 2013 165,024 -70,055 -23,295 -12,179 -1,084 -10,561 -8,251 -15,839 23,759 2014 181,929 -77,218 -25,689 -13,399 -1,373 -11,548 -9,096 -17,442 26,163 2015 199,600 -84,352 -28,131 -14,664 -1,959 -12,307 -9,980 -19,283 28,925 2016 217,540 -90,813 -30,415 -15,904 -2,631 -13,032 -10,877 -21,547 32,321 2017 231,499 -95,242 -32,104 -16,779 -2,559 -13,230 -11,575 -24,004 36,006 2018 242,308 -98,007 -33,266 -17,442 -2,178 -13,497 -12,115 -26,321 39,482 2019 251,122 -100,130 -34,150 -17,987 -1,828 -13,895 -12,556 -28,230 42,345 Teuer Furniture Pro Forma Balance Sheet 2012 2013 2014 2015 2016 2017 2018 48,200 33,343 81,543 0 3,920 85,463 67 53,529 35,506 89,034 17,115 5,203 111,352 64 59,012 38,862 97,874 32,851 7,155 137,880 61 64,745 41,980 106,724 51,598 9,310 167,633 60 70,564 45,299 115,863 77,811 7,440 201,114 60 75,092 46,614 121,706 110,401 5,203 237,310 59 78,598 47,624 126,221 147,316 3,641 277,178 59 11,364 1,682 13,046 0 72,417 85,463 12,315 2,861 15,176 0 96,176 111,352 12,845 2,696 15,541 0 122,339 137,880 13,784 2,586 16,369 0 151,264 167,633 15,030 2,500 17,529 0 183,584 201,114 15,404 2,316 17,719 0 219,590 237,310 15,726 2,380 18,107 0 259,072 277,178 Assets Accounts Receivable Inventory Total Current Assets Cash (bal fig) PPE Total Assets Liabilities and Equity Accounts Payable Accrued Expenses Total Current Liabilities Debt Equity Total Liabilities and Equity Balance sheet check (A=L+E) NWC Change in NWC 2011 60,132 2012 68,497 2013 73,858 2014 82,333 2015 90,355 2016 98,333 2017 103,986 2018 108,115 -8,365 -5,361 -8,475 -8,022 -7,978 -5,653 -4,129 Teuer Furniture Pro Forma Cash Flow Assets Sales - Costs - Taxes Net income - Capital expenditure + Depreciation - Increase in NWC Cash flow assets Terminal value Year NPV Firm value Asset value Share value Parameters: Asset discount rate Long-term growth rate Shareholders (#) Shares outstanding (K) 2012 148,218 -112,132 -14,434 21,651 -2,229 1,450 -8,365 12,508 12.1% 3.5% 187 9,945 2014 181,929 -138,323 -17,442 26,163 -3,325 1,373 -8,475 15,736 2015 199,600 -151,392 -19,283 28,925 -4,114 1,959 -8,022 18,748 2016 217,540 -163,672 -21,547 32,321 -761 2,631 -7,978 26,213 2017 231,499 -171,490 -24,004 36,006 -322 2,559 -5,653 32,590 1.00 15,268 318,584 318,584 32.03 2013 165,024 -125,426 -15,839 23,759 -2,367 1,084 -5,361 17,115 2.00 12,522 3.00 13,309 4.00 16,599 5 18,410 2018 242,308 -176,506 -26,321 39,482 -616 2,178 -4,129 36,914 444,261 6 242,477 Discount Rate Assuming diferent growth rates and different discount rates 32.03 3.5% 12.1% 318,584 Sensitivity Chart for Share Price Growth Rate Err:509 2.50% 3% 3.50% 11.10% 33.89 35.49 37.30 11.6% 31.81 33.20 34.77 12.10% 29.95 31.17 32.53 12.60% 28.28 29.36 30.55 13.10% 26.77 27.72 28.77 4% 39.36 36.54 34.06 31.88 29.94 4.50% 41.74 38.55 35.79 33.38 31.24 Discount Rate Share price growth rate discount rate Firm value Sensitivity Chart for Firm Value Growth Rate #REF! 2.50% 3% 3.50% 11.10% 337029 352934 370932 11.6% 316349 330193 345745 12.10% 297868 309995 323532 12.60% 281256 291942 303802 13.10% 266250 275715 286167 4% 391465 363343 338740 317042 297767 4.50% 415109 383420 355950 331915 310717