View the step-by-step solution to:

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

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.
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
Background image of page 1
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%
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

I have tried to solve by making... View the full answer

fixed sheet.xlsx

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 Accounts...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online