Exp19_Excel _AppCapstone_ Compassessment_ Manufacturing Project.

please answer questions 22-25

Excel 2019 Prod der Instructions Points Possible Instructions Step 5 22 Create a data validation list in cell D5 that displays Quantity, Payment Type, and Amount. Type the Trans# 30038C in cell B5 and select Quantity from the validation list in cell 05 2 23 3 24 Enter a nested lookup function in cell F5 that evaluates the Trans #in cell B5 as well as the Category in cell D5, and returns the results based on the data in the range A3:F32. 5 25 Create a Pivot Table based on the range A7 G32. Place the PivotTable in cell 117 on the current worksheet. Place Payment Type in the Rows box and Amount in the Values box Format the Amount with Accounting Number Format. 26 Insert a PivotChart using the Pie chart type based on the data. Place the upper-left corner of the chart inside cell 122. Format the Legend of the chart to appear at the bottom of the chart area. Format the Data Labels to appear on the Outside end of the chart. 3 27 5 28 Insert a Slicer based on Date. Place the upper-left corner of the Slicer inside cell L8. Use PowerQuery to connect to the Access database Exp19_Excel_AppCapstone_Comprehensive_Inventory.accdb. Load the inventory table into a new worksheet named Inventory Note, Mac users, download and import the delimited Inventory.bxt file into a new worksheet named Inventory starting in cell A1. 2 29 Create a footer with your name on the left, the sheet code in the center, and the file name on the right for each worksheet 0 30 Save the file Exp19_Exce_App Capstone_Comprehensive Assessment- Manufacturing xlsx Exit Excel Submit the file as directed Total Points 100 46 M Beta Manufacturing Employee Withholdings Deduction Withholding Statistics Number of FT Employees Average Salary Full Time Additional Information Average Salary of FT >1 Dependent Dependents 0 1 2 3 4 Deduction $ 50.00 $ 125.00 $ 250.00 $325.00 $ 500.00 2 3 4 Employee ID Status 5 7276 FT 6 9858 PT 7 8936 FT 8 6881 FT 9 3852 FT 10 6580 PT 11 4772 FT 12 4296 PT 13 8884 FT 9916 FT 15 5803 FT 16 8708 FT 17 5565 PT 18 6142 PT 19 8164 FT 20 4338 FT 21 5833 PT 22 6060 FT 23 2559 PT 24 5569 PT 25 3488 PT 26 7353 PT 27 4990 PT 28 4785 PT 29 3090 FT 30 8087 PT 31 4474 PT 32 4251 PT 33 5324 PT 3123 PT 35 36 37 38 39 Dependents 4 $ 2 $ 4 $ 5 $ 1 5 $ 0 $ 3 $ 4 $ 5 $ 4 $ 1 $ 1 $ 0 $ 2 $ 1 $ 5 $ 4 $ 4 $ 4 $ 1 $ 4 $ 1 $ 5 $ 1 $ 4 $ $ 4 $ 5 $ S Salary 90,212 18,984 42,906 37,191 52,528 17.126 94,101 12,471 67,132 88,604 55,680 98,869 11,994 17,957 66,693 84,432 18,994 65,648 18,835 15,525 15,861 16,173 9,893 17,081 39,364 19,306 9,933 16,707 13,665 7,689 FICA Withholdings FT at least 1 Dependent PT or FT No Dependents 7% 5% Employee ID Dependents Status FT Salary Deduction withholdings O OU 41 42 Insurance Facilities Sales File Home O PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in A1 B Facility Amortization Table Loan Details Loan $325,000.00 Periodic Rate 0.479% # of Payments 60 Principal Interest Paid Repayment Remaining Balance Cumulative Interest Cumulative Principal 1 2 3 4 5 Payment Details 6 Payment $6,245.45 7 APR 5.75 5 8 Years 12 9 Pmts per Year 10 Payment Beginning Payment 11 Number Balance Amount 12 1 13 2 14 3 15 4 16 5 17 6 18 7 19 8 20 9 21 10 22 11 12 24 13 25 14 26 15 27 16 28 17 29 18 19 31 20 32 21 33 22 23 35 24 36 25 Facilities EXI Insurance Sales Type here to search O RE 46 Results ES H G F E C D B 1 March Transactions 2. Search Results Results Category Status Status Trans # 3 4 5 Trans # 6 7 Date 8 3/1/2018 9 3/2/2018 10 3/3/2018 11 3/3/2018 12 3/5/2018 13 3/7/2018 14 3/9/2018 15 3/10/2018 16 3/10/2018 17 3/10/2018 18 3/11/2018 19 3/11/2018 20 3/12/2018 21 3/13/2018 22 3/13/2018 23 3/14/2018 24 3/18/2018 25 3/24/2018 26 3/24/2018 27 3/24/2018 28 3/28/2018 29 3/28/2018 30 3/30/2018 31 3/30/2018 32 3/30/2018 33 34 35 36 Item Quantity Payment Type 3003 8 Credit 5005 4 Financed 5005 1 Credit 5005 10 Financed 1001 6 Credit 5005 5 Credit 1001 5 Credit 5005 3 Credit 2002 1 Financed 4004 4 Financed 2002 9 Financed 3003 8 Credit 1001 9 Financed 2002 8 Financed 2002 9 Credit 3003 9 Credit 3003 1 Credit 4004 1 Credit 4004 3 Financed 4004 10 Cash 2002 10 Credit 1001 9 Cash 3003 8 Credit 4004 6 Financed 2002 9 Cash Amount $4,800 $2,800 $700 $7,000 $1,500 $3,500 $1,250 $2,100 $500 $2,600 $4,500 $4,800 $2,250 $4,000 $4,500 $5,400 $600 $650 $1,950 $6,500 $5,000 $2,250 $4,800 $3,900 $4,500 8 Insurance Facilities Sales Type here to search Extract Warehouse Inventory - Notepad File Edit Format View Help Item Quantity 1001 5000 40000 2002 3500 35000 3003 2500 30000 4004 4000 22000 5005 1000 18000 Total Value B D I Excel 2019 Prod der Instructions Points Possible Instructions Step 5 22 Create a data validation list in cell D5 that displays Quantity, Payment Type, and Amount. Type the Trans# 30038C in cell B5 and select Quantity from the validation list in cell 05 2 23 3 24 Enter a nested lookup function in cell F5 that evaluates the Trans #in cell B5 as well as the Category in cell D5, and returns the results based on the data in the range A3:F32. 5 25 Create a Pivot Table based on the range A7 G32. Place the PivotTable in cell 117 on the current worksheet. Place Payment Type in the Rows box and Amount in the Values box Format the Amount with Accounting Number Format. 26 Insert a PivotChart using the Pie chart type based on the data. Place the upper-left corner of the chart inside cell 122. Format the Legend of the chart to appear at the bottom of the chart area. Format the Data Labels to appear on the Outside end of the chart. 3 27 5 28 Insert a Slicer based on Date. Place the upper-left corner of the Slicer inside cell L8. Use PowerQuery to connect to the Access database Exp19_Excel_AppCapstone_Comprehensive_Inventory.accdb. Load the inventory table into a new worksheet named Inventory Note, Mac users, download and import the delimited Inventory.bxt file into a new worksheet named Inventory starting in cell A1. 2 29 Create a footer with your name on the left, the sheet code in the center, and the file name on the right for each worksheet 0 30 Save the file Exp19_Exce_App Capstone_Comprehensive Assessment- Manufacturing xlsx Exit Excel Submit the file as directed Total Points 100 46 M Beta Manufacturing Employee Withholdings Deduction Withholding Statistics Number of FT Employees Average Salary Full Time Additional Information Average Salary of FT >1 Dependent Dependents 0 1 2 3 4 Deduction $ 50.00 $ 125.00 $ 250.00 $325.00 $ 500.00 2 3 4 Employee ID Status 5 7276 FT 6 9858 PT 7 8936 FT 8 6881 FT 9 3852 FT 10 6580 PT 11 4772 FT 12 4296 PT 13 8884 FT 9916 FT 15 5803 FT 16 8708 FT 17 5565 PT 18 6142 PT 19 8164 FT 20 4338 FT 21 5833 PT 22 6060 FT 23 2559 PT 24 5569 PT 25 3488 PT 26 7353 PT 27 4990 PT 28 4785 PT 29 3090 FT 30 8087 PT 31 4474 PT 32 4251 PT 33 5324 PT 3123 PT 35 36 37 38 39 Dependents 4 $ 2 $ 4 $ 5 $ 1 5 $ 0 $ 3 $ 4 $ 5 $ 4 $ 1 $ 1 $ 0 $ 2 $ 1 $ 5 $ 4 $ 4 $ 4 $ 1 $ 4 $ 1 $ 5 $ 1 $ 4 $ $ 4 $ 5 $ S Salary 90,212 18,984 42,906 37,191 52,528 17.126 94,101 12,471 67,132 88,604 55,680 98,869 11,994 17,957 66,693 84,432 18,994 65,648 18,835 15,525 15,861 16,173 9,893 17,081 39,364 19,306 9,933 16,707 13,665 7,689 FICA Withholdings FT at least 1 Dependent PT or FT No Dependents 7% 5% Employee ID Dependents Status FT Salary Deduction withholdings O OU 41 42 Insurance Facilities Sales File Home O PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in A1 B Facility Amortization Table Loan Details Loan $325,000.00 Periodic Rate 0.479% # of Payments 60 Principal Interest Paid Repayment Remaining Balance Cumulative Interest Cumulative Principal 1 2 3 4 5 Payment Details 6 Payment $6,245.45 7 APR 5.75 5 8 Years 12 9 Pmts per Year 10 Payment Beginning Payment 11 Number Balance Amount 12 1 13 2 14 3 15 4 16 5 17 6 18 7 19 8 20 9 21 10 22 11 12 24 13 25 14 26 15 27 16 28 17 29 18 19 31 20 32 21 33 22 23 35 24 36 25 Facilities EXI Insurance Sales Type here to search O RE 46 Results ES H G F E C D B 1 March Transactions 2. Search Results Results Category Status Status Trans # 3 4 5 Trans # 6 7 Date 8 3/1/2018 9 3/2/2018 10 3/3/2018 11 3/3/2018 12 3/5/2018 13 3/7/2018 14 3/9/2018 15 3/10/2018 16 3/10/2018 17 3/10/2018 18 3/11/2018 19 3/11/2018 20 3/12/2018 21 3/13/2018 22 3/13/2018 23 3/14/2018 24 3/18/2018 25 3/24/2018 26 3/24/2018 27 3/24/2018 28 3/28/2018 29 3/28/2018 30 3/30/2018 31 3/30/2018 32 3/30/2018 33 34 35 36 Item Quantity Payment Type 3003 8 Credit 5005 4 Financed 5005 1 Credit 5005 10 Financed 1001 6 Credit 5005 5 Credit 1001 5 Credit 5005 3 Credit 2002 1 Financed 4004 4 Financed 2002 9 Financed 3003 8 Credit 1001 9 Financed 2002 8 Financed 2002 9 Credit 3003 9 Credit 3003 1 Credit 4004 1 Credit 4004 3 Financed 4004 10 Cash 2002 10 Credit 1001 9 Cash 3003 8 Credit 4004 6 Financed 2002 9 Cash Amount $4,800 $2,800 $700 $7,000 $1,500 $3,500 $1,250 $2,100 $500 $2,600 $4,500 $4,800 $2,250 $4,000 $4,500 $5,400 $600 $650 $1,950 $6,500 $5,000 $2,250 $4,800 $3,900 $4,500 8 Insurance Facilities Sales Type here to search Extract Warehouse Inventory - Notepad File Edit Format View Help Item Quantity 1001 5000 40000 2002 3500 35000 3003 2500 30000 4004 4000 22000 5005 1000 18000 Total Value B D