C268 WGU EXAM QUESTIONS & 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. - Answers - =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.) - Answers - =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.) - Answers -
=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. - Answers -
=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. - Answers - 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. - Answers -
=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. -
Answers - =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. -
Answers - =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. - Answers - <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. - Answers - =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. - Answers - =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. - Answers - =C15-C16
Which product generates the largest gross profit? Select the correct answer from the
drop-down list in cell L38. - Answers - 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. - Answers - =C15*C5 (sales
& commission %)
Enter the fixed costs in the model. Reference the appropriate amount for each product
type in the "Model Inputs" section. - Answers - =C6 (already referenced)
Calculate the total operating expenses for each product type. This is the sum of salary
expenses (yet to be calculated), commissions, and the fixed costs for each product
type. - Answers - =SUM(C21:C23)
Calculate the profit before the arena fee for each product type. This is the gross profit
minus the total operating expenses for each type of product, including the salary
expenses (yet to be calculated). - Answers - =C17-C24
Calculate the number of employees that are needed to work the event for each type of
product. This is calculated as the profit before the arena fee for each type of product,
times the employees (% of expected profit) for each product type from the "Model
Inputs" section of the worksheet. Because you cannot hire a fraction of an employee,
round your calculations up to the next whole number. - Answers -
=ROUNDUP(C26*C7,0)
(profit before arena * employees, 0 <- no decimals)
Which product will require the fewest employees? Select the correct answer from the
drop-down list in cell L40. - Answers - row 28 (lowest amount)
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. - Answers - =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.) - Answers - =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.) - Answers -
=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. - Answers -
=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. - Answers - 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. - Answers -
=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. -
Answers - =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. -
Answers - =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. - Answers - <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. - Answers - =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. - Answers - =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. - Answers - =C15-C16
Which product generates the largest gross profit? Select the correct answer from the
drop-down list in cell L38. - Answers - 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. - Answers - =C15*C5 (sales
& commission %)
Enter the fixed costs in the model. Reference the appropriate amount for each product
type in the "Model Inputs" section. - Answers - =C6 (already referenced)
Calculate the total operating expenses for each product type. This is the sum of salary
expenses (yet to be calculated), commissions, and the fixed costs for each product
type. - Answers - =SUM(C21:C23)
Calculate the profit before the arena fee for each product type. This is the gross profit
minus the total operating expenses for each type of product, including the salary
expenses (yet to be calculated). - Answers - =C17-C24
Calculate the number of employees that are needed to work the event for each type of
product. This is calculated as the profit before the arena fee for each type of product,
times the employees (% of expected profit) for each product type from the "Model
Inputs" section of the worksheet. Because you cannot hire a fraction of an employee,
round your calculations up to the next whole number. - Answers -
=ROUNDUP(C26*C7,0)
(profit before arena * employees, 0 <- no decimals)
Which product will require the fewest employees? Select the correct answer from the
drop-down list in cell L40. - Answers - row 28 (lowest amount)
