Please review the excel sheets and recalculate Present Value Payback using 5.75%, then using 8.75% cost of money, and then using 11.75% cost of money.
Please look at excel sheet to see how it show be formatted. Thank you in advance!
100% * $ % .0 .00 123 Defaul 10 + B I A A1 fx A B C D E F G H I J K 2 3 Assumptions or Cost Relationships 4 YEAR 0 1 2 3 4 5 6 7 8 5 Building Cost 350,000.00 6 Construction Cost 330,000.00 7 Equipment Cost 300,000.00 8 Delivery & Testing 20,000.00 9 POS and Music System 40,000.00 10 Research & Development Costs 50,000.00 11 Initial Promotion 60,000.00 12 Employee Training 20,000.00 13 Additional Investment in Working Capital as Inves 7% 7% 7% 7% 7% 7% 7% 7% 7% 14 Working Capital Balance 40,000.00 15 16 Sales 2,500,000.00 3,400,393.97 3,631, 199.61 3,700,000.00 2,724, 121.11 2,767,764.93 3,079,953.75 3,794,680.41 17 COGS 2,075,000.00 2,822,327.00 3,013,895.68 3,071,000.00 2,261,020.52 2,297,244.89 2,556,361.61 3,149,584.74 18 19 Cash balance is 9% of revenue 9% 9% 9% 9% 9% 9% 9% 9% 9% Imuanton bolanos in 150/ of rong ICO/ 4CO/ ICO 1CO ACO/ ACO/ 4 CO/ ACO/ ACO/100% $ % .0 .00 123 Defaul 10 B A A1 fx A B C D E F G H I J K 18 19 Cash balance is 9% of revenue 9% 9% 9% 9% 9% 9% 9% 9% 9% 20 Inventory balance is 15% of COGS 15% 15% 15% 15% 15% 15% 15% 15% 15% 21 Accounts payable are 8% of COGS 8% 8% 8% 8% 8% 8% 8% 8% 8% 22 Accrued expenses are 7% of revenues 7% 7% 7% 7% 7% 7% 7% 7% 7% 23 24 Acquisition Stage Cash Flows 25 YEAR 0 1 2 3 4 5 6 7 8 26 Cost of Building -350,000.00 27 Cost of Construction -330,000.00 28 Cost of Equipment -300,000.00 29 Working Capital: 30 Cash Balance 225,000.00 306,035.46 326,807.96 333,000.00 245, 170.90 249,098.84 277,195.84 341,521.24 31 Inventory Balance 311,250.00 423,349.05 452,084.35 460,650.00 339,153.08 344,586.73 383,454.24 472,437.71 32 Accounts Payable Balance 166,000.00 225,786.16 241, 111.65 245,680.00 180,881.64 183,779.59 204,508.93 251,966.78 33 Accrued Expenses Balance 145,250.00 197,562.89 210,972.70 214,970.00 158.271.44 160.807.14 178,945.31 220,470.93 34 Total Net Working Capital Balance 225,000.00 306,035.46 326,807.96 333,000.00 245, 170.90 249,098.84 277,195.84 341,521.24 35 Incremental Investment in Working Capital -225,000.00 -81,035.46 -20,772.51 -6, 192.04 87,829.10 -3,927.94 -28,096.99 -64,325.40 36100% $ % .0 .00 123 Defaul 10 + B I A A1 fx A B C D E F G H J K 53 Year 0 1 2 3 4 5 6 7 8 54 Revenues 2,500,000.00 3,400,393.91 3,631, 199.61 3,700,000.00 2,724, 121.11 2,767,764.93 3,079,953.75 3,794,680.41 55 Variable cost of goods sold -750000 -680078.782 -907799.9025 -1110000 -953442.3885 -830329.479 -1077983.813 -1138404.123 56 Fixed cost of goods sold -721,000.00 -721,000.00 -721,000.00 -721,000.00 -721,000.00 -721,000.00 -721,000.00 -721,000.00 57 Depreciation expense -81,250.00 -81,250.00 -100,000.00 -110,000.00 -120,000.00 -130,000.00 -140,000.00 -150,000.00 58 Selling Cost -81,250.00 -81,250.00 -100,000.00 -110,000.00 -120,000.00 -130,000.00 -140,000.00 -150,000.00 59 General and administrative -100,000.00 -120,000.00 -150,000.00 -170,000.00 -180,000.00 -190,000.00 -200,000.00 -220,000.00 60 Pretax Operating income 766,500.00 1,716,815.13 1,652,399.71 1,479,000.00 629,678.72 766,435.45 800,969.94 1,415,276.29 61 Income taxes expense -214620 480708.2358 -462671.9181 -414120 -176310.042 -214601.9263 -224271.5825 -396277.3604 62 Net operating income 551,880.00 1,236, 106.89 1, 189,727.79 1,064,880.00 453,368.68 551,833.52 576,698.36 1,018,998.93 63 Add back depriciation 551,880.00 1,236, 106.89 1, 189,727.79 1,064,880.00 453,368.68 551,833.52 576,698.36 1,018,998.93 64 Total operating stage cash flows 1, 103,760.00 2,472,213.78 2,379,455.58 2, 129,760.00 906,737.36 1, 103,667.05 1, 153,396.71 2,037,997.85 65 66 67 68 69 Disposition Stage Cash Flows 70 Year 0 1 2 3 4 5 6 7 8 71 Continuing Value 10,189,989.27 77 + Week 7 +
