Westend Catering takes orders for meals using multiple sales people. The owner is concerned about bookings for the next two months. She anticipates a loss if bookings on some days are not increased and if sales people continue to overbook because it increases the cost of staff for the events.

The staff members and additional staff if needed will work a full day if they are scheduled.

The owner has asked you to look at the bookings for the next two months and give her a summary of sales and wages if no additional events are booked.

2.1 Calculate the remaining meals available based on the normal capacity.

• Reference the normal capacity from the model input section of the worksheet in your calculation.
• Copy and paste down to complete the column.

2.2 Use a VLOOKUP function to display the status of each booking based on the total number of meals sold.

• Use the data in the Booking Analysis table in your calculation.
• Copy and paste down to complete the column.

2.3 Calculate the total number of staff members needed for each booking.

• Use the meals per staff member on the Model Inputs section in your calculation.
• Use the ROUNDUP function to return a whole number. Note: the number of digits should be zero.
• Copy and paste down to complete the column.

2.4 Calculate the number of additional staff needed for any bookings that are overbooked.

• Use an IF statement that checks to see if the status is "Overbooked".
• If the condition is true use the ABS (to make it a positive number) and ROUNDUP functions to return the number of additional staff members needed.
• Reference the the meals available and meals per staff member from the Model Inputs table to find the number of additional staff needed.
• If the condition is false display a zero.
• Copy and paste down to complete the column.

2.5 Calculate the count of bookings for the Excess Capacity status.

• Use a COUNTIF function.
• Copy and paste down to complete the column.

2.6 Calculate the total number of meals booked.

2.7 Calculate the total number of staff days.

• This should be the total number of regular staff needed for the bookings.

2.8 Calculate the total number of additional staff days.

• This should be the total number of additional staff needed for the bookings.

2.9 Calculate the sales revenue for the bookings.

• Use the number of meals booked and the price per meal in the Model Inputs.

2.10 Calculate the cost of meals for all the bookings.

• Use the number of meals sold and the cost per meal in the Model Inputs.

2.11 Calculate the total revenue for all the bookings.

2.12 Calculate the total wages for the company's regular staff.

• Use the summary calculation you did for total staff days and the staff cost per day in the Model Inputs table.

2.13 Calculate the total wages for additional staff.

• Use the summary calculation you did for additional staff days and the additional staff cost per day in the model inputs table.

2.14 Calculate the total wages for all the staff.

2.15 Calculate the profit expected if bookings do not change.

• Remember the booking data is for two months.