I need help with this Assessment. Can you explain step by step on how to do it on excel please.

11. Complete the rest of the Amortization Schedule for the car. BA 216 - Data Analytics with Excel (Hint: In cell J51, the balance should be zero if done correctly.) 12. Read Carefully: BMW Financial also includes a Depreciation Factor for the car to assist the Final Group Assessment Instructions buyer in their decision. Most 7 series BMW have a life of approx. 10 years and tend to have a trade in value of about $55,000 based on mileage. Based on the information provided, enter the Data File Needed: JonabyFinancialAnalysisDataFile.xIsx BMW Depreciation Terms in cells 08 to Q10 Project: Brad Jonaby is Financial Advisor at PNB Advisors LLC in the Portland Metro area. One of his 13. In cells P16 to P25, calculate the Straight-Line Depreciation. clients asked his client to perform a financial health check ahead of their 50th Birthday because they were 14. In cells R16 to R25, calculate the new Depreciated Value of the Asset. interested in purchasing a new car. The client provided Brad with a Bank Statement of their expense 15. In cells P29 to P38, calculate the Declining Balance Depreciation from January to April 2023, Information about their current income. and the approved loan terms that were given from BMW Financial. As his lead analyst, Brad has asked you to assess the current financial 16. In cells R16 to R25, calculate the new Depreciated Value of the Asset. health of the client and determine whether they can afford to make the purchase ahead oi their big day. 17. In cells P46 to U46, calculate the cumulative interest payments. In cells P47 to U47, calculate the Afterwards. Brad asked if you could research and provide answers to 8 scenarios for meetings with some cumulative principal payments. potential clients next week (Hint: Cell U48 should reflect $0.00) Complete the following steps: 18. Navigate to the Monthly Income Statement worksheet. 1. On the Monthly Income Statement worksheet, enter the following: Name in Cell 85:05 Read Carefully: Through conversation with the client, we learned their Annual Salary is $140,000. . G-Number in Cell E5:G5 Additional Income includes investment returns in the amount of $28,000 from a rental property that is 2. Navigate to the Bank Statement Transaction worksheet, convert the data range into an excel owned by a trust and $11,500 from instructing at the local college. The client is subject to a 32% tax table, and insert the following: rate. . Change the Table Design to Red. Table Style Medium 19. 19. In cells C7 to C10, insert the appropriate information from above. A Totals Row to the Excel Table 20. In cell C11, calculate the client's Total Gross Income. Count in the Transaction Type Column (Column C) 21. In cell C12, calculate the client's Income after Taxes Sum in the Amount Column (Column G) 22. In cell C13:014, calculate the monthly Income before Taxes. In cell C15:016, calculate the 3. Navigate to the BMW Financial Analysis worksheet monthly Income after Taxes. Read Carefully: The car the client wants to purchase is a 2023 BMW 750i that has a sticker price of 23. Using the excel table we created on the Bank Statements Transactions worksheet, create a $136,778 and was approved at an interest rate of 7.00% for 8 years or 72 months PivotTable, and place it in cell B28. 24. Name of the Pivot Table to Allocated Expenses and adjust the fields list to display the following: 4. In cells C5 to C10, enter the BMW Financial Loan Terms. Expense Category as Rows 5. In cell C11, calculate the monthly payment of the loan. Date as Columns (Month Only) (Hint: If done correctly, the payment amount in will equal $2,331.93.) Expense Amounts as Totals 25. Format the PivotTable to include the following: 6. In cell C15, using a cell reference, enter the principal value of the loan. Select PivotTable Style - Red, Pivot Style Medium 7. In cell D15, calculate the Interest Payment. Add Banded Rows to the Pivot Table B. In cell E15, calculate the Principal Payment. Only show Total Averages for Rows. B. In cell F15, sum cells D15 and E15 to calculate the Total Payment. 10. In cell C16, calculate the next starting principal balance All Dollar Amounts in Accounting Format BA 216: Week 10 Final Group Assessment Page 1 of 3 BA 216: Week 10 Final Group Assessment Page 2 of 326. Insert a slicer for Bank Accounts between cells D7:G9. The slicer should display two columns and have a Slicer Style of Rose, Slicer Style Dark 4. 27. Insert another slicer for Expense Category between cells D11:G18. The slicer should display three columns and have a Slicer Style of Rose, Slicer Style Dark 4 28. Insert a timeline slicer for the months January to December in between cells C20:G25. The timeline slicer should reflect the following: Remove the Scrollbar, Selection Label, and Time Level Rename the timeline slicer to Expense Periods 29. Insert a Pie Chart through PivotCharts in cells B44:G60 and complete the following: Place the Pie chart to the left and the legend to the right. Add Data Labels displayed as percentages. (Formatted to be easily read.) Place a black boarder around the entire chart. (Hint: Make sure all expense categories are visible in your chart.) 30. In cell N9:N11, using a reference, insert the clients Average Monthly Salary After Taxes. 31. In cells N15:N29 using a variation of the IF Function, calculate the average monthly expense for each category using the Excel Table on the Bank Statement Transaction worksheet 32. In cell N30 using a reference, insert the BMW Car Loan Payment Amount. (Hint: If done correctly, the TOTAL GENERAL EXPENSES should display negative $11,548.69.) 33. In cell J37:N39, write a response to the client detailing whether they should purchase this car for their birthday. Minimum Two Sentences. 34. Navigate to the Question Prompt Worksheet. Read Carefully: Answer the questions. Use the loan terms and depreciation factor cells to assist you. Each question is worth 2 points total (1 for using the right formula & 1 for generating the appropriate answer.) 35. Review your work and save the Workbook with the same name followed by your initials. Submit the file to the Assignments dropbox.BA-216: Data Analytics Final Group Project Bank Statements Transactions Transaction Tuna Transaction Mum. Data Bank Account Amount Mortgage 43435 1/4/2023 OnPaint CU 3,568.00 Home Insurance Exporise 43437 17/2023 Bank of the West 1309.51 Water Bill 43450 1/15/2023 Bank of the West (14400 PGE 43455 1/15/2023 Bank of the: West (450.00 Youtube TV Exportsa 43510 1/15/2023 OnPaint CU (64.99 WIFI 43519 1/15/2023 Bank of the West (234.50 Pelatari Exporise 43521 1/15/2023 On Point CU 126.92 43523 1/15/202 On Point CU (91.41 Credit Card 43536 1/15/2023 Bark of the West (1,000.00) Student Loans Expense 43541 1/19/2023 On Paint CU (600.00 Pet Insurance 43798 1/242023 Bank of the West (200.00) Work Phone: Bill Expryise 4379 1/25/2023 Bank of the West [143.92) Bark Firkis 43011 1/25/2023 Bank of the West (274.74) 43816 1/252023 OnPain CU [1,455.00) Gym 43871 1/25/2023 Bank of the West (45.00) Mortgage 43880 22/2023 On Paint CU (3,568.00 Home Insurance 43842 2/52023 OnPoint CU (309.51) Water Bill Experise 43084 2/13/2023 Bank of the West [162.72 PGE Expense 43897 2/13/2023 OnPoint CU (508.50) Youtube TV 13902 2/13/2023 Bank of the West (64.99) WIFI 44157 2/13/2023 Bank of the West (234.50 Pelatari 44159 2/132023 Bark of the West [126.92 Dark Fires 14181 2/15/2023 On Paint CU 10.05) 44172 2/15/2023 OnPaint CU (103.29 Credit Card 44177 2/15/2023 Bank of the West (1, 130.00) Student Loans 44232 2/17/2023 On Paint CU [1,000.00) Pet Insurance 44241 2/22/2023 On Paint CU (200.00) Work Phone Bill 44243 2/23/2023 Bank of the West (143.92) Credit Card 44245 2/23/2023 On Point CU 1310.46 Entertainment 44258 2/23/2023 Bank of the West (5,222.00 Gym Expertsa 44283 2/23/2023 Bank of the West 145.00) Mortgage 44518 3/1/2023 Bank of the West 3,568.00 Home Insurance 44520 342023 OnPoint CU (309.51) Water Bill Exporise 44533 3/12/2023 Bank of the West (174.11) PGE Expense 44538 3/12/2023 OnPaint CU (544.10) Youtube TV 44593 3/12/2023 On Paint CU (64.99 WIFI 44802 3/12/2023 Bank of the West (234.50 Pelatari 44804 312/2023 OnPaint CU (126.92 14806 3/12/2023 Bank of the West (110.52 Credit Card Expose 44819 312/2023 Bark of the West (1,209.10 Studeril Loaris 44824 3/16/2023 Bank of the West (455.00) Bank Fires Exportsa 44829 321/2023 Bank of the West (0.45) Pet Insurance Exportse 44879 3/21/2023 On Point CU (200.00) Work Phone Bill 44081 3/22/2023 Bank of the West (143.92) Credit Card 44894 /22/2023 OnPoint CU (332.19] Entertainment 44899 3/22/2023 OnPoint CU (450.00 Gym Expense 44954 3/22 2023 Bank of the West 145.00 Mortgage 14983 46 2023 OnPaint CU 3,588.00) Home Insurance Expense 44985 Bank of the West 1408.89 Water Bill 44987 4/17/2023 Bank of the West (217.64) PGE Exportse 44980 4/17/2023 Bank of the West (630.12) Youtube TV 44985 4/17/2023 OnPoint CU (81.24) WIFI 15240 4/17/2023 Bank of the West (293.13 Palatani Exporise 45253 4/17/2023 OnPaint CU [158.65 45258 4/17/2023 Bank of the West (138.15 Credit Card 5313 4/21/2023 OnPaint CU (200.00 Studeril Loaris 15322 428/2023 Bank of the West 1770.00 Pet Insurance 45324 4/27/2023 Bark of the West (250.00 Work Phone Bill 45326 4/27/2023 Bank of the West (179.90 Bank Fires Expertse 45339 4/27/2023 On Paint CU (415.24) Entertainment Exporise 45344 427/2023 Bank of the West 727.60 4559 424/2023 153.25 34 151.951BA-216: Data Analytics Final Group Project BMW Financial Analysis BMW Financial Loan Terms $136,778 Sale Price Annual Rate Payment per Year Rate Per Period (RATE] Years Payments (NPER) Payment (PMT) Amortization Schedule Period Remaining Principle Interest Payment Principal Payment Total Payment Year Remaining Principle Interest Payment'rincipal Paymer Total Payment 37 Year Period 38 39 40 41 42 43 44 45 46 GEBREGONODAWN 47 48 49 50 51 52 53 16 54 5 17 55 18 56 19 20 58 21 59 22 60 23 61 24 62 25 63 26 64 27 65 28 66 6 29 67 30 68 31 69 32 70 33 71 34 72 35 Final Balance S 36BMW Depreciation Factor Terms Current Price (Cost Salvage Value (Salvage) Life of Asset (Life Straight-Line (SLN) Year Yearly Deprecation Cumulative DeprecationDepreciated Asset Value Declining Balance (DB] Year Yearly Deprecation Cumulative Deprecationepreciated Asset Value Cumulative Interest and Principal Payments per Year Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 1 13 25 37 49 61 Months 12 24 36 48 60 72 Total Interest Principal S Principal Remaining | $ 136,778.00 S 136, 778.00 S 136, 778.00 S 136,778.00 S 136,778.00 $ 136,778.00