Module 4 CIS271 Fix 1

xlsx

School

West Liberty University *

*We aren’t endorsed by this school

Course

271

Subject

Computer Science

Date

Feb 20, 2024

Type

xlsx

Pages

Uploaded by DukeEel4043

Shelly Cashman Excel 2019 | Module 4: SAM Project 1a Zachary Jordan GE ver. 17.2.0-rc0000 1. 1/1 Delete a defined name. Update the defined name for a cell. 2. 1/1 Create a formula. 3. 0/1 Create defined names for a cell. In the Loan Calculator worksheet, cell D4 should have the defined name "Rate". Create defined names for a cell. In the Loan Calculator worksheet, cell D5 should have the defined name "Term_in_Months". Create defined names for a cell. In the Loan Calculator worksheet, cell D6 should have the defined name "Monthly_Payment". Create defined names for a cell. In the Loan Calculator worksheet, cell D7 should have the defined name "Total_Interest". Create defined names for a cell. In the Loan Calculator worksheet, cell D8 should have the defined name "Total_Cost". 4. 0/1 Create a formula using a function. In the Loan Calculator worksheet, the formula in cell D6 should use the named range "Rate". 5. 0/1 Create a formula. In the Loan Calculator worksheet, the formula in cell D7 should use the named range "Monthly_Payment". 6. 0/1 Create a formula. In the Loan Calculator worksheet, the formula in cell D8 should use the named range "Total_Interest". 7. 0/1 Create a formula. In the Loan Calculator worksheet, the formula in cell A12 should be a reference to the named range "Monthly_Payment". Create a two-variable data table. 8. 1/1 Apply a conditional formatting rule. Apply a conditional formatting rule. 9. 0/1 Add a border to a range. In the Loan Calculator worksheet, cells in the range A10:D25 should be formatted using a Gold, Accent 1 outside cell border. 10. 0/1 Create a formula using a function. In the Loan Calculator worksheet, the formula in cell G10 should contain the PMT function. 11. 1/1 Create a formula using a function. 12. 0/1 Create a formula using a function. In the Loan Calculator worksheet, the formula in cell I11 should contain the FV function. 13. 2/2 Add print titles to a worksheet. Set the print area for a worksheet. 14. 2/2 Hide a worksheet. 15. 2/2 Update a value in a cell. Enter a formula in a cell. 16. 2/2 Add a border to a range. SUBMISSION #1 | SCORE IS: 12 OUT OF 20 Liam Richardson is the business manager for the Smith & Lyngate Insurance agencies in the state of Maryland. Liam is interested in increasing the number of agents in Baltimore and plans to buy an office building for the new operation. He has asked for your help in creating a loan analysis that summarizes information about the loans to cover the cost of the building. Go to the Loan Calculator worksheet. The cells in the range B6:B8 have defined names, but one is incomplete and could be confusing. Cell A2 also has a defined name, which is unnecessary for a cell that will not be used in a formula. Update the defined names in the worksheet as follows: a. Delete the Loan_Calculator defined name. b. For cell B8, edit the defined name to use Loan_Amount as the name. In cell B8, calculate the loan amount by entering a formula without using a function that subtracts the Down_Payment from the Price . Liam also wants to use defined names in other calculations to help him interpret the formulas. In the range D4:D8, create defined names based on the values in the range C4:C8. Liam needs to calculate the monthly payment for a loan to purchase the Charles Street property. Calculate the payment as follows: a. In cell D6, start to enter a formula using the PMT function. b. For the rate argument, divide the Rate by 12 to use the monthly interest rate. c. For the nper argument, use the Term_in_Months to specify the number of periods. d. For the pv argument, use the Loan_Amount to include the present value. e. Insert a negative sign ( - ) after the equal sign in the formula to display the result as a positive amount. In cell D7, enter a formula without using a function that multiples the Monthly_Payment by the Term_in_Months and then subtracts the Loan_Amount from the result to determine the total interest. In cell D8, enter a formula without using a function that adds the Price to the Total_Interest to determine the total cost. Liam wants to compare monthly payments for interest rates that vary from 3.95% to 5.15% and for terms of 120, 180, and 240 months. He has already set up the structure for a data table in the range A12:D25. Create a two-variable data table as follows to provide the comparison that Liam requests: a. In cell A12, enter a formula without using a function that references the Monthly_Payment amount because Liam wants to compare the monthly payments. b. Based on the range A12:D25, create a two-variable data table that uses the term in months (cell D5) as the row input cell and the rate (cell D4) as the column input cell. In the list of interest rates (range A13:A25), create a Conditional Formatting Highlight Cells Rule to highlight the listed rate that matches the rate for the Charles Street property (cell D4 ) in Light Red Fill with Dark Red Text . Change the color of the left, right, and bottom borders of the range A10:D25 to Gold, Accent 1 to match the other outside borders in the worksheet. Liam has three other options for purchasing the Charles Street property. In the first scenario, he could borrow additional money to fund a renovation of the building. He wants to determine the monthly payment for the first scenario. In cell G10, insert a formula using the PMT function using the monthly interest rate (cell G6 ), the loan period in months (cell G8 ), and the loan amount (cell G4 ) to calculate the monthly payment for the renovation scenario. In the second scenario, Liam could pay back the loan in 20 years instead of 15 and reduce his monthly payments to $6,000 with an annual interest rate of 4.5%. He wants to know the loan amount he should request with those conditions. In cell H4, insert a formula using the PV function and the monthly interest rate (cell H6 ), the loan period in months (cell H8 ), and the monthly payment (cell H10 ) to calculate the loan amount for the 20-year scenario. In the third scenario, Liam could pay back the loan for eight years with a monthly payment of $8,000 and then renegotiate better terms. He wants to know the amount remaining on the loan after eight years or the future value of the loan. In cell I11, insert a formula using the FV function and the rate (cell I6 ), the number of periods (cell I8 ), the monthly payment (cell I10 ), and the loan amount (cell I4 ) to calculate the future value of the loan for the eight-year scenario. Liam plans to print parts of the Loan Calculator workbook. Prepare for printing as follows: a. Set row 3 as the print titles for the worksheet. b. Set the range F3:I11 as the print area. Hide the Properties worksheet, which contains data Liam wants to keep private. Go to the Equipment Loan worksheet, which contains details about a loan the company used for computer network equipment. The worksheet contains two errors. Make sure Excel is set to check all types of errors, and then resolve the ones on the Equipment Loan worksheet as follows: a. Display the possible errors and solutions for cell C2, and then select convert to number. b. Trace the precedents to the formula in cell I9, which should multiply the scheduled payment amount by the number of scheduled payments. Correct the error. Draw attention to the optional extra payments in the range C10:E10 by adding a thick outside border using the Orange, Accent 3 shape outline color.

Author: Note: Do

Smith & Lyngate Insurance CREATE A LOAN ANALYSIS Zachary Jordan Shelly Cashman Excel 2019 | Module 4: SAM Project 1a o not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SA website.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Scenarios Renovation 20 Years 8 Years Loan Amount $ 1,020,000 $ 968,938 $ 920,000 Annual Interest Rate 4.45% 4.25% 4.45% Monthly Interest Rate 0.37% 0.35% 0.37% Loan Period in Years 15 20 8 Loan Period in Months 180 240 96 Start Date 1/3/2022 1/3/2022 1/3/2022 Monthly Payment $ (6,000) $ (8,000) Future Value n/a n/a #NAME?

Page 6 of 6 Equipment Loan Year: 2020 Loan Details Loan Summary Loan amount $15,000.00 Scheduled payment $1,278.28 Annual interest rate 4.15% Scheduled number of payments 12 Loan period in years 1 Actual number of payments 12 Number of payments per year 12 Total early payments $1,100.00 Start date of loan 2/26/2020 Total interest $316.23 Total paid $15,339.32 Optional extra payments $100.00 Pmt No Payment Date Beg Balance Payment Amt Extra Payment Total Payment Principal Interest Ending Balance Cumulative Interest 1 2/26/2020 $15,000.00 $1,278.28 $100.00 $1,378.28 $1,326.40 $51.88 $13,673.60 $51.88 2 3/26/2020 $13,673.60 $1,278.28 $100.00 $1,378.28 $1,330.99 $47.29 $12,342.61 $99.16 3 4/26/2020 $12,342.61 $1,278.28 $100.00 $1,378.28 $1,335.59 $42.68 $11,007.02 $141.85 4 5/26/2020 $11,007.02 $1,278.28 $100.00 $1,378.28 $1,340.21 $38.07 $9,666.81 $179.91 5 6/26/2020 $9,666.81 $1,278.28 $100.00 $1,378.28 $1,344.85 $33.43 $8,321.96 $213.34 6 7/26/2020 $8,321.96 $1,278.28 $100.00 $1,378.28 $1,349.50 $28.78 $6,972.46 $242.12 7 8/26/2020 $6,972.46 $1,278.28 $100.00 $1,378.28 $1,354.16 $24.11 $5,618.30 $266.24 8 9/26/2020 $5,618.30 $1,278.28 $100.00 $1,378.28 $1,358.85 $19.43 $4,259.45 $285.67 9 10/26/2020 $4,259.45 $1,278.28 $100.00 $1,378.28 $1,363.55 $14.73 $2,895.91 $300.40 10 11/26/2020 $2,895.91 $1,278.28 $100.00 $1,378.28 $1,368.26 $10.02 $1,527.65 $310.41 11 12/26/2020 $1,527.65 $1,278.28 $100.00 $1,378.28 $1,372.99 $5.28 $154.65 $315.70 12 1/26/2021 $154.65 $1,278.28 $0.00 $154.65 $154.12 $0.53 $0.00 $316.23 2/1/2020 3/1/2020 4/1/2020 5/1/2020 6/1/2020 7/1/2020 8/1/2020 9/1/2020 10/1/2020 11/1/2020 12/1/2020 1/1/2021 $0.00 $2,000.00 $4,000.00 $6,000.00 $8,000.00 $10,000.00 $12,000.00 $14,000.00 $16,000.00 $0.00 $50.00 $100.00 $150.00 $200.00 $250.00 $300.00 $350.00 Ending Balance and Cumulative Interest Ending Balance Cumulative Interest

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Module 4 CIS271 Fix 1

Related Documents