WGU C268 SPREADSHEETS FINAL OA
ACTUAL SCRIPT WITH 100 PERCENT
CORRECT ANSWERS
◉ 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. (Spending)
Answer: =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"
Answer: =CONCAT($D$13,D18,$D$14,C18,$D$15,J18)
◉ Change formula option type.
Answer: File- > Options- > Formulas
, Under calculation options check the box to Enable Iterative
Calculations
◉ 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. (Event Profit)
Answer: =AVERAGE('Past Events'!C4:C103)
◉ 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.) (Event Profit)
Answer: =STDEV.S('Past Events'!C4:C103)
◉ 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.) (Event Profit)
Answer: =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
ACTUAL SCRIPT WITH 100 PERCENT
CORRECT ANSWERS
◉ 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. (Spending)
Answer: =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"
Answer: =CONCAT($D$13,D18,$D$14,C18,$D$15,J18)
◉ Change formula option type.
Answer: File- > Options- > Formulas
, Under calculation options check the box to Enable Iterative
Calculations
◉ 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. (Event Profit)
Answer: =AVERAGE('Past Events'!C4:C103)
◉ 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.) (Event Profit)
Answer: =STDEV.S('Past Events'!C4:C103)
◉ 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.) (Event Profit)
Answer: =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
