Please Label and calculate the internal rate of return (just how it's listed on the excel sheet)
Please explain what do the decision model indicates to the capital budgeting committee,
A1 fx A B C D E F G H I J K 69 Disposition Stage Cash Flows 70 Year 0 1 2 3 4 5 6 7 8 71 Continuing Value 10,189,989.27 72 73 Total Cash Flows -1,205,000.00 1,022,724.54 2,451,441.28 2,373,263.54 2,217,589.10 902,809.42 1,075,570.06 1,089,071.31 2,037,997.85 74 75 76 Net Present Value 77 The Net Present Value is calculated below with 8% Cost of Capital: 78 79 Year 0 1 2 3 4 5 6 7 8 80 Total Acquisition Stage Cash Flow -1,205,000.00 -81,035.46 -20,772.51 -6, 192.04 87,829.10 -3,927.94 -28,096.99 -64,325.40 0.00 81 Total Operating Stage Cash Flow 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 82 Total Cash Flows -1,205,000.00 1,022,724.54 2,451,441.27 2,373,263.54 2,217,589.10 902,809.42 1,075,570.06 1,089,071.31 2,037,997.85 83 Present Value of Future Cash Flows at 8% $9,591,408.22 84 Net Present Value at 8% $8,386,408.22 85 86 87@ 5 100% - $ % .0 .00 123 Defaul 10 B I A 41 fx Acquisition Stage and Working Capital Cash Flows A B C D E F G H I J K Acquisition Stage and Working Capital dash Flows Assumptions or Cost Relationships YEAR 0 1 2 3 4 5 6 7 8 Building Cost 350,000.00 Construction Cost 330,000.00 Equipment Cost 300.000.00 Delivery & Testing 20,000.00 POS and Music System 40,000.00 Research & Development Costs 50,000.00 Initial Promotion 60,000.00 12 Employee Training 20,000.00 3 Additional Investment in Working Capital as 7% 7% 7% 7% 7% 7% 7% 7% 7% 4 Working Capital Balance 40,000.00 5 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 9 Cash balance is 9% of revenue 9% 9% 9% 9% 9% 9% 9% 9% 9% ACO/ ACO/ CO 4CO/ 4CO/ ACO/ ACO/ 4 CO/100% $ % .0 .00 123 Defaul 10 + B I A1 fx A B C D E F G H J K 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% 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% 3% 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.40A B C D E F G H J K 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 0 36 37 Total Acquisition Stage Cash Flow -1,205,000.00 -81,035.46 -20,772.51 -6, 192.04 87,829.10 3,927.94 -28,096.99 -64,325.40 0.00 38 39 40 Operating Stage Cash Flow 41 Assumptions of Cost Relationships 42 YEAR 0 1 2 3 4 5 6 7 8 43 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 44 Variable cog sold 30% 20% 25% 30% 5% 30% 35% 30% 45 Fixed COG 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 46 Depreciation expense on building and equipment 81,250.00 81,250.00 100,000.00 110,000.00 120,000.00 130,000.00 140,000.00 150,000.00 47 Selling cost 200,000.00 250,000.00 280,000.00 300,000.00 320,000.00 350,000.00 380,000.00 400,000.00 48 General 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 49 Income taxes and a percentage 28% 28% 28% 28% 28% 28% 28% 28% 50 51 52 Operating Stage Cash Flows: 53 Year 0 1 2 3 4 5 6 7 8 DAUARU a con non an 2 Ann 209 01 2 624 100 61 2 70n non no 2 794 491 14 2 767 764 03 2 070 0co 70 2 70A COn A154 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 72 72 Total Cash Clown 1 023 754 CA 2 272 262 CA ona onn 19 4 07c c70 nc 1 0on 074 21 3 097 007 or
