C268 Newest updated Exam with verified detailed answers || || || || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the average attendance
|| || || || || || || || || || || || ||
and sales for each type of product from the past events listed in the "Past Events" worksheet.
|| || || || || || || || || || || || || || || ||
- ✔✔=AVERAGE('Past Events'!C4:C103) for each column
|| || || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the sample standard
|| || || || || || || || || || || || ||
deviation for attendance and sales for each type of product from the past events listed on
|| || || || || || || || || || || || || || || ||
the "Past Events" worksheet. (Note for Excel 2007 users: Excel 2007 does not support a
|| || || || || || || || || || || || || || ||
specific function to calculate sample standard deviations. Use the STDEV function instead.)
|| || || || || || || || || || || ||
- ✔✔=STDEV.S('Past Events'!C4:C103) for each column
|| || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the 95% confidence
|| || || || || || || || || || || || ||
interval for the sales for each type of product. (You will not calculate a confidence interval
|| || || || || || || || || || || || || || || ||
for attendance.) Use the number of events (calculated in cell I3) as part of your calculations.
|| || || || || || || || || || || || || || ||
(Note to Excel 2007 users: the CONFIDENCE.NORM function is not supported in Excel
|| || || || || || || || || || || || || ||
2007. Use the CONFIDENCE function instead.) - ✔✔=CONFIDENCE.NORM(0.05,J7,$I$3)
|| || || || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the correlations between
|| || || || || || || || || || || || ||
the sales of each type of product and event attendance. Use appropriate ranges from the
|| || || || || || || || || || || || || || ||
"Past Event" worksheet for your calculations. - ✔✔=CORREL('Past Events'!
|| || || || || || || ||
$C$4:$C$103,'Past Events'!D4:D103) ||
The sales for which product type are most highly correlated with attendance? Select the
|| || || || || || || || || || || || || ||
correct answer from the drop-down list in cell L32. - ✔✔Food (highest correlation number)
|| || || || || || || || || || || || ||
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 I13
|| || || || || || || || || || || || || || ||
(the attendance forecast) for your calculations. - ✔✔=FORECAST($I$13,'Past Events'!
|| || || || || || || || ||
D4:D103,'Past Events'!$C$4:$C$103) ||
, 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. - ✔✔=I16+J8
|| || || || || || || || || || ||
(sales forecast + 95% confidence interval)
|| || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the lower 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. - ✔✔=I16-J8
|| || || || || || || || || || ||
(sales forecast - 95% confidence interval)
|| || || || ||
How likely is it that food sales would exceed $220,000 if attendance is 18,000 people? Select
|| || || || || || || || || || || || || || || ||
the correct answer from the drop-down list in cell L35. - ✔✔<5% chance
|| || || || || || || || || || || ||
In the "Event Profitability Analysis" section of the model, enter the projected sales for each
|| || || || || || || || || || || || || || ||
product type for this event by referencing your sales forecast from the "Input Analysis"
|| || || || || || || || || || || || || ||
section of the model. - ✔✔=I16 (calculated previously)
|| || || || || || ||
Calculate the cost of goods sold (COGS) for each product. The COGS is calculated as the
|| || || || || || || || || || || || || || || ||
sales for each product type times the COGS percentage from the "Model Inputs" section of
|| || || || || || || || || || || || || || ||
the model for that product type. - ✔✔=C15*C4 (gross profit * COGS %)
|| || || || || || || || || || || ||
Calculate the gross profit for each product. The gross profit is calculated as the sales minus
|| || || || || || || || || || || || || || || ||
the COGS for each product. - ✔✔=C15-C16
|| || || || || ||
Which product generates the largest gross profit? Select the correct answer from the drop-
|| || || || || || || || || || || || ||
down list in cell L38. - ✔✔Merchandise (line 17)
|| || || || || || || ||
Calculate the commissions paid for each product type. The commissions paid are calculated
|| || || || || || || || || || || ||
as the expected sales for each product type times the commission percentage from the
|| || || || || || || || || || || || || || ||
"Model Inputs" section of the model. - ✔✔=C15*C5 (sales & commission %)
|| || || || || || || || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the average attendance
|| || || || || || || || || || || || ||
and sales for each type of product from the past events listed in the "Past Events" worksheet.
|| || || || || || || || || || || || || || || ||
- ✔✔=AVERAGE('Past Events'!C4:C103) for each column
|| || || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the sample standard
|| || || || || || || || || || || || ||
deviation for attendance and sales for each type of product from the past events listed on
|| || || || || || || || || || || || || || || ||
the "Past Events" worksheet. (Note for Excel 2007 users: Excel 2007 does not support a
|| || || || || || || || || || || || || || ||
specific function to calculate sample standard deviations. Use the STDEV function instead.)
|| || || || || || || || || || || ||
- ✔✔=STDEV.S('Past Events'!C4:C103) for each column
|| || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the 95% confidence
|| || || || || || || || || || || || ||
interval for the sales for each type of product. (You will not calculate a confidence interval
|| || || || || || || || || || || || || || || ||
for attendance.) Use the number of events (calculated in cell I3) as part of your calculations.
|| || || || || || || || || || || || || || ||
(Note to Excel 2007 users: the CONFIDENCE.NORM function is not supported in Excel
|| || || || || || || || || || || || || ||
2007. Use the CONFIDENCE function instead.) - ✔✔=CONFIDENCE.NORM(0.05,J7,$I$3)
|| || || || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the correlations between
|| || || || || || || || || || || || ||
the sales of each type of product and event attendance. Use appropriate ranges from the
|| || || || || || || || || || || || || || ||
"Past Event" worksheet for your calculations. - ✔✔=CORREL('Past Events'!
|| || || || || || || ||
$C$4:$C$103,'Past Events'!D4:D103) ||
The sales for which product type are most highly correlated with attendance? Select the
|| || || || || || || || || || || || || ||
correct answer from the drop-down list in cell L32. - ✔✔Food (highest correlation number)
|| || || || || || || || || || || || ||
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 I13
|| || || || || || || || || || || || || || ||
(the attendance forecast) for your calculations. - ✔✔=FORECAST($I$13,'Past Events'!
|| || || || || || || || ||
D4:D103,'Past Events'!$C$4:$C$103) ||
, 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. - ✔✔=I16+J8
|| || || || || || || || || || ||
(sales forecast + 95% confidence interval)
|| || || || ||
In the "Input Analysis" section of the spreadsheet model, calculate the lower 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. - ✔✔=I16-J8
|| || || || || || || || || || ||
(sales forecast - 95% confidence interval)
|| || || || ||
How likely is it that food sales would exceed $220,000 if attendance is 18,000 people? Select
|| || || || || || || || || || || || || || || ||
the correct answer from the drop-down list in cell L35. - ✔✔<5% chance
|| || || || || || || || || || || ||
In the "Event Profitability Analysis" section of the model, enter the projected sales for each
|| || || || || || || || || || || || || || ||
product type for this event by referencing your sales forecast from the "Input Analysis"
|| || || || || || || || || || || || || ||
section of the model. - ✔✔=I16 (calculated previously)
|| || || || || || ||
Calculate the cost of goods sold (COGS) for each product. The COGS is calculated as the
|| || || || || || || || || || || || || || || ||
sales for each product type times the COGS percentage from the "Model Inputs" section of
|| || || || || || || || || || || || || || ||
the model for that product type. - ✔✔=C15*C4 (gross profit * COGS %)
|| || || || || || || || || || || ||
Calculate the gross profit for each product. The gross profit is calculated as the sales minus
|| || || || || || || || || || || || || || || ||
the COGS for each product. - ✔✔=C15-C16
|| || || || || ||
Which product generates the largest gross profit? Select the correct answer from the drop-
|| || || || || || || || || || || || ||
down list in cell L38. - ✔✔Merchandise (line 17)
|| || || || || || || ||
Calculate the commissions paid for each product type. The commissions paid are calculated
|| || || || || || || || || || || ||
as the expected sales for each product type times the commission percentage from the
|| || || || || || || || || || || || || || ||
"Model Inputs" section of the model. - ✔✔=C15*C5 (sales & commission %)
|| || || || || || || || || || ||
