The company would like a break-even analysis completed. On the Break-Even Analysis worksheet, enter the correct formulas to calculate the Gross Revenue, Total Fixed Costs, Total Variable Costs, and Net Income. Place the formulas in the appropriate cells in column D.
Management thinks an average of 75 guest a day would be a realistic average. Use Goal Seek to find out what the price would need to be to come out exactly even with 75 guests per day.ue
Fill in the Expenses, Revenues, and Net Income columns (range G5:I19) using a data table. Format the range as appropriate for the data.
Copy the information in range A#:D18 from the Break-Even Analysis worksheet and paste it in range A4:D19 on the worksheet PriceandGuest. Resize the columns to fit the data. Create a data table in the range F4:N22. The price needs to start at $3 and increase to $10 in $1 increments, and the number of guest needs to start at 25 and increase in increments of 5.
Once the data table has been created, format the data in the data table so any net profit under -$5000 uas a light red background and dark red text, data that is above or equal to -$5000 will have a light yellow background and dark yellow text, and anything $5000 or greater will have a light green background and dark green text. Format cell F5 to hide the result of the formula in the cell.
Copy the information in range A3:D18 from the sheet Break – Even Analysis to range A3:D18 on the sheet DaysAndPric. Now add two scroll bars to the sheet to the right of the inserted cells. One scroll bar will change the days of operation per year between the values of 200 and 250 one day at a time. THe other scroll bar will change the price between the values of $3 and $10 by $1 increments. Be sure to label the scroll bars as to what the control changes.
The Excel file is uploaded.