Question
Billy's Gut Busting Buffet - Billy is opening a new buffet restaurant and wants to use Excel to build a weekly budget forecast and estimate
Billy's Gut Busting Buffet - Billy is opening a new buffet restaurant and wants to use Excel to build a weekly budget forecast and estimate his profit. He has inputs and assumptions for revenue and expenses in the spreadsheet. Now he needs to set up his weekly budget forecast to show the profit he can expect based on the input he has provided. Research conducted has indicated that the number of customers Billy can expect in a week is directly tied to the price of an individual buffet meal (see "Buffet Price and associated number of customers" data in Inputs and Assumptions section of start file spreadsheet). All customers will be charged the same meal price, Billy must determine what price to charge for a meal ($12, $15, $18). **Do not use the cell naming feature in Excel to name any cells in this assignment. 1. Use an Excel feature in cell B13 to allow a user of this model to click on cell B13 and select (or set) one of the three buffet meal price options. 2. In the Weekly Budget Forecast section of the spreadsheet calculate each revenue item and each expense item for the week as well as the Total Revenue, Total Expenses, Profit, Profit Margin, and Breakeven Quantity. All of these must be calculated and must contain cell references to the inputs and assumptions so that if input values change, the calculated weekly budget values will change as well. Round all values to the nearest dollar. Notes Weekly Budget Forecast Calculations Buffet Revenue - Create a formula that calculates the Buffet Revenue based on the buffet price set in cell B13. When that value (buffet price) is changed by the user, the prices associated number of customers will be used in the calculation of the Buffet Revenue. Use the VLOOKUP function. This version of the homework will not have an amount in the input value for Additional Revenue, however your formula for Total Revenue and Breakeven Quantity must consider that the user may enter an input value for Additional Revenue in the future, which would need to be reflected in the Total Revenue amount. Salary Expense - Create a formula to calculate the Salary Expense. This expense will depend on the buffet price set in cell B13. If the buffet price is set to be the highest of the three prices, the lower salary rate will be used, along with the weekly total revenue, to calculate the weekly salary expense. If the buffet price is set to the lowest or the middle price option the higher salary rate will be used. When the buffet price is changed, the salary expense should change accordingly. Use the IF function. USE CELL REFERENCES! Profit Margin If you do not know what this is look it up and create a formula for it. Breakeven Quantity Calculate how many meals Billy will have to sell to breakeven. Use a formula for this calculation. You were given the formula to calculate breakeven quantity in class; identify the inputs that contribute to the formula. To check that your formula is correct, you can use goal seek. AGAIN...USE CELL REFERENCES!
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