Exp19_Excel _AppCapstone_ Compassessment_ Manufacturing Project.

please answer questions 11-14

12 Create the following three scenarios using Scenario Manager. The scenarios should change the cells B7 BB, and E6 Good B7.0325 B85 E6275000 Most Likely B7 057 B85 E6 - 312227.32 Bad B7 = .0700 B8 = 3 E6 = 350000 Create a Scenario Summary Report based on the value in cell B6. Format the new report appropriately 13 Ensure that the Facilities worksheet is active. Enter a reference to the beginning loan balance in cell B12 and enter a reference to the payment amount in cell C12. 14 Enter a function in cell D12, based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. 15 Enter a function in cell E12, based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 16 Enter a formula in cell F12 to calculate the remaining balance after the current payment The remaining balance is calculated by subtracting the principal payment from the balance in column B 17 Enter a function in cell G12, based on the payment and loan details, that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 18 Enter a function in cell H12, based on the payment and loan details, that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 19 Enter a reference to the remaining balance of payment 1 in cell B13. Use the fill handle to copy the functions created in the prior steps down to complete the amortization table. 20 Ensure the sales worksheet is active Enter a function in cell B8 to create a custom transaction number. The transaction number should be comprised of the item number listed in cell C combined with the quantity in cell D8 and the first initial of the payment type in cell E8 Use Auto Fill to copy the function down, completing the data in column B 21 Enter a nestod function cell GB that displays the word Flag of the Payment Type is Credit and the Amount is greater than or equal to $4000. Otherwise, the function will display a blank cell Use Auto Fifo copy the function down, completing the data in column G 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 G H 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 12 Create the following three scenarios using Scenario Manager. The scenarios should change the cells B7 BB, and E6 Good B7.0325 B85 E6275000 Most Likely B7 057 B85 E6 - 312227.32 Bad B7 = .0700 B8 = 3 E6 = 350000 Create a Scenario Summary Report based on the value in cell B6. Format the new report appropriately 13 Ensure that the Facilities worksheet is active. Enter a reference to the beginning loan balance in cell B12 and enter a reference to the payment amount in cell C12. 14 Enter a function in cell D12, based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. 15 Enter a function in cell E12, based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 16 Enter a formula in cell F12 to calculate the remaining balance after the current payment The remaining balance is calculated by subtracting the principal payment from the balance in column B 17 Enter a function in cell G12, based on the payment and loan details, that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 18 Enter a function in cell H12, based on the payment and loan details, that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references 19 Enter a reference to the remaining balance of payment 1 in cell B13. Use the fill handle to copy the functions created in the prior steps down to complete the amortization table. 20 Ensure the sales worksheet is active Enter a function in cell B8 to create a custom transaction number. The transaction number should be comprised of the item number listed in cell C combined with the quantity in cell D8 and the first initial of the payment type in cell E8 Use Auto Fill to copy the function down, completing the data in column B 21 Enter a nestod function cell GB that displays the word Flag of the Payment Type is Credit and the Amount is greater than or equal to $4000. Otherwise, the function will display a blank cell Use Auto Fifo copy the function down, completing the data in column G 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 G H 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