How do I calculate NPV, IRR and Payout Period for the following tables?
Excel File Edit View Insert Format Tools Data Window Help AT 52% [ Thu 13:14 AutoSave O OFF ~ H E 6 0 = New%20Heritage%20Doll%20Company%2%20Spreadsheet%20Supplement Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) V 11 AA E =1 ap Wrap Text General Ex AP- O. Paste BIUV DVAv Merge & Center v v % 9 00 -20 Conditional Format Cell Insert Delete Format Sort & Find & Ideas Formatting as Table Styles Filter Select M32 B E G H K L M N 0 P Q R S T U V W X Z IDWN New Heritage Doll Company: Capital Budgeting Exhibit 1 Selected Operating Projections for Match My Doll Clothing Line Expansion 2010 2011 2012 201: 2014 2015 2016 2017 2018 2019 2020 5 Revenue 4,500 6,860 8,409 9,082 9,808 10,593 11,440 12,355 13,344 14,411 6 Revenue Growth 52.4% 22.6% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 7 Production Costs 8 Fixed Production Expense (excl depreciation) 57: 575 58 86S 610 62 635 648 660 674 Variable Production Costs 2,035 3,404 4.291 4,669 5,078 5,521 ,000 6,519 7,079 7,685 10 Depreciation 152 192 20 242 11 Total Production Costs :,762 1,131 5,029 5,419 5,853 ,321 5,827 1,373 1,963 8,600 12 Selling, General & Administrative 1,250 1,155 1,735 2,102 2,270 2,452 ,648 2,860 3,089 3,336 3,603 13 Total Operating Expenses 1,250 3,917 5,866 7,132 7.690 8.305 8,969 .687 10,462 1,299 12,203 14 15 Operating Profit (1,250 583 994 1,277 1,392 1,503 1,623 1,753 1,893 2,045 2,209 16 17 Working Capital Assumptions: 18 Minimum Cash Balance as % of Sales 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 19 Days Sales Outstanding 59.2x 9.2x 59.2x 59.2x 59.2x 59.2x 9.2x 59.2x 59.2x 59.2x 20 Inventory Turnover (prod. cost/ending inv.) 7.7x 8.3x 12.7x 12.7x 12.7x 12.7x 12.7x 12.7x 12.7x 12.7x 21 Days Payable Outstanding (based on tot. op. exp.) 30.8x 30.9x 31.0x 31.0x 31.0x 31.0x 31.0x 31.0x 31.0x 31.0x 22 23 Capital Expenditures 1,470 952 152 152 334 361 389 421 454 491 530 24 25 26 27 28 NPV CapE 3520 29 IRR 30 Payout Period 31 32 33 34 35 36 37 38 39 Exhibit 1 MMDC Exhibit 2 DYOD + Ready - + 100% ... 1 W FExcel File Edit View Insert Format Tools Data Window Help A 53% [ Thu 13:14 AutoSave O OFF ~ H 6 0 - New%20Heritage%20Doll%20Company%2C%20Spreadsheet%20Supplement Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Times New Roman 9 AA E ap Wrap Text Number LIX Ex AY- O. Paste BIUVE MVAv LEE Merge & Center v 00 -20 Conditional Format Cell Insert Delete Format Sort & Ideas Formatting as Table Styles Filter Find & Select E23 4 X fx | 4610 B E G H J K L M N O P Q R S T U V W X Z IDWN New Heritage Doll Company: Capital Budgeting Exhibit 2 Selected Operating Projections for Design Your Own Doll 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 5 Revenue 6,000 14,360 20,222 21,435 22,721 24,084 25,529 27,061 28,685 6 Revenue Growth 139.3% 40.8% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 7 Production Costs 8 Fixed Production Expense (excl depreciation) 1,650 1,68 1,717 1,751 1,786 1,822 1,85 1,895 1,933 Variable Production Costs 2.250 7,651 11,427 12,182 12,983 3,833 14.736 15,694 16,712 10 Depreciation 310 436 462 490 52 584 11 Total Production Costs 1,210 ,644 3,454 14,369 5,231 16,145 7,113 8,140 9,229 12 Selling, General & Administrative 1,201 1,240 2,922 4,044 4,28 4,544 4,817 5,106 5,412 5,737 13 Total Operating Expenses 1,201 ,450 12,566 17,498 18,656 19,775 20,962 2,219 23,553 24,966 14 15 Operating Profit (1,201) 0 550 1,794 2,724 2,779 2,946 3,123 3,310 3,509 3,719 16 17 Working Capital Assumptions: 18 Minimum Cash Balance as % of Sales 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 3.0% 19 Days Sales Outstanding 9.2x 59.2x 59.2x 59.2x 59.2x 9.2x 59.2x 59.2X 59.2x 20 Inventory Turnover (prod. cost/ending inv. 12.2x 12.3x 12.6x 12.7x 12.7x 12.7x 12.7x 12.7x 12.7x 21 Days Payable Outstanding (based on tot. op. exp.) 33.7x 33.8x 33.9x 33.9x 33.9x 33.9x 33.9x 33.9x 33.9x 22 23 Capital Expenditures 4,610 0 310 310 2,192 826 875 928 983 1,043 1,105 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 Exhibit 1 MMDC Exhibit 2 DYOD Ready - ... 1 W XI SY
