Download WGU - C268 Spreadsheets - Useful formula guide and more Exams Computer Science in PDF only on Docsity!
1 /
WGU - C268 Spreadsheets - Useful formula guide
1. 1. Calculate the payment amount for the loan in cell C15. Reference the cells
containing the appropriate loan information as the arguments for the function you use. Cells C20-C67 in the "Payment" column are populated with the payment amount from cell C15.: PMT=(c13/12,c12,c11) Payment
2. Calculate, in cell D20, the interest amount for period 1 by multiplying the balance in
period 0 (cell F19) by the loan interest rate (cell C13) divided by
- Dividing the interest rate by 12 results in the monthly interest rate. This formula is reusable. The interest for a given period is always the monthly interest rate times the balance from the previous period.: =F19*$C$13/ F19 time absolute values C13 divided by 12.
3. Copy the interest amount calculation down to complete the "Interest" col-umn of the
amortization table. [2 points]: Drag D19 down to D67 to complete column
4. Calculate, in cell E20, the principal amount for period 1. The principal amount is the
difference between the payment amount (cell C20) and the interest amount (cell D20) for period 1. Construct your formula in such a way that it can be reused to complete the "principal" column of the amortization table.: =C20-D
5. Copy the principal amount calculation down to complete the "principal" column of
the amortization table.: Drag E20 down to E67 to complete column
6. Calculate, in cell F20, the balance for period 1. The balance is the difference between
the balance for period 0 (cell F19) and the principal amount for period 1 (cell E20). This formula is reusable. The balance is always calculated as the difference between the balance from the previous period and the principal amount for the current period.: =C20-D
7. Copy the balance amount calculation down to complete the "Balance" col-umn of the
amortization table.: drag F20 down to F67 to complete column
8. Calculate, in cell G12, the total amount paid by multiplying the payment amount
(cell C15) by the term of the loan (cell C12).: =C15*C
9. Calculate the total interest paid in cell G13. The total interest paid is the sum of
all interest paid in the "Interest" column of the amortization table.- : =SUM(D20:D67)
2 / Add D20 range through D
10. To verify that the total interest calculation from the amortization table is
correct, calculate the total interest paid in cell G14. This is the difference between the Total Amount Paid over the course of the loan and the original Loan Amount. Notice the negative sign associated with the original Loan Amount.: =G12--C two minus cancel out the minus from C
11. Assume you have made the first 36 payments on your loan. You want to trade
the car in for a new car. You believe that you can sell your car for $4000. Will this cover the balance remaining on the car in period 36? Answer either "Yes" or "No" in cell G from the drop-down menu.: No G36 = 5,193.87 $4,000 is not enough
12. Use the HLOOKUP function to complete the "Hourly Wage" column of table
- Use the "Employee" from table 1 as the lookup_value and the "Employee Wage Information" above table 1 as your reference table.: =hlookup(D16, $E$11:$H$12, 2, 0) horizontal lookup (Jim, absolute value E and 11 through absolute value H and 12, 2nd column, Exact)
13. Use the AND function to complete the "Time Bonus?" column of table 1. An
employee earns a time bonus if the project's "Hours Worked" are fewer than the "Estimated Hours" and if the work "Quality" is greater than 1.: - =AND(E161) And used when all logical functions are true. (E16 greater than C16, AND H16 greater than 1)
14. Use the OR function to complete the "Outcome Bonus?" column of table 1. An
employee earns an outcome bonus if the difficulty of a job is greater than 3 or if the quality of their work is equal to 3.: =OR(G16>3,H16=3) when either logical functions need to be true. (G16 greater than 3, OR H16=
4 /
20. Use the COUNTIF function to complete the "# of Jobs" column in table 3.
Reference the appropriate field in table 1 as your range and the "Employee" names in table 3 as your criteria.: =COUNTIF($D$16:$D$35,G39) Counts the occurrences in range D16-D35, looks for the value in G39.
21. Use the SUMIF function to complete the "Total Hours" column in table 3.
Reference the appropriate field in table 1 as your range and the "Employee" names in table 3 as your criteria.: =SUMIF($D$16:$D$35,G39,$E$16:$E$35) Adds the absolute range of D16-D35, looks for the value in G39, and adds the hours of absolute range of E16-E
22. Use the SUMIF function to complete the "Total Pay" column in table 3.
Reference the "Employee" field in table 1 as your range, the "Employee" names in table 3 as your criteria, and the "Total Pay" field in table 1 as your sum_range.: =SUMIF($D$16:$D$35,G39,N16:N35) looks in table range D16-D35, looks for value from G39, Adds the number from table range N16-N
23. Use the COUNTIF function to complete the "# of Touch-ups" column in table
- Reference the appropriate field in table 2 as your range and the "Difficulty" rating in table 4 as your criteria.: =COUNTIF($D$40:$D$46,G46) Formula looks at absolute rang of D40-D46, for value in G
24. Use the SUMIF function to complete the "Cost Touch-ups" column in table
- Reference the appropriate field in table 2 as your range and the "Difficulty" rating in table 4 as your criteria.: =(SUMIF($D$40:$D$46,G46,$E$40:$E$46)) Formula looks at table range D40-D46, looks for value in G46, and then looks at E40-E
25. Use the AVERAGEIF function to complete the "Average Hours/Job" column in table
- Reference the appropriate field in table 1 as your range and the "Difficulty" rating in table 4 as your criteria.: =AVERAGEIF($G$16:$G$35,G46,$E$16:$E$35) Gets the average of values in G16-G35, for value in G46, averages values in E16-E
5 /
26. Construct a column chart to examine the total annual revenue for Google, Inc.
from 2003 to 2010, using the data in table 1 (range C12:J12). Format the
7 /
Searches for the $ sign in cell D9 and displays position of character
31. 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.: =LEFT(D9,9) I spent $ Looks at D9 starting from the LEFT to display the first 9 characters
32. 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)—ad-justed 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.: =MID(D9,10,14) at merchant # Searches the middle of D9, displays the characters in at position 10 and number of characters you want to display
33. Use the RIGHT function in cell D15 to return the text "on:" from the state-ment
template in cell D9. Use the difference between the length of the state-ment template (in cell D10) and the location of the "#" character (in cell D11) as the "num_char" argument.: =Right(D9,5) on: Examines D9 from the right, to display up to 4 characters
34. 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.: =month(B18) Excel apparently knows what to do with the data in B
8 / Drag down to the bottom of table to reveal all "8s" (for August).
35. 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.: =day(B18) Excel apparently knows what to do with the data in B18. Drag the formula down to the bottom of the column DAY.
36. 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.: =Hour(b18) Excel apparently knows what to do with the data in B18. Drag the formula down to the bottom of the column HOUR.
37. 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.: =minute(b18) Excel apparently knows what to do with the data in B18. Drag the formula down to the bottom of the column MINUTE.
38. 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.: =SECOND(B18) Excel apparently knows what to do with the data in B18. Drag the formula down to the bottom of the column SECOND.
39. Use the CONCAT function (or the CONCATENATE function if you are using Ex-cel
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.: =concatenate(E18,"/",F18)
10 / correlations between the sales of each type of product and event attendance. Use appropriate ranges from the "Past Event" worksheet for your calcula-tions.: =CORREL('Past Events'!$C$4:$C$103,'Past Events'!D4:D103) =CORREL('Past Events'!$C$4:$C$103,'Past Events'!E4:E103) =CORREL('Past Events'!$C$4:$C$103,'Past Events'!F4:F103)
45. The sales for which product type are most highly correlated with atten-
dance? Select the correct answer from the drop-down list in cell L32.: Food Close to 1.
46. 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 calcula-tions.: =FORECAST($I$13,'Past Events'!D4:D103,'Past Events'!C4:C103) =FORECAST($I$13,'Past Events'!E4:E103,'Past Events'!C4:C103) =FORECAST($I$13,'Past Events'!F4:F103,'Past Events'!C4:C103) Remember your X value for the forecast formula should match the X data type, example Attendance and past Attendance numbers. =forcase(X,knownY, knownX)
