Model Inputs

Input Analysis

You are the owner of a small business that manages the program, food, and merchandise sales at the Excel Center, where the local professional basketball team plays. In addition to basketball games, the Excel Center hosts dozens of events a year. Your company handles all sales made inside the arena. There are three major products you sell: programs, food, and merchandise. You have decided to build a spreadsheet model to help you analyze and project the profitability of future events. The Event Profit worksheet contains this model. You have randomly selected sales and attendance data from a number of past events to help build your model. This data can be found on the Past Events worksheet. You will analyze this data in order to obtain appropriate values to develop the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than hard-coding values in the cells of your worksheet.

Product Type

Programs

Food

Merchandise

# of Events

100

Cost of Goods Sold (COGS)

40%

60%

70%

Commission

20%

10%

5%

Statistic

Attendance

Programs

Food

Merchandise

Fixed Costs

$5,000.00

$10,000.00

$20,000.00

Average

Employees (% of Expected Profit)

0.05%

0.20%

0.03%

Standard Deviation

Salary (per employee)

$50.00

95% Confidence Interval

Arena Fee

$100,000.00

Product Type

Programs

Food

Merchandise

Event Profitability Analysis

Correlation with Attendance

Gross Profit

Attendance

18000

Product Type

Programs

Food

Merchandise

Sales

Product Type

Programs

Food

Merchandise

Cost of Goods Sold (COGS)

Sales Forecast

Gross Profit

Upper Limit for Sales

Lower Limit for Sales

Operating Expenses

Product Type

Programs

Food

Merchandise

What-if Analysis

Salary Expenses

Attendance Forecast

Commissions

15000

18000

21000

Fixed Costs

Arena Fee

$50,000.00

Total Operating Expenses

$75,000.00

$100,000.00

Profit before Arena Fee

$125,000.00

$150,000.00

Number of Employees

$175,000.00

$200,000.00

Profit Summary

Product Type

Total

Questions

Sales

Question 1: The sales for which product type are most highly correlated with attendance?

Gross Profit

Total Operating Expenses

Profit before Arena Fee

Question 2: How likely is it that food sales would exceed $220,000 if attendance is 18000?

Arena Fee

Net Profit

Question 3: Which product generates the largest gross profit?

Question 4: Which product will require the fewest employees?

Question 5: Would the event be profitable with a 150,000 arena fee if 15000 people were in attendance?