C268 WGU SPREADSHEETS QUESTIONS & ANSWERS
Use the LEFT function in cell D13 to return the text "I spent $" from the statement
template in cell D9. Refer to the location of the "$" character you calculated in cell D12
as the "num_char" argument for your function. - Answers - =LEFT(D9,D12)
Use the MID function in cell D14 to return the text "at merchant #" from the statement
template in cell D9. Refer to the location of the "$" (in cell D12)—adjusted by adding 1—
as the "start_num" argument. Use the difference between the location of the "#"
character (in cell D11) and the "$" character (in cell D12) as the "num_char" argument. -
Answers - =MID(D9,D12+1,D11-D12)
Use the RIGHT function in cell D15 to return the text "on:" from the statement template
in cell D9. Use the difference between the length of the statement template (in cell D10)
and the location of the "#" character (in cell D11) as the "num_char" argument. -
Answers - =RIGHT(D9,D10-D11)
Use the MONTH function in cell E18 to calculate the month portion of the "Time Stamp"
in cell B18. Copy and paste your function down to complete the "Month" column of the
table. - Answers - =MONTH(B18)
Use the DAY function in cell F18 to calculate the day portion of the "Time Stamp" in cell
B18. Copy and paste your function down to complete the "Day" column of the table. -
Answers - =DAY(B18)
Use the HOUR function in cell G18 to calculate the hour portion of the "Time Stamp" in
cell B18. Copy and paste your function down to complete the "Hour" column of the
table. - Answers - =HOUR(B18)
Use the MINUTE function in cell H18 to calculate the minute portion of the "Time
Stamp" in cell B18. Copy and paste your function down to complete the "Minute" column
of the table. - Answers - =MINUTE(B18)
Use the SECOND function in cell I18 to calculate the second portion of the "Time
Stamp" in cell B18. Copy and paste your function down to complete the "Second"
column of the table. - Answers - =SECOND(B18)
Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013
or earlier) in cell J18 to create the "Date" by combining the "Month" in cell E18 with the
"Day" in cell F18. "Date" should use this syntax: "Month/Day". Your function should,
therefore, also insert the "/" character between the "Month" and "Day." Copy and paste
your formula down to complete the "Date" column of the table. - Answers -
=CONCAT(E18,"/",F18)
, Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013
or earlier) to create the "Transaction Statement" in cell K18. The statement in cell K18
should read "I spent $24.06 at merchant #4931 on: 8/1". To make this statement,
combine "Statement P1" in cell D13, the "Amount" in cell D18, "Statement P2" in cell
D14, the "Merchant ID" in cell C18, "Statement P3" in cell D15, and the "Date" in cell
J18. Copy and paste your function down to complete the "Transaction Statement"
column of the table. - Answers - =CONCAT($D$13,D18,$D$14,C18,$D$15,J18)
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
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'!C4:C103)
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
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
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
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
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
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
Use the LEFT function in cell D13 to return the text "I spent $" from the statement
template in cell D9. Refer to the location of the "$" character you calculated in cell D12
as the "num_char" argument for your function. - Answers - =LEFT(D9,D12)
Use the MID function in cell D14 to return the text "at merchant #" from the statement
template in cell D9. Refer to the location of the "$" (in cell D12)—adjusted by adding 1—
as the "start_num" argument. Use the difference between the location of the "#"
character (in cell D11) and the "$" character (in cell D12) as the "num_char" argument. -
Answers - =MID(D9,D12+1,D11-D12)
Use the RIGHT function in cell D15 to return the text "on:" from the statement template
in cell D9. Use the difference between the length of the statement template (in cell D10)
and the location of the "#" character (in cell D11) as the "num_char" argument. -
Answers - =RIGHT(D9,D10-D11)
Use the MONTH function in cell E18 to calculate the month portion of the "Time Stamp"
in cell B18. Copy and paste your function down to complete the "Month" column of the
table. - Answers - =MONTH(B18)
Use the DAY function in cell F18 to calculate the day portion of the "Time Stamp" in cell
B18. Copy and paste your function down to complete the "Day" column of the table. -
Answers - =DAY(B18)
Use the HOUR function in cell G18 to calculate the hour portion of the "Time Stamp" in
cell B18. Copy and paste your function down to complete the "Hour" column of the
table. - Answers - =HOUR(B18)
Use the MINUTE function in cell H18 to calculate the minute portion of the "Time
Stamp" in cell B18. Copy and paste your function down to complete the "Minute" column
of the table. - Answers - =MINUTE(B18)
Use the SECOND function in cell I18 to calculate the second portion of the "Time
Stamp" in cell B18. Copy and paste your function down to complete the "Second"
column of the table. - Answers - =SECOND(B18)
Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013
or earlier) in cell J18 to create the "Date" by combining the "Month" in cell E18 with the
"Day" in cell F18. "Date" should use this syntax: "Month/Day". Your function should,
therefore, also insert the "/" character between the "Month" and "Day." Copy and paste
your formula down to complete the "Date" column of the table. - Answers -
=CONCAT(E18,"/",F18)
, Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013
or earlier) to create the "Transaction Statement" in cell K18. The statement in cell K18
should read "I spent $24.06 at merchant #4931 on: 8/1". To make this statement,
combine "Statement P1" in cell D13, the "Amount" in cell D18, "Statement P2" in cell
D14, the "Merchant ID" in cell C18, "Statement P3" in cell D15, and the "Date" in cell
J18. Copy and paste your function down to complete the "Transaction Statement"
column of the table. - Answers - =CONCAT($D$13,D18,$D$14,C18,$D$15,J18)
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
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'!C4:C103)
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
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
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
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
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
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
