2. Calculate the NPV and IRR for Lock In. Show the calculations in the Homework data...

 

Transcribed Image Text:

2. Calculate the NPV and IRR for Lock In. Show the calculations in the Homework data spreadsheet. The initial investment is $2,500 and the discount rate is 13.00 percent. Lock In Statement of Cash Flow Business Plan Most Likely Week 1 2 3 4 5 6 7 8 9 10 Total Starting Cash Balance 0.00 1,832.65 1,931.17 1,999.17 2,019.43 2,196.92 2,240.00 2,358.83 2,505.14 2,597.94 Cash Inflows Cash Sales 242.28 215.36 Credit Collections 363.42 323.04 Line of Credit 2,500.00 Total Cash Inflows 3,105.70 0.00 538.40 269.20 403.80 0.00 673.00 349.96 524.94 0.00 874.90 323.04 484.56 242.28 363.42 242.28 363.42 269.20 403.80 349.96 188.44 2,692.00 524.94 282.66 4,038.00 0.00 807.60 0.00 605.70 0.00 605.70 0.00 673.00 0.00 874.90 0.00 2,500.00 471.10 9.230.00 Cash Outflows Inventory 927.00 0.00 0.00 618.00 0.00 0.00 0.00 0.00 0.00 0.00 1,545.00 Sales tax (10.25%) 62.08 55.19 68.98 89.68 82.78 62.08 62.08 68.98 89.68 48.29 689.83 Marketing 200.00 60.00 45.00 30.00 40.00 70.00 45.00 70.00 80.00 60.00 700.00 Merchant fee (4.75%) 17.26 15.34 19.18 24.93 23.02 17.26 17.26 19.18 24.93 13.43 191.81 SGA (10%) 60.57 53.84 67.30 87.49 80.76 60.57 60.57 67.30 87.49 47.11 673.00 Interest (13.00%) 6.13 5.52 4.54 4.54 3.56 2.70 1.96 1.23 0.00 0.00 30.16 Loan Payment 0.00 250.00 Total Cash Outflows 1,273.05 439.89 400.00 605.00 0.00 400.00 350.00 300.00 300.00 500.00 0.00 2,500.00 854.64 630.11 562.61 486.88 526.69 782.10 168.82 6,329.79 Change in Cash 1,832.65 98.51 68.00 20.26 177.49 43.09 118.82 146.31 92.80 302.28 Ending Cash Balance 1,832.65 1,931.17 1,999.17 2,019.43 2.196.92 2,240.00 2,358.83 2,505.14 2,597.94 2,900.21 Sales tax 10.25% Merchant fee 4.75% Interest expense 13.00% 4 Credit Sales 27 24 30 39 36 27 27 30 39 21 Cash Sales 18 16 20 26 24 18 18 20 26 14 TOTAL UNITS (per wk) 45 40 50 65 60 45 45 50 65 35 500.00 C2 fx A B C D E F G H I J K L M N 1 Finance 330 spring 2024 Name 2 3 4 Discount rate 5 6 7 8 Buckets of Love CFs 0 1 2 3 4 5 6 7 8 9 10 9 10 NPV IRR 11 12 Lock In CFs 13 14 15 45 NPV IRR 16 Memora-Ball CFs 17 18 NPV IRR 2. Calculate the NPV and IRR for Lock In. Show the calculations in the Homework data spreadsheet. The initial investment is $2,500 and the discount rate is 13.00 percent. Lock In Statement of Cash Flow Business Plan Most Likely Week 1 2 3 4 5 6 7 8 9 10 Total Starting Cash Balance 0.00 1,832.65 1,931.17 1,999.17 2,019.43 2,196.92 2,240.00 2,358.83 2,505.14 2,597.94 Cash Inflows Cash Sales 242.28 215.36 Credit Collections 363.42 323.04 Line of Credit 2,500.00 Total Cash Inflows 3,105.70 0.00 538.40 269.20 403.80 0.00 673.00 349.96 524.94 0.00 874.90 323.04 484.56 242.28 363.42 242.28 363.42 269.20 403.80 349.96 188.44 2,692.00 524.94 282.66 4,038.00 0.00 807.60 0.00 605.70 0.00 605.70 0.00 673.00 0.00 874.90 0.00 2,500.00 471.10 9.230.00 Cash Outflows Inventory 927.00 0.00 0.00 618.00 0.00 0.00 0.00 0.00 0.00 0.00 1,545.00 Sales tax (10.25%) 62.08 55.19 68.98 89.68 82.78 62.08 62.08 68.98 89.68 48.29 689.83 Marketing 200.00 60.00 45.00 30.00 40.00 70.00 45.00 70.00 80.00 60.00 700.00 Merchant fee (4.75%) 17.26 15.34 19.18 24.93 23.02 17.26 17.26 19.18 24.93 13.43 191.81 SGA (10%) 60.57 53.84 67.30 87.49 80.76 60.57 60.57 67.30 87.49 47.11 673.00 Interest (13.00%) 6.13 5.52 4.54 4.54 3.56 2.70 1.96 1.23 0.00 0.00 30.16 Loan Payment 0.00 250.00 Total Cash Outflows 1,273.05 439.89 400.00 605.00 0.00 400.00 350.00 300.00 300.00 500.00 0.00 2,500.00 854.64 630.11 562.61 486.88 526.69 782.10 168.82 6,329.79 Change in Cash 1,832.65 98.51 68.00 20.26 177.49 43.09 118.82 146.31 92.80 302.28 Ending Cash Balance 1,832.65 1,931.17 1,999.17 2,019.43 2.196.92 2,240.00 2,358.83 2,505.14 2,597.94 2,900.21 Sales tax 10.25% Merchant fee 4.75% Interest expense 13.00% 4 Credit Sales 27 24 30 39 36 27 27 30 39 21 Cash Sales 18 16 20 26 24 18 18 20 26 14 TOTAL UNITS (per wk) 45 40 50 65 60 45 45 50 65 35 500.00 C2 fx A B C D E F G H I J K L M N 1 Finance 330 spring 2024 Name 2 3 4 Discount rate 5 6 7 8 Buckets of Love CFs 0 1 2 3 4 5 6 7 8 9 10 9 10 NPV IRR 11 12 Lock In CFs 13 14 15 45 NPV IRR 16 Memora-Ball CFs 17 18 NPV IRR