answer the question in the same format as shown in the example shown in the second photo
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.Cisco Proforma Project Life 5 Units 50,000 Year 1 Price/ s 260 Revenues 12,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 -$2,800,000 52,800.000 1.500.000 40% Depreciation 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 80% -1.280,000 1,280,000 1,280,000 1.280,000 600,000 1.970,000 S 900,000) Incrementalarning 1.920,000 $ $ 1.920,000 $ 1,500,000 1.920,000 $ 1,500,000 Change NWC Add Back Depreciation 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 $ 7,500,000 $ 2.295,000 $ 3,420,000 $ 1,420,000 $ 3.420,000 $ 1.725,000 3 4 0 1125000 Discounted Cash Flow 2.726,4035 2.11.2015 $0.500,000 $2,000,00 Formula 1/50 2.173412 97801 NPV formula 52,162,082 Formula SUM2121) NPV (function 52.462,082 Forma NPV101310118 IRRunction 26. SON Formula Project Analysis NPV accept project since positive IRR accept project incerter than required return
