Assuming 360 days in a year Accounts Receivable = (DSO * Sales) / 360 Inventory = Cost of Sales / Inventory Turnover Ratio Accounts Payable =...
Question

# I need the following information to be fixed below on the attached

file. lines 79 and 80 are not correct, and in fact “impossible”: by definition, a PV factor cannot be higher than 1, and a present value cannot be higher than a future value. corporation is valued at the end of 2009.

1 Attachment
Assuming 360 days in a year Accounts Receivable = (DSO * Sales) / 360 Inventory = Cost of Sales / Inventory Turnover Ratio Accounts Payable = (Accounts Payable Days * Cost of Sales) / 360 Table 1 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 Accounts Receivable \$7,460,492.93 \$8,191,550.44 \$8,995,632.60 \$9,879,189.63 \$10,453,221.00 \$11,060,049.64 \$11,700,044.89 \$12,618,311.92 \$13,361,101.05 \$14,422,209.96 Inventory \$1,114,517.26 \$1,204,458.81 \$1,289,493.60 \$1,367,507.96 \$1,436,430.36 \$1,494,318.51 \$1,539,446.93 \$1,601,486.64 \$1,682,201.56 \$1,783,974.76 Accounts Payable \$1,114,517.26 \$1,204,458.81 \$1,289,493.60 \$1,367,507.96 \$1,436,430.36 \$1,494,318.51 \$1,539,446.93 \$1,601,486.64 \$1,682,201.56 \$1,783,974.76 Net Working Capital \$6,796,372.50 \$7,460,492.93 \$8,191,550.44 \$8,995,632.60 \$9,879,189.63 \$10,453,221.00 \$11,060,049.64 \$11,700,044.89 \$12,618,311.92 \$13,361,101.05 \$14,422,209.96 Increase in Net Working Capital \$664,120.43 \$731,057.51 \$804,082.16 \$883,557.03 \$574,031.37 \$606,828.64 \$639,995.25 \$918,267.03 \$742,789.13 \$1,061,108.91 Table 2 EBIT \$15,693,440.26 \$17,931,110.36 \$20,515,173.18 \$23,494,914.35 \$25,019,137.18 \$26,742,712.81 \$28,673,223.39 \$31,611,013.21 \$33,332,792.26 \$36,864,656.23 Less: Income Tax \$6,277,376.10 \$7,172,444.14 \$8,206,069.27 \$9,397,965.74 \$10,007,654.87 \$10,697,085.12 \$11,469,289.36 \$12,644,405.28 \$13,333,116.90 \$14,745,862.49 Net Income after tax \$9,416,064.15 \$10,758,666.22 \$12,309,103.91 \$14,096,948.61 \$15,011,482.31 \$16,045,627.68 \$17,203,934.03 \$18,966,607.93 \$19,999,675.36 \$22,118,793.74 Add: Depreciation \$503,172.00 \$504,143.76 \$504,629.01 \$505,115.29 \$505,600.79 \$506,086.10 \$506,571.71 \$507,057.16 \$507,543.16 \$508,029.27 Less: Increase in Net working Capital \$664,120.43 \$731,057.51 \$804,082.16 \$883,557.03 \$574,031.37 \$606,828.64 \$639,995.25 \$918,267.03 \$742,789.13 \$1,061,108.91 Less: Capital expenditure \$50,000.00 \$50,000.00 \$50,000.00 \$50,000.00 \$50,000.00 \$50,000.00 \$50,000.00 \$50,000.00 \$50,000.00 \$50,000.00 Less: Leasehold Improvement \$10,000.00 \$10,000.00 \$10,000.00 \$10,000.00 \$10,000.00 \$10,000.00 \$10,000.00 \$10,000.00 \$10,000.00 \$10,000.00 Net Cash Flow \$9,195,115.72 \$10,471,752.47 \$11,949,650.77 \$13,658,506.86 \$14,883,051.72 \$15,884,885.14 \$17,010,510.50 \$18,495,398.05 \$19,704,429.38 \$21,505,714.10 NPV Year 1 2 3 4 5 6 7 8 9 10 11 12 13 Net Cash Flows \$- \$- \$- \$9,195,115.72 \$10,471,752.47 \$11,949,650.77 \$13,658,506.86 \$14,883,051.72 \$15,884,885.14 \$17,010,510.50 \$18,495,398.05 \$19,704,429.38 \$21,505,714.10 PV Factor 0.870 0.756 0.658 0.572 0.497 0.432 0.376 0.327 0.284 0.247 Present Value \$- \$- \$- \$7,995,752.80 \$7,918,149.31 \$7,857,089.35 \$7,809,295.63 \$7,399,507.07 \$6,867,474.21 \$6,394,880.96 \$6,046,178.43 \$5,601,228.62 \$5,315,883.62 NPV \$69,205,440.01
Week 1: Income Statement 10 year projection 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 UNITS 500000 540000 577800 624024 667706 707768 743156 772883 796069 811991 836350 869804 913294 TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT TOTAL PER UNIT REVENUE PRODUCTS \$25,000,000.00 \$50.00 \$28,080,000.00 \$52.00 \$30,946,968.00 \$53.56 \$34,091,179.95 \$54.63 \$37,571,889.42 \$56.27 \$41,419,250.90 \$58.52 \$45,664,724.12 \$61.45 \$48,441,139.34 \$62.68 \$51,391,204.73 \$64.56 \$54,515,789.97 \$67.14 \$58,958,826.86 \$70.50 \$62,543,523.53 \$71.91 \$67,640,820.70 \$74.06 SERVICE \$1,500,000.00 \$3.00 \$1,593,000.00 \$2.95 \$1,675,620.00 \$2.90 \$1,719,186.12 \$2.76 \$1,747,552.69 \$2.62 \$1,759,785.56 \$2.49 \$1,755,386.10 \$2.36 \$1,734,321.46 \$2.24 \$1,697,033.55 \$2.13 \$1,644,425.51 \$2.03 \$1,609,070.36 \$1.92 \$1,589,761.52 \$1.83 \$1,585,787.11 \$1.74 TOTAL REVENUE \$26,500,000.00 \$53.00 \$29,673,000.00 \$54.95 \$32,622,588.00 \$56.46 \$35,810,366.07 \$57.39 \$39,319,442.11 \$58.89 \$43,179,036.46 \$61.01 \$47,420,110.21 \$63.81 \$50,175,460.80 \$64.92 \$53,088,238.28 \$66.69 \$56,160,215.49 \$69.16 \$60,567,897.22 \$72.42 \$64,133,285.05 \$73.73 \$69,226,607.81 \$75.80 EXPENSES COGS \$10,000,000.00 \$20.00 \$11,124,000.00 \$20.60 \$12,260,916.00 \$21.22 \$13,374,207.17 \$21.43 \$14,453,505.69 \$21.65 \$15,473,923.19 \$21.86 \$16,410,095.55 \$22.08 \$17,237,164.36 \$22.30 \$17,931,822.09 \$22.53 \$18,473,363.11 \$22.75 \$19,217,839.65 \$22.98 \$20,186,418.76 \$23.21 \$21,407,697.10 \$23.44 ADMINISTRATION \$5,000,000.00 \$10.00 \$5,151,600.00 \$9.54 \$5,304,204.00 \$9.18 \$5,410,288.08 \$8.67 \$5,572,596.72 \$8.35 \$5,795,500.59 \$8.19 \$6,085,275.62 \$8.19 \$6,450,392.16 \$8.35 \$6,901,919.61 \$8.67 \$7,454,073.18 \$9.18 \$8,124,939.76 \$9.71 \$8,937,433.74 \$10.28 \$9,205,556.75 \$10.08 UTILITIES \$200,000.00 \$0.40 \$205,200.00 \$0.38 \$213,786.00 \$0.37 \$220,199.58 \$0.36 \$229,007.56 \$0.37 \$240,457.94 \$0.38 \$254,885.42 \$0.39 \$272,727.40 \$0.40 \$294,545.59 \$0.41 \$321,054.69 \$0.42 \$353,160.16 \$0.43 \$392,007.78 \$0.44 \$439,048.71 \$0.45 RENT \$500,000.00 \$1.00 \$513,000.00 \$0.95 \$531,576.00 \$0.92 \$547,523.28 \$0.88 \$563,948.98 \$0.84 \$580,867.45 \$0.82 \$598,293.47 \$0.81 \$616,242.28 \$0.80 \$634,729.54 \$0.80 \$653,771.43 \$0.81 \$673,384.57 \$0.81 \$693,586.11 \$0.80 \$714,393.69 \$0.78 DEPRECIATION \$500,000.00 \$1.00 \$502,200.00 \$0.93 \$502,686.00 \$0.87 \$503,172.00 \$0.81 \$504,143.76 \$0.76 \$504,629.01 \$0.71 \$505,115.29 \$0.68 \$505,600.79 \$0.65 \$506,086.10 \$0.64 \$506,571.71 \$0.62 \$507,057.16 \$0.61 \$507,543.16 \$0.58 \$508,029.27 \$0.56 CONSULTANTS \$35,000.00 \$0.07 \$43,200.00 \$0.08 \$40,446.00 \$0.07 \$43,681.68 \$0.07 \$46,739.40 \$0.07 \$49,543.76 \$0.07 \$52,020.95 \$0.07 \$54,101.79 \$0.07 \$55,724.84 \$0.07 \$56,839.34 \$0.07 \$58,544.52 \$0.07 \$60,886.30 \$0.07 \$63,930.61 \$0.07 BAD DEBT (estimated at 3%) \$15,000.00 \$0.03 \$10,800.00 \$0.02 \$17,334.00 \$0.03 \$17,854.02 \$0.03 \$18,389.64 \$0.03 \$18,941.33 \$0.03 \$19,509.57 \$0.03 \$20,094.86 \$0.03 \$20,697.70 \$0.03 \$21,318.63 \$0.03 \$21,958.19 \$0.03 \$22,616.94 \$0.03 \$23,295.45 \$0.03 TOTAL EXPENSES \$16,250,000.00 \$32.50 \$17,550,000.00 \$32.50 \$18,870,948.00 \$32.66 \$20,116,925.81 \$32.25 \$21,388,331.75 \$32.06 \$22,663,863.28 \$32.07 \$23,925,195.86 \$32.24 \$25,156,323.63 \$32.60 \$26,345,525.47 \$33.14 \$27,486,992.10 \$33.88 \$28,956,884.01 \$34.63 \$30,800,492.79 \$35.40 \$32,361,951.59 \$35.41 EBIT \$10,250,000.00 \$20.50 \$12,123,000.00 \$22.45 \$13,751,640.00 \$23.80 \$15,693,440.26 \$25.14 \$17,931,110.36 \$26.83 \$20,515,173.18 \$28.94 \$23,494,914.35 \$31.56 \$25,019,137.18 \$32.32 \$26,742,712.81 \$33.55 \$28,673,223.39 \$35.28 \$31,611,013.21 \$37.78 \$33,332,792.26 \$38.33 \$36,864,656.23 \$40.39 MARGIN 38.68% 40.86% 42.15% 43.82% 45.60% 47.51% 49.55% 49.86% 50.37% 51.06% 52.19% 51.97% 53.25% EBITDA \$10,750,000.00 \$21.50 \$12,625,200.00 \$23.38 \$14,254,326.00 \$24.67 \$16,196,612.26 \$25.95 \$18,435,254.12 \$27.58 \$21,019,802.19 \$29.66 \$24,000,029.64 \$32.24 \$25,524,737.97 \$32.97 \$27,248,798.91 \$34.19 \$29,179,795.10 \$35.91 \$32,118,070.37 \$38.39 \$33,840,335.42 \$38.91 \$37,372,685.50 \$40.95 MARGIN 40.57% 42.55% 43.69% 45.23% 46.89% 48.68% 50.61% 50.87% 51.33% 51.96% 53.03% 52.77% 53.99%

End of preview

, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit

icitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar torto
1 Attachment
xlsx
The student who asked this rated it