Question 6 In the "Input Analysis" section of the spreadsheet model, calculate a sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference cell 113 (the attendance forecast) for your calculations. Question 7: In the "Input Analysis" section of the spreadsheet model, calculate the upper limit for your sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference the confidence interval you created for your calculation. R S T U V W 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 event a year. Your company handles all sales made inside the arena. There are three major products that 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 on the 'Past Events' worksheet. You will complete statistics on this model to obtain appropriate values for the inputs for the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than 'hard-coding' values in the cells of your spreadsheet. A B C D E F G H K L M О P 1 2 Model Inputs 3 Product Type Programs Food 4 Cost of Goods Sold (COGS) 40% 60% 5 Commission 20% 10% 6 Fixed Costs $5,000.00 7 Employees (% of Expected Profit) 0.05% Merchandise 70% 5% $10,000.00 $20,000.00 0.20% 0.03% # of Events Input Analysis 100 Statistic Average Standard Deviation Attendance Programs Food Merchandise 14,966 $72,471.76 $154,242.26 $ 393,360.47 3,994.23 $42,687.13 $ 66,229.45 $ 227,919.37 8 Salary (per employee) $50.00 95% Confidence Interval 9 Arena Fee $100,000.00 10 Product Type 11 Event Profitability Analysis Correlation with Attendance Programs 0.55 0.3 м Food 0.62 0.3 м Merchandise 0.54 0.3 м 12 13 14 Product Type Gross Profit Programs Food Merchandise Attendance 18000 15 Sales Product Type Programs Food Merchandise 16 Cost of Goods Sold (COGS) Sales Forecast 17 Gross Profit 18 Upper Limit for Sales Lower Limit for Sales
Question 6 In the "Input Analysis" section of the spreadsheet model, calculate a sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference cell 113 (the attendance forecast) for your calculations. Question 7: In the "Input Analysis" section of the spreadsheet model, calculate the upper limit for your sales forecast for each type of product if expected attendance at the future event is 18000 people. Reference the confidence interval you created for your calculation. R S T U V W 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 event a year. Your company handles all sales made inside the arena. There are three major products that 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 on the 'Past Events' worksheet. You will complete statistics on this model to obtain appropriate values for the inputs for the model. Complete the tasks to finish the model. Whenever possible, reference cells rather than 'hard-coding' values in the cells of your spreadsheet. A B C D E F G H K L M О P 1 2 Model Inputs 3 Product Type Programs Food 4 Cost of Goods Sold (COGS) 40% 60% 5 Commission 20% 10% 6 Fixed Costs $5,000.00 7 Employees (% of Expected Profit) 0.05% Merchandise 70% 5% $10,000.00 $20,000.00 0.20% 0.03% # of Events Input Analysis 100 Statistic Average Standard Deviation Attendance Programs Food Merchandise 14,966 $72,471.76 $154,242.26 $ 393,360.47 3,994.23 $42,687.13 $ 66,229.45 $ 227,919.37 8 Salary (per employee) $50.00 95% Confidence Interval 9 Arena Fee $100,000.00 10 Product Type 11 Event Profitability Analysis Correlation with Attendance Programs 0.55 0.3 м Food 0.62 0.3 м Merchandise 0.54 0.3 м 12 13 14 Product Type Gross Profit Programs Food Merchandise Attendance 18000 15 Sales Product Type Programs Food Merchandise 16 Cost of Goods Sold (COGS) Sales Forecast 17 Gross Profit 18 Upper Limit for Sales Lower Limit for Sales
Essentials of Business Analytics (MindTap Course List)
2nd Edition
ISBN:9781305627734
Author:Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. Anderson
Publisher:Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. Anderson
Chapter8: Time Series Analysis And_forecasting
Section: Chapter Questions
Problem 3P: Problems 1 and 2 used different forecasting methods. Which method appears to provide the more...
Related questions
Question
Transcribed Image Text:Question 6
In the "Input Analysis" section of the spreadsheet model, calculate a sales forecast for each type of
product if expected attendance at the future event is 18000 people. Reference cell 113 (the attendance
forecast) for your calculations.
Question 7:
In the "Input Analysis" section of the spreadsheet model, calculate the upper limit for your sales forecast
for each type of product if expected attendance at the future event is 18000 people. Reference the
confidence interval you created for your calculation.
Transcribed Image Text:R
S
T
U
V
W
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 event a year. Your company handles all sales made
inside the arena. There are three major products that 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 on
the 'Past Events' worksheet. You will complete statistics on this model
to obtain appropriate values for the inputs for the model. Complete the
tasks to finish the model. Whenever possible, reference cells rather
than 'hard-coding' values in the cells of your spreadsheet.
A
B
C
D
E
F G
H
K
L
M
О
P
1
2
Model Inputs
3
Product Type
Programs
Food
4
Cost of Goods Sold (COGS)
40%
60%
5
Commission
20%
10%
6
Fixed Costs
$5,000.00
7
Employees (% of Expected Profit)
0.05%
Merchandise
70%
5%
$10,000.00 $20,000.00
0.20%
0.03%
# of Events
Input Analysis
100
Statistic
Average
Standard Deviation
Attendance Programs Food
Merchandise
14,966 $72,471.76 $154,242.26 $ 393,360.47
3,994.23 $42,687.13 $ 66,229.45 $ 227,919.37
8
Salary (per employee)
$50.00
95% Confidence Interval
9
Arena Fee
$100,000.00
10
Product Type
11
Event Profitability Analysis
Correlation with Attendance
Programs
0.55
0.3 м
Food
0.62
0.3 м
Merchandise
0.54
0.3 м
12
13
14
Product Type
Gross Profit
Programs
Food
Merchandise
Attendance
18000
15
Sales
Product Type
Programs
Food
Merchandise
16
Cost of Goods Sold (COGS)
Sales Forecast
17
Gross Profit
18
Upper Limit for Sales
Lower Limit for Sales
<Q.6
<Q.7
<Q.8
19
Operating Expenses
20
Product Type
Programs
Food Merchandise
What-if Analysis
21
Salary Expenses
Attendance Forecast
22
Commissions
15000
18000
21000
23
Fixed Costs
24
Total Operating Expenses
$50,000.00
$75,000.00
25
26
Profit before Arena Fee
27
Arena Fee
28
Number of Employees
29
$100,000.00
$125,000.00
$150,000.00
$175,000.00
$200,000.00
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Recommended textbooks for you
Essentials of Business Analytics (MindTap Course …
Statistics
ISBN:
9781305627734
Author:
Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. Anderson
Publisher:
Cengage Learning
Cornerstones of Cost Management (Cornerstones Ser…
Accounting
ISBN:
9781305970663
Author:
Don R. Hansen, Maryanne M. Mowen
Publisher:
Cengage Learning
Essentials Of Business Analytics
Statistics
ISBN:
9781285187273
Author:
Camm, Jeff.
Publisher:
Cengage Learning,
Essentials of Business Analytics (MindTap Course …
Statistics
ISBN:
9781305627734
Author:
Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. Anderson
Publisher:
Cengage Learning
Cornerstones of Cost Management (Cornerstones Ser…
Accounting
ISBN:
9781305970663
Author:
Don R. Hansen, Maryanne M. Mowen
Publisher:
Cengage Learning
Essentials Of Business Analytics
Statistics
ISBN:
9781285187273
Author:
Camm, Jeff.
Publisher:
Cengage Learning,
Managerial Accounting: The Cornerstone of Busines…
Accounting
ISBN:
9781337115773
Author:
Maryanne M. Mowen, Don R. Hansen, Dan L. Heitger
Publisher:
Cengage Learning
Financial Reporting, Financial Statement Analysis…
Finance
ISBN:
9781285190907
Author:
James M. Wahlen, Stephen P. Baginski, Mark Bradshaw
Publisher:
Cengage Learning
Excel Applications for Accounting Principles
Accounting
ISBN:
9781111581565
Author:
Gaylord N. Smith
Publisher:
Cengage Learning