Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Hi. I am trying to figure out the most appropriate Excel calculation for the breakeven quantity of meals in the attached excel document. I've highlighted
Hi. I am trying to figure out the most appropriate Excel calculation for the breakeven quantity of meals in the attached excel document. I've highlighted the section. Can you please assist?
Inputs and Assumptions Based on Historical Data Revenue Buffet price and associated number of customers Beverage type and price Meal Price Number of Customers (weekly) Beverage Type Beverage Price $ 12 4500 Beer $ 2.50 $ 15 3800 Coffee $ 1.50 $ 18 3300 Soda $ 1.00 Prices Use drop down to select price Single Buffet Price 12.00 4500 number of customers at this price Beer 2.50 10% of customers will order this item with meal Coffee 1.50 5% of customers will order this item with meal Soda 1.00 50% of customers will order this item with meal Expenses Variable Costs Raw Materials 45% weekly revenue Salary: Full Time 7000.00 per week Salary: Hourly 12/15 20% of total weekly revenue if meal price is $12 or $15 Salary: Hourly 18 25% of total weekly revenue if meal price is $18 Fixed Costs Operating Expenses 9000.00 per week Rent & occupancy costs 3100.00 per week 12100.00 Forecast Time Period # of weeks Annual 52 Monthly 4 Weekly 1 Budget Forecast Use drop down to select period Forecast Time Period Weekly Weeks in period 1 Revenue Computed based on forecast time period selected. Meals $54,000.00 Beer $1,125.00 Coffee $337.50 Soda $2,250.00 Total Revenue $57,712.50 Expenses Computed based on forecast time period selected. Variable Costs Raw Materials $25,970.63 Hourly Wages $11,542.50 Full Time Wages $7,000.00 Total Variable Costs $44,513.13 Fixed Costs Operating Expenses $9,000.00 Rent & Occupancy Costs $3,100.00 Total Fixed Costs $12,100.00 Total Expenses $56,613.13 Profit $1,099.38 Profit Margin 0.02 Breakeven Quantity (Meals) Fixed Costs / (Price - Variable Costs) - my results don't make sense. How many meals do I have to sell to break even at each price and each selected Forecast Time Period?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started