Steps to Step 1 2 5 6 7 8 9 m: Instructions Start Excel. Open exploring ecap_grader_c2_Transactions.xlsx and save the workbook as exploring_ecap_grader_c2_Transactions_LastFirst. On the June Totals worksheet, sort the data in the range A3:E16 in ascending order by Category. At each change in Category, use the Sum function to add subtotals to the data in the Amount column. Accept all other defaults. Collapse the outline to show the grand total and Category subtotals only. Create a Pivot Table in cell F1 on the AnnualExp worksheet using the data in the range A1:D17. Add the Expense field to the PivotTable as a row label; add the Amount field as the value; add the Year field as the column label. Change the format of the values in the Pivot Table to accounting with no decimal places and set columns E1 to AutoFit Column width. Add the Category field to the Report Filter area of the PivotTable. Filter the data so that only expenses in the Variable category are displayed. Display the values as percentages of the grand total. Insert a Year slicer in the worksheet and use the slicer to filter the data so that only data from 2015 is displayed. Change the height of the slicer to 2" and then reposition it so that the top left corner aligns with the top left corner of cell 12. Create a PivotChart based on the data in the PivotTable using the default Pie chart type. Change the chart title text to Variable Expenses and remove the legend. Add data labels to the outside end position displaying only the category names and leader lines. Reposition the chart so that the top left corner aligns with the top left corner of cell F13. Note, Mac users, select the range FS:G7, and on the Insert tab, click Ple, and then click Pie. Follow the remaining instructions. On the HomeLoan worksheet, in cell A10, enter a reference to the monthly payment from column B. Create a one-variable data table for the range A9:H10 using the interest rate from column B as the Row input cell. On the HomeLoan worksheet, in cell A12, enter a reference to the monthly payment from column B. Create a two-variable data table in the range A12:H16, using the interest rate from column B as the Row input cell and the term in months from column B as the Column input cell. On the HomeLoan worksheet, perform a goal seek analysis to determine what the down payment in column B needs to be if you want the monthly payment in column B to be 2000. Accept the solution. Points Possibl 0 10 11 12 13 14 15 16 17 18 19 20 On the HomeLoan worksheet, create a scenario named Maximum using cells B2, B3, B5, and B6 as the changing cells. Enter these values for the scenario: 280000, 24000, .075, and 360, respectively. Show the results and dose the Scenario Manager. Undo the last change. On the June2017 worksheet, in cell 17, sum the values in E7:E24 if the purchase in column C is groceries; in cell 18, average the values in E7:E24 if the purchase in column C is groceries; in cell 19, calculate the number of times groceries were purchased during the month. On the June2017 worksheet, in cell 111, calculate the total amount spent on groceries using a credit card; in cell 112, calculate the average spent on groceries using a credit card; in cell 113, calculate the number of times groceries were purchased using a credit card during the month. On the June2017 worksheet, in cell F7, nest an AND function within an IF function to determine if the transaction was paid using a credit card and the amount of the transaction is less than -100. If both conditions are met in the AND function, the function should return the text Flag. For all others, the function should return the text OK. Copy the function down through cell F24. On the June2017 worksheet, in cell E4, use the INDEX function to identify the transaction amount that aligns with the position in cell C4. Type 5 for the columo.num, and use the range A7:E24 as the array argument. On the June2017 worksheet, filter the data in the range A6:E24 using the criteria in the range A27:E28. Set the filter to copy the data to the range A31:E31. In cell 117, use the DAVERAGE function to determine the average amount spent for transactions meeting the criteria in the range A27:E28. Group the June2017 and JuneTotals worksheets together. Fill the contents and formatting from cell A1 on the JuneTotals, worksheet across the grouped worksheets. Ungroup the worksheets. In cell 119 on the June2017 worksheet, insert a reference to cell E26 on the June Totals, worksheet. On the June2017 worksheet, create a validation rule for the range D7:D24 to only allow values in the list from the range 121:124. Create an error alert for the rule that will display after invalid data is entered. Using the Stop style, enter Invalid Entry as the title and Please select a valid method. (including the period) as the error message. On the Carloan worksheet, in cell G10, use the CUMPRINC function to calculate the cumulative principle paid on the car loan. Use the data in the range E4:E6 when entering the first three arguments in the function. Reference cell A10 as the start and end period arguments. Enter 0 as the type argument. Modify the function to convert the results to a positive value. Make the row references absolute in all arguments except for the end period. Copy the formula down through cell G19. [SKIP - No Point] From Excel, open the downloaded tab-delimited text file eV2_stocks.bxt using a data format of general. You do not need to create a connection to the original data file. Copy the range A1:F5 and then close the text file. Paste the copied range onto the Stocks worksheet in cell A3. [SKIP-No Point] On the Stocks worksheet, separate the text in the first column into two columns using the asterisk (*) as the delimiter. Insert a function in cell A3 that will display the text in cell A9 with initial capitalization only. 9 9 3 5 6 0 10 Step 21 22 23 24 Instructions [SKIP-No Point] On the Stocks worksheet, import the downloaded XML file eV2_highclose.xm/into cell A11. Open the XML file in Notepad, and then find and replace all instances of the text General Electric with General Electric Co (no period). Save the XML document and close Notepad. Refresh the XML data on the Stocks worksheet. Note, Mac users, import the comma-delimited eV2_highclose.csv file into cell A11. Open the CSV file in Excel, and then find and replace all instances of the text General Electric with General Electric Co (no period). Save and close the CSV document. Refresh the data on the Stocks worksheet. [SKIP-No Point] Apply the Retrospect theme to the workbook. Apply the cell style Accent1 to cells A3 and D3 on the Carloan worksheet. [SKIP-No Point] Set the Author property of the workbook to Exploring Excel Student and then set the Title property to Personal Finances. Save the workbook. Ensure that the following worksheets are present (in this order): June Totals, June2017, AnnualExp, HomeLoan, Carloan, Stocks. Close the workbook, and then submit the file as directed. Total Points Points Possible 0 0 O 84 A Date Payee 6/5/17 South & Main 6/4/17 Visa 6/10/17 SEC 6/3/17 Sam's Stop 6/9/17 Gas-N-Wash 6/13/17 Gas-N-Wash 6/2/17 George's SuperMart 6/8/17 George's SuperMart 6/12/17 George's SuperMart 6/1/17 Zachary Kaye 6/6/17 Income 6/11/17 Springfield Savings & Loans 6/7/17 (Savings Account] June Transactions Category ATM Withdrawal ATM Withdrawal Total Credit Card Payment Credit Card Payment Total Electric Bill Electric Bill Total Gas Gas Gas Gas Total Groceries Groceries Groceries Groceries Total Rent Rent Total Salary Salary Total Student Loan Method of Payment Debit Card Student Loan Total Transfer to Savings Transfer to Savings Total Grand Total Check Check Debit Card Debit Card Credit Card Credit Card. Credit Card Debit Card Check Check Check Transfer $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ssssssss $ $ $ $ Amount (202.00) (202.00) (300.00) (300.00) (217.61) (217.61) (61.13) (57.49) (65.33) (183.95) (114.62) (136.52) (173.06) (424.20) (800.00) (800.00) 2,034.78 2,034.78 (175.00) (175.00) (300.00) (300.00) (567.98) 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 ***2=== 24 25 26 27 28 29 30 31 32 Date Payee 6/1/17 Zachary Kaye 6/4/17 Va 6/8/17 South & Main 6/8/17 Income 6/8/17 (Savings Account Date Date 6/15/17 SEC 6/15/17 Springfield Savings & Loans 6/18/17 Gas-N-Wash 6/18/17 SGW 6/22/17 South & Main 6/22/17 Income 6/22/17 (Savings Account 6/23/17 George's SuperMart 6/25/17 CN Solutions 6/25/17 Springfield Savings & Loans 6/28/17 AMP Insurance 6/29/17 Sam's Stop 6/30/17 George's SuperMart Payee Position: Payee June Totals Search Results 8 Category Rent Credit Card Payment ATM Withdrawal Salary Transfer to Savings Electric Bill Student Loan GM Water Bill ATM Withdrawal Salary Transfer to Savings Groceries Cable/Internet Car Loan Car Insurance GM Groceries Criteria June2017 Extract Category Category Method of Payment Check Check Debit Card Check Transfer Check Check Credit Card Check Debit Card Check Transfer Credit Card Check Check Check Debit Card Debit Card Amount: Method of Payment Credit Card Method of Payment AnnualExp Amount -800 -300 -202 2034.78 -300 -217.61 -175 -65.33 -43.59 -262 2034.78 -300 -109.64 -102.83 HomeLoan -233 -96.47 -60.08 -149.68 Amount Amount CarLoan Total Cost: Average Cost Occurrences Groceries Credit Card Total Cost Average Cost Occurrences: Groceries Average Credit Card Purchase: Total Remaining Payment Options: Stocks Check Credit Card Debit Card Transfer + 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 2222 23 24 25 A Year B Category 2015 Variable 2015 Fixed 2015 Fixed 2015 Fixed 2015 Variable 2015 Variable 2016 Variable 2016 Fixed 2016 Fixed 2016 Fixed 2016 Variable 2017 Variable 2017 Fixed 2017 Fixed 2017 Fixed 2017 Variable June Totals C Expense ATM Withdrawal Cable/Internet Car Insurance Car Loan: Credit Card Payment Water Bill ATM Withdrawal Cable/Internet Car Insurance Car Loan Credit Card Payment ATM Withdrawal Cable/Internet Car Insurance Car Loan Credit Card Payment June2017 D Amount AnnualExp 3995 1188.06 1112.16 2796 2460 493.27 4240 1215.96 1157.64 2796 3290 4465 1233.96 1157.64 2796 3924 E HomeLoan C C C C W A1 1 2 9 10 11 12 13 14 15 3 4 Amount to Finance 5 Interest Rate 6 Term (months) 7 8 16 17 18 19 Retall Cost Down Payment 20 21 22 23 24 25 x fx Mortgage Options A Monthly Payment JuneTotals 120 180 240 360 June2017 B Mortgage Options $265,500 $24,000 $241,500 6.75% 180 $2,137.06 6.00% 6.00% AnnualExp 6.25% 6.25% 6.50% 6.50% Home Loan 6.75% 6.75% CarLoan 7.00% 7.00% Stocks G 7.25% 7.25% + 7.50% 7.50% 1 2 3 4 Payment 5 APR 6 Years 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 2227 23 24 Payment Detalls Pmts per Year Payment Number 1 2 3 4 5 6 THE 7 B 8 9 10 Totals 233 5.75% 5 12 Beginning Balance $12,124.83 $11,949.93 $11,774.19 $11,597.60 $11,420.18 $11,241.90 $11,062.76 $10,882.77 $10,701.92 $10,520.20 June Totals Payment Amount Car Loan $ 2,330.00 $ June2017 Loan Details Loan Periodic Rate # of Payments Cumulative Principal Repayment Interest Paid 233 58.09813185 233 57.2600604 233 56.41797319 233 55.57185098 233 54.72167443 233 53.86742412 233 53.00908053 233 52.14662404 233 51.28003495 233 50.40929345 182.5907066 542.782148 542.78 $ 1,787.22 Interest 174.9018681 58.0981319 175.7399396 115.358192 176.5820268 171.776165 177.428149 227.348016 178.2783256 282.069691 179.1325759 335.937115 179.9909195 388.946196 180.853376 441.09282 181.7199651 492.372855 $12,124.83 0.479% 60 AnnualExp HomeLoan Cumulative Principal CarLoan Stocks A1 1 2 3 4 5 6 7 8 9 COMPANY 10 11 12 13 14 15 16 17 18 19 8722 20 21 23 24 25 X fx Stock Purchases June Totals B Stock Purchases June2017 Annual Exp D HomeLoan E CarLoan G Stocks Steps to Step 1 2 5 6 7 8 9 m: Instructions Start Excel. Open exploring ecap_grader_c2_Transactions.xlsx and save the workbook as exploring_ecap_grader_c2_Transactions_LastFirst. On the June Totals worksheet, sort the data in the range A3:E16 in ascending order by Category. At each change in Category, use the Sum function to add subtotals to the data in the Amount column. Accept all other defaults. Collapse the outline to show the grand total and Category subtotals only. Create a Pivot Table in cell F1 on the AnnualExp worksheet using the data in the range A1:D17. Add the Expense field to the PivotTable as a row label; add the Amount field as the value; add the Year field as the column label. Change the format of the values in the Pivot Table to accounting with no decimal places and set columns E1 to AutoFit Column width. Add the Category field to the Report Filter area of the PivotTable. Filter the data so that only expenses in the Variable category are displayed. Display the values as percentages of the grand total. Insert a Year slicer in the worksheet and use the slicer to filter the data so that only data from 2015 is displayed. Change the height of the slicer to 2" and then reposition it so that the top left corner aligns with the top left corner of cell 12. Create a PivotChart based on the data in the PivotTable using the default Pie chart type. Change the chart title text to Variable Expenses and remove the legend. Add data labels to the outside end position displaying only the category names and leader lines. Reposition the chart so that the top left corner aligns with the top left corner of cell F13. Note, Mac users, select the range FS:G7, and on the Insert tab, click Ple, and then click Pie. Follow the remaining instructions. On the HomeLoan worksheet, in cell A10, enter a reference to the monthly payment from column B. Create a one-variable data table for the range A9:H10 using the interest rate from column B as the Row input cell. On the HomeLoan worksheet, in cell A12, enter a reference to the monthly payment from column B. Create a two-variable data table in the range A12:H16, using the interest rate from column B as the Row input cell and the term in months from column B as the Column input cell. On the HomeLoan worksheet, perform a goal seek analysis to determine what the down payment in column B needs to be if you want the monthly payment in column B to be 2000. Accept the solution. Points Possibl 0 10 11 12 13 14 15 16 17 18 19 20 On the HomeLoan worksheet, create a scenario named Maximum using cells B2, B3, B5, and B6 as the changing cells. Enter these values for the scenario: 280000, 24000, .075, and 360, respectively. Show the results and dose the Scenario Manager. Undo the last change. On the June2017 worksheet, in cell 17, sum the values in E7:E24 if the purchase in column C is groceries; in cell 18, average the values in E7:E24 if the purchase in column C is groceries; in cell 19, calculate the number of times groceries were purchased during the month. On the June2017 worksheet, in cell 111, calculate the total amount spent on groceries using a credit card; in cell 112, calculate the average spent on groceries using a credit card; in cell 113, calculate the number of times groceries were purchased using a credit card during the month. On the June2017 worksheet, in cell F7, nest an AND function within an IF function to determine if the transaction was paid using a credit card and the amount of the transaction is less than -100. If both conditions are met in the AND function, the function should return the text Flag. For all others, the function should return the text OK. Copy the function down through cell F24. On the June2017 worksheet, in cell E4, use the INDEX function to identify the transaction amount that aligns with the position in cell C4. Type 5 for the columo.num, and use the range A7:E24 as the array argument. On the June2017 worksheet, filter the data in the range A6:E24 using the criteria in the range A27:E28. Set the filter to copy the data to the range A31:E31. In cell 117, use the DAVERAGE function to determine the average amount spent for transactions meeting the criteria in the range A27:E28. Group the June2017 and JuneTotals worksheets together. Fill the contents and formatting from cell A1 on the JuneTotals, worksheet across the grouped worksheets. Ungroup the worksheets. In cell 119 on the June2017 worksheet, insert a reference to cell E26 on the June Totals, worksheet. On the June2017 worksheet, create a validation rule for the range D7:D24 to only allow values in the list from the range 121:124. Create an error alert for the rule that will display after invalid data is entered. Using the Stop style, enter Invalid Entry as the title and Please select a valid method. (including the period) as the error message. On the Carloan worksheet, in cell G10, use the CUMPRINC function to calculate the cumulative principle paid on the car loan. Use the data in the range E4:E6 when entering the first three arguments in the function. Reference cell A10 as the start and end period arguments. Enter 0 as the type argument. Modify the function to convert the results to a positive value. Make the row references absolute in all arguments except for the end period. Copy the formula down through cell G19. [SKIP - No Point] From Excel, open the downloaded tab-delimited text file eV2_stocks.bxt using a data format of general. You do not need to create a connection to the original data file. Copy the range A1:F5 and then close the text file. Paste the copied range onto the Stocks worksheet in cell A3. [SKIP-No Point] On the Stocks worksheet, separate the text in the first column into two columns using the asterisk (*) as the delimiter. Insert a function in cell A3 that will display the text in cell A9 with initial capitalization only. 9 9 3 5 6 0 10 Step 21 22 23 24 Instructions [SKIP-No Point] On the Stocks worksheet, import the downloaded XML file eV2_highclose.xm/into cell A11. Open the XML file in Notepad, and then find and replace all instances of the text General Electric with General Electric Co (no period). Save the XML document and close Notepad. Refresh the XML data on the Stocks worksheet. Note, Mac users, import the comma-delimited eV2_highclose.csv file into cell A11. Open the CSV file in Excel, and then find and replace all instances of the text General Electric with General Electric Co (no period). Save and close the CSV document. Refresh the data on the Stocks worksheet. [SKIP-No Point] Apply the Retrospect theme to the workbook. Apply the cell style Accent1 to cells A3 and D3 on the Carloan worksheet. [SKIP-No Point] Set the Author property of the workbook to Exploring Excel Student and then set the Title property to Personal Finances. Save the workbook. Ensure that the following worksheets are present (in this order): June Totals, June2017, AnnualExp, HomeLoan, Carloan, Stocks. Close the workbook, and then submit the file as directed. Total Points Points Possible 0 0 O 84 A Date Payee 6/5/17 South & Main 6/4/17 Visa 6/10/17 SEC 6/3/17 Sam's Stop 6/9/17 Gas-N-Wash 6/13/17 Gas-N-Wash 6/2/17 George's SuperMart 6/8/17 George's SuperMart 6/12/17 George's SuperMart 6/1/17 Zachary Kaye 6/6/17 Income 6/11/17 Springfield Savings & Loans 6/7/17 (Savings Account] June Transactions Category ATM Withdrawal ATM Withdrawal Total Credit Card Payment Credit Card Payment Total Electric Bill Electric Bill Total Gas Gas Gas Gas Total Groceries Groceries Groceries Groceries Total Rent Rent Total Salary Salary Total Student Loan Method of Payment Debit Card Student Loan Total Transfer to Savings Transfer to Savings Total Grand Total Check Check Debit Card Debit Card Credit Card Credit Card. Credit Card Debit Card Check Check Check Transfer $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ssssssss $ $ $ $ Amount (202.00) (202.00) (300.00) (300.00) (217.61) (217.61) (61.13) (57.49) (65.33) (183.95) (114.62) (136.52) (173.06) (424.20) (800.00) (800.00) 2,034.78 2,034.78 (175.00) (175.00) (300.00) (300.00) (567.98) 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 ***2=== 24 25 26 27 28 29 30 31 32 Date Payee 6/1/17 Zachary Kaye 6/4/17 Va 6/8/17 South & Main 6/8/17 Income 6/8/17 (Savings Account Date Date 6/15/17 SEC 6/15/17 Springfield Savings & Loans 6/18/17 Gas-N-Wash 6/18/17 SGW 6/22/17 South & Main 6/22/17 Income 6/22/17 (Savings Account 6/23/17 George's SuperMart 6/25/17 CN Solutions 6/25/17 Springfield Savings & Loans 6/28/17 AMP Insurance 6/29/17 Sam's Stop 6/30/17 George's SuperMart Payee Position: Payee June Totals Search Results 8 Category Rent Credit Card Payment ATM Withdrawal Salary Transfer to Savings Electric Bill Student Loan GM Water Bill ATM Withdrawal Salary Transfer to Savings Groceries Cable/Internet Car Loan Car Insurance GM Groceries Criteria June2017 Extract Category Category Method of Payment Check Check Debit Card Check Transfer Check Check Credit Card Check Debit Card Check Transfer Credit Card Check Check Check Debit Card Debit Card Amount: Method of Payment Credit Card Method of Payment AnnualExp Amount -800 -300 -202 2034.78 -300 -217.61 -175 -65.33 -43.59 -262 2034.78 -300 -109.64 -102.83 HomeLoan -233 -96.47 -60.08 -149.68 Amount Amount CarLoan Total Cost: Average Cost Occurrences Groceries Credit Card Total Cost Average Cost Occurrences: Groceries Average Credit Card Purchase: Total Remaining Payment Options: Stocks Check Credit Card Debit Card Transfer + 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 2222 23 24 25 A Year B Category 2015 Variable 2015 Fixed 2015 Fixed 2015 Fixed 2015 Variable 2015 Variable 2016 Variable 2016 Fixed 2016 Fixed 2016 Fixed 2016 Variable 2017 Variable 2017 Fixed 2017 Fixed 2017 Fixed 2017 Variable June Totals C Expense ATM Withdrawal Cable/Internet Car Insurance Car Loan: Credit Card Payment Water Bill ATM Withdrawal Cable/Internet Car Insurance Car Loan Credit Card Payment ATM Withdrawal Cable/Internet Car Insurance Car Loan Credit Card Payment June2017 D Amount AnnualExp 3995 1188.06 1112.16 2796 2460 493.27 4240 1215.96 1157.64 2796 3290 4465 1233.96 1157.64 2796 3924 E HomeLoan C C C C W A1 1 2 9 10 11 12 13 14 15 3 4 Amount to Finance 5 Interest Rate 6 Term (months) 7 8 16 17 18 19 Retall Cost Down Payment 20 21 22 23 24 25 x fx Mortgage Options A Monthly Payment JuneTotals 120 180 240 360 June2017 B Mortgage Options $265,500 $24,000 $241,500 6.75% 180 $2,137.06 6.00% 6.00% AnnualExp 6.25% 6.25% 6.50% 6.50% Home Loan 6.75% 6.75% CarLoan 7.00% 7.00% Stocks G 7.25% 7.25% + 7.50% 7.50% 1 2 3 4 Payment 5 APR 6 Years 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 2227 23 24 Payment Detalls Pmts per Year Payment Number 1 2 3 4 5 6 THE 7 B 8 9 10 Totals 233 5.75% 5 12 Beginning Balance $12,124.83 $11,949.93 $11,774.19 $11,597.60 $11,420.18 $11,241.90 $11,062.76 $10,882.77 $10,701.92 $10,520.20 June Totals Payment Amount Car Loan $ 2,330.00 $ June2017 Loan Details Loan Periodic Rate # of Payments Cumulative Principal Repayment Interest Paid 233 58.09813185 233 57.2600604 233 56.41797319 233 55.57185098 233 54.72167443 233 53.86742412 233 53.00908053 233 52.14662404 233 51.28003495 233 50.40929345 182.5907066 542.782148 542.78 $ 1,787.22 Interest 174.9018681 58.0981319 175.7399396 115.358192 176.5820268 171.776165 177.428149 227.348016 178.2783256 282.069691 179.1325759 335.937115 179.9909195 388.946196 180.853376 441.09282 181.7199651 492.372855 $12,124.83 0.479% 60 AnnualExp HomeLoan Cumulative Principal CarLoan Stocks A1 1 2 3 4 5 6 7 8 9 COMPANY 10 11 12 13 14 15 16 17 18 19 8722 20 21 23 24 25 X fx Stock Purchases June Totals B Stock Purchases June2017 Annual Exp D HomeLoan E CarLoan G Stocks
