Please calculate the Profitability Index, and the Present Value Payback. By using the 5.75% cost of money, an 8.75% cost of money and a 11.75% cost of money.

What is the profitability index at 5.75%, 8.75%, and 11.75%

What is the PV pay back at 5.75%, 8.75%, and 11.75%

Use the excel sheets below

100% $ % .0 .00 123 Defaul 10 + B A A1 fx A B C D E F G H J K 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% 3% 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 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 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 an$ 5 100% $ % .0 .00 123 Defaul 10 + B I A A1 fx A B C D E F G H K 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 12 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% 35% 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 31,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 0.000.00 120,000.00 150,000.00 170,000.00 180,000.00 0,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 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 Chead a 721 non on100% Y $ % .00 123 Defaul 10 + B ISA A1 fx A B C D E F G H K 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 5.75% 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 5.75% $10,430,340.35 84 Net Present Value at 5.75% $9,225,340.35 85 86 Net Present Value 87 The Net Present Value is calculated below with 8.75% Cost of Capital: 88 89 Year 0 1 2 3 4 5 6 7 8 90 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 91 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 Total Canh Claim 1 039 79A CA 3 ACA AA4 77 2 279 762 CA 2 247 con 10 ana onn 13 1 070 070 nc 1 non 071 21 2 037 007 of5100% * $ % .0 .00 123 Defaul 10 + B ISA H104 A B C D E F G H K 91 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 92 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 93 Present Value of Future Cash Flows at 8.75% $9,334,958.38 94 Net Present Value at 8.75% $8,129,958.38 95 96 Net Present Value 97 The Net Present Value is calculated below with 11.75% Cost of Capital: 98 99 Year 0 1 2 3 5 6 7 8 100 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 101 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 102 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 103 Present Value of Future Cash Flows at 11.75% $8,409,468.24 104 Net Present Value at 11.75% $7,204,468.24 105 106 107 108 109 110 noubank Dariod100% $ % .0 .00 123 Defaul 10 + B A fx A B C D E F G H J K Payback Period Year 0 1 2 3 4 5 6 7 8 Cash Flows -1,205,000.00 1022724.54 2451441.27 2373263.54 2217589.1 902809.42 1075570.06 1089071.31 2037997.85 Accumulated cash flow -1,205,000.00 -182,275.46 2,269, 165.81 4,642,429.35 6,860,018.45 7,762,827.87 8,838,397.93 9,927,469.24 11,965,467.09 Payback period 1.074354406 1.07 years Present Value Payback Period Year 0 1 2 3 4 5 6 7 8 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 Present Value each year -$1,205,000.00 $946,967.17 $4,371,568.79 $6, 116,130.32 $7,344,936.38 $3,604,656.23 $4,972,230.96 $5,670, 108.26 $11,711,637.81 Accumulated present value -1,205,000.00 -$258,032.83 $4, 113,535.96 $10,229,666.28 $17,574,602.66 $21, 179,258.89 $26, 151,489.85 $31,821,598.11 $43,533,235.92 Present value payback period 1.059025225 1.05 years100% $ % .0 .00 123 Defaul Y 10 + B I A A1 fx A B C D E F G H K 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 Yea 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 7AFile Edit View Insert Format Data Tools Extensions Help be = 100% $ % .0 .00 123 Defaul 10 + B I A A1 fx A B C D E F G H I J K 1 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 Inve: 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% CO 1CO 4 CO/ ACO/ ACO/ ICO/ ACO/