the following problem is to be put into a excel format like the one in the "capital budgetint project example "photo

thank you

-

Scenario Cupcake Bakery You estimate that your bakery will have a project life of 3 years and that the required return is 12%. Your marketing partner completes an analysis and estimates annual sales of 8,000 cupcakes at a price per cupcake of $2.50. Costs per cupcake are estimated at $1.00. You will have to rent space for $1,000 per month or $12,000 per year and purchase equipment (one- time purchase) of $1,200. Your equipment will be depreciated by the straight-line method for 3 years (S400 per year) You purchased a business guide on how to prepare cupcakes a few years back (2018) for a one-time cost of $155.00 Your tax rate is 25% Your Change in Net Working Capital Year 0 Year 1 (5750) SO SO $750 Year 2 Year 3 Use Excel to 1. Prepare pro forma income statement for three years 2. Prepare pro forma incremental earnings and cash flows from assets for the three years 3. Calculate the project's NPV - Using an Excel Formula and Function 4. Calculate the projects IRR - Using an Excel Function 5. Insert a text box in your spreadsheet, state whether you accept or reject this project, and briefly explain your rationale, 6. After conducting the initial analysis above your marketing partner performs a sensitivity analysis and determines that, you would sell 7,900 cupcakes per year if you increased the price per cupcake to $2.70 each. How does this impact your project (NPV, IRR), will it change your decision to accept or reject it? (Type response into Excel Spreadsheet) - Show work 7. Based upon your analysis which price maximizes your NPV? Chapter 9 Capital Budgeting Project Example Inputs HomeNet - Cica Proforma Project Life 5 Units 50,000 Year 1 Price/ S 260 11,000,000 13,000,000 13.000.000 13,000,000 Cost/ $ 110 COGS -5,500,000 -5,500,000 -5,500,000 -5,500,000 SGA Expenses $ 2.800,000 Gross Profit 7.500.000 7.500,000 7.500.000 7.500.000 Depreciation $ 1.500,000 SGA Expenses -52,800.000 $2,800,000 52,800,000 52.800,000 40 Depreciation 1.500,000 1,500,000 1.500.000 -1.500,000 -1,500,000 Equipment $ 7,500,000 EBIT 3.200,000 3,200,000 3,200.000 3,200,000 1.500.000 Required return 12 Income Tax -20% -1,280,000 1,280.000 1.280.000 1.280,000 600,000 Incremental Caming $ 1.920,000 $ 1.920,000 $ 1.920,000 $ 1.970,000 1900,000) Change NWC Add Back Depreciation 1,500,000 1,500,000 1.500.000 1.500,000 1.500.000 0 0 Equipment Purchase -7.500,000 1 1125000 Change in WC 1,125,000 0 0 0 1.125.000 2 0 Incremental Free Cash Flow $ 6.500,000 $ 2.295,000 $ 3,420,000 $ 3,420,000 $ 1.420,000 $ 1.725,000 3 4 0 1125000 Discounted Cash Flow $ 0.500,000 $2,049,90 Formula 1/50516 2.726,4035 , 2.17MAN 981 NPV formula 52,462.012 Formula SUM2121) NPV (function 52.462,082 Form NPV10111101010 IRR Wunction 26. SON Formula Project Analysis NPV accept project sincer positive IRR accept project since greater than required return