Breakeven Analysis

Please follow the Inputs/output/calculation model:

This is a sample following the model; It's not the correct answer for this question:

Please do it in Excel and show the details!! Thank you!!!

Three entrepreneur graduates were planning to open a coffee shop. They estimate the average customer will be willing to pay $4.00 per cup. Its cost to them will be $.50 wholesale. They also estimate the fixed cost of the shop will be $400,000 per year. Finally, they estimate the shop will sell 100,000 cups the first year. Use this information to answer the questions below. Follow the Inputs/Outputs/Calculations structure. A What would be their expected profit or loss the first year? Answer B How many cups would they have to sell to breakeven in Year 1 Answer C If the shop is open 12 hours per day, 6 days per week, how many cups per hour do they need to sell on average to breakeven? Answer D Keep the estimated quantity at its original level, 100,000 and all other variables at their original values also. What price would they have to charge to breakeven? Answer E On a separate tab, keep the estimated quantity at its original level, 100,000 and all other variables at their original values also. How fast would the quantity sold have to grow to earn $50,000 in Year 3? Answer: (Show Inputs/Output/Calculations below.) Good spreadsheet engineering uses the following general framework: Parameters Optional lists author, date of analysis, currency used, etc. Inputs Values that are used to calculate the outputs Outputs Results of the calculations Calculations Where all the work is done to convert input values into output values. 10 11 INPUTS: Revenues: Quantity Sold per day Price per unit Costs: Variable Cost per unit Fixed Costs per year $20.00 $5.00 $60,000 OUTPUTS: Quantity sold per year: Profit 2,920 ($16,200) 26 23 Calculations: Revenues: 25 Quantity Sold per day Quantity Sold per Year Price per unit Total Revenues per year 29 Costs: Quantity Sold per Year Variable Cost per unit Total Variable Cost per year 8 2,920 $20.00 $58,400 2,920 $5.00 $14,600 Fixed Costs per year Total Cost $60,000 $74,600 Profit: ($16,200)