EX16_XL_Comp_Grader_Cap_Hw

Looking for help on this spreadsheet project

Project Description:

In this project, you will update a workbook to display bank transactions as a PivotTable. You will filter the PivotTable, format the values, display the values as calculations, and create a PivotChart using this data. Additionally, you will sort and subtotal data, create data tables, and use Goal Seek and Scenario Manager. You will format grouped worksheets, set up validation rules, and create functions. You will also import a text file and XML data and manipulate the imported text. Finally, you will modify the document properties, insert a comment, and mark the workbook as final.

Start Excel. Open exploring_ecap_grader_c2_Transactions.xlsx and save the workbook as exploring_ecap_grader_c2_Transactions_LastFirst.

On the JuneTotals 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 PivotTable 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 PivotTable to accounting with no decimal places and set colums F:J 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 I2.

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.

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.

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 close the Scenario Manager. Undo the last change.

On the June2017 worksheet, in cell I7, sum the values in E7:E24 if the purchase in column C is groceries; in cell I8, average the values in E7:E24 if the purchase in column C is groceries; in cell I9, calculate the number of times groceries were purchased during the month.

On the June2017 worksheet, in cell I11, calculate the total amount spent on groceries using a credit card; in cell I12, calculate the average spent on groceries using a credit card; in cell I13, 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 column_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 I17, 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 I19 on the June2017 worksheet, insert a reference to cell E26 on the JuneTotals worksheet.

On the June2017 worksheet, create a validation rule for the range D7:D24 to only allow values in the list from the range I21:I24. 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.

From Excel, open the downloaded tab-delimited text file eV2_stocks.txt 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.

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.

On the Stocks worksheet, import the downloaded XML file eV2_highclose.xml 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.

Apply the Retrospect theme to the workbook. Apply the cell style Accent1 to cells A3 and D3 on the CarLoan worksheet.

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): JuneTotals, June2017, Annual Exp, HomeLoan, CarLoan, Stocks. Close the workbook, and then submit the file as directed.

Notes:

NAME *SymbolDateSharesPriceCost
Yahoo *YHOO7/17/201512013.611633.2
Intel Corporation *INTC7/17/201510020.082008
General Electric *GE7/17/201512015.81896
Microsoft Corporation *MSFT7/17/2015100262600

 

 

June Transactions
Date
Payee
6/1/2017 Zachary Kaye
6/2/2017 George's SuperMart
6/3/2017 Sam's Stop
6/4/2017 Visa
6/5/2017 South & Main
6/6/2017 Income
6/7/2017 [Savings Account]
6/8/2017 George's SuperMart
6/9/2017 Gas-N-Wash
6/10/2017 SEC
6/11/2017 Springfield Savings & Loans
6/12/2017 George's SuperMart
6/13/2017 Gas-N-Wash Category
Rent
Groceries
Gas
Credit Card Payment
ATM Withdrawal
Salary
Transfer to Savings
Groceries
Gas
Electric Bill
Student Loan
Groceries
Gas Method of Payment
Check
Credit Card
Debit Card
Check
Debit Card
Check
Transfer
Credit Card
Debit Card
Check
Check
Debit Card
Credit Card $
$
$
$
$
$
$
$
$
$
$
$
$ Amount
(800.00)
(114.62)
(61.13)
(300.00)
(202.00)
2,034.78
(300.00)
(136.52)
(57.49)
(217.61)
(175.00)
(173.06)
(65.33) Position:
Date
Payee
6/1/2017 Zachary Kaye
6/4/2017 Visa
6/8/2017 South & Main
6/8/2017 Income
6/8/2017 [Savings Account]
6/15/2017 SEC
6/15/2017 Springfield Savings & Loans
6/18/2017 Gas-N-Wash
6/18/2017 SGW
6/22/2017 South & Main
6/22/2017 Income
6/22/2017 [Savings Account]
6/23/2017 George's SuperMart
6/25/2017 CIV Solutions
6/25/2017 Springfield Savings & Loans
6/28/2017 AMP Insurance
6/29/2017 Sam's Stop
6/30/2017 George's SuperMart Search Results
8
Category
Rent
Credit Card Payment
ATM Withdrawal
Salary
Transfer to Savings
Electric Bill
Student Loan
Gas
Water Bill
ATM Withdrawal
Salary
Transfer to Savings
Groceries
Cable/Internet
Car Loan
Car Insurance
Gas
Groceries Date Payee Criteria
Category Date Payee Extract
Category Amount:
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
-800
-300
-202
2034.78
-300
-217.61
-175
-65.33
-43.59
-262
2034.78
-300
-109.64
-102.83
-233
-96.47
-60.08
-149.68 Method of Payment
Credit Card Amount Method of Payment Amount Groceries
Total Cost:
Average Cost:
Occurrences:
Groceries - Credit Card
Total Cost:
Average Cost:
Occurrences: Average Credit Card
Purchase:
Total Remaining:
Check
Credit Card
Payment Options:
Debit Card
Transfer Year
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 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 Amount
3995
1188.06
1112.16
2796
2460
493.27
4240
1215.96
1157.64
2796
3290
4465
1233.96
1157.64
2796
3924 Mortgage Options
Retail Cost
Down Payment
Amount to Finance
Interest Rate
Term (months)
Monthly Payment 120
180
240
360 $265,500
$24,000
$241,500
6.75%
180
$2,137.06
6.00% 6.25% 6.50% 6.75% 7.00% 6.00% 6.25% 6.50% 6.75% 7.00% 7.25% 7.50% 7.25% 7.50% Car Loan
Payment Details
Payment
233
APR
5.75%
Years
5
Pmts per Year
12
Payment
Number
1
2
3
4
5
6
7
8
9
10
Totals Loan Details
Loan
$12,124.83
Periodic Rate
0.479%
# of Payments
60 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 Payment
Amount
233
233
233
233
233
233
233
233
233
233
$ Principal
Interest Paid
Repayment
58.0981318548 174.901868145
57.2600604033 175.739939597
56.4179731927 176.582026807
55.5718509809 177.428149019
54.7216744336 178.278325566
53.8674241236 179.132575876
53.0090805308 179.990919469
52.1466240417 180.853375958
51.2800349486 181.719965051
50.4092934494 182.590706551 2,330.00 $ 542.78 $ 1,787.22 Cumulative Cumulative
Interest
Principal
58.0981319
115.358192
171.776165
227.348016
282.069691
335.937115
388.946196
441.09282
492.372855
542.782148 Stock Purchases COMPANY

• 1493691458-exploring_ecap_grader_c2_Transactions.xlsx

ComputerScienceExpert

(11)

Status NEW Posted 02 May 2017 02:05 AM My Price 9.00

-----------

Attachments

file 1493691463-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer