Answered step by step
Verified Expert Solution
Link Copied!

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Modern Advanced Accounting In Canada

Authors: Murray Hilton

6th Edition

0070001537, 978-0070001534

More Books

Students also viewed these Accounting questions