You need to provide a rate for each customer based on their credit score. Any user who has a score less than 600 should be denied. a. Display the Approval Form worksheet and create an IF statement in cell B13 to determine if the applicant is eligible for a personal loan. Use the criteria below as part of your logic statement: - Credit Score < 600 = "Denied" - Credit Score >= 600 = "Approved" c. Create a VLOOKUP function in cell B14 that uses the Credit Score in cell B9 to generate a rate based on the array in cells I4:J11. Determine Rate You will calculate the payment quote for each customer. d. Insert a row in the worksheet above row 15 (Payment). e. Type Periodic Rate: in cell A15. f. Calculate the periodic rate of the loan in cell B15. g. Generate the loan payment in cell B16. Determine Rate You will create a working list of all existing accounts that provides the number of days remaining on their loan and an overall look at all accounts maturity dates. h. Display the Existing Accounts worksheet and calculate the number of days remaining for each existing account. i. Calculate the maximum number of Days Remaining for all existing accounts in cell C17. j. Calculate the minimum number of Days Remaining for all existing accounts in cell C18. k. Calculate the average number of Days Remaining for all existing accounts in cell C19.
Added by Julia M.
Step 1
Step 1: Open the Approval Form worksheet in your spreadsheet application. Show more…
Show all steps
Your feedback will help us improve your experience
Supreeta N and 92 other AP CS educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Key Concepts
Recommended Videos
You want to purchase a new car. Your current car is worth $3,500 as a trade-in. The base sticker price on the car you want is $24,595. The first financing offer from the dealer is 1.9% APR for 24 months, with no cash back. The original financing offer terms are listed in cells A3:B9 on the Car Loan worksheet. Cell B10 contains a formula with a PMT function to calculate the monthly payment. Use what-if analysis tools to compare financing options. Create scenarios to compare financing options for the car loan. (a) Create names for the following cells on the Car Loan worksheet to make the scenarios easier to follow: Name cell B3: Price Name cell B4: TradeIn Name cell B5: CashBack Name cell B6: Loan Name cell B8: Months Name cell B9: APR Name cell B10: Payment Name cell B12: TotalCost (b) Create a scenario named Original Financing to save the original values in cells B3, B5, B8, and B9. (c) Create a second scenario named Intermediate Car. Change the values as follows: Cell B3: 33999 Cell B5: 1500 Cell B8: 36 Cell B9: 2.4% (d) Show the Intermediate Car scenario to verify your data entry. (e) Create a third scenario named Luxury Car. Change the values as follows: Cell B3: 62700 Cell B5: 2500 Cell B8: 60 Cell B9: 3.9% (f) Show the Luxury Car scenario to verify your data entry. (2) Create a scenario summary report to compare the results for cells B10 (the monthly payment) and B12 (the total cost of the car including interest). B6 = B3 - B4 - B5 A B New Car Loan 2 Loan Inputs 3 Sticker price $ 24,595 4 Trade in $ 3,500 5 Cash back offer $ 6 Loan amount $ 21,095 7 8 Loan term (months) 24 9 Loan interest (APR) 1.90% 10 Loan payment $ 896.46 11 12 Total cost of the car $ 21,515.04 13 14 15 16 17 18 c D E F G H
Akash M.
1. 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. 2. 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. 3. 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. 4. 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. 5. 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. 6. 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. 7. 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.
1. Open the Stefanek workbook located in the Excel4 > Case1 folder included with your Data Files, and then save the workbook as Stefanek Budget in the location specified by your instructor. 2. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4. 3. In the Budget worksheet, in the range B3:B8, enter the parameters for a $285,000 mortgage at 4.3% annual interest paid back over 25 years. Calculate the interest rate per month and the total number of payments. 4. In cell B10, calculate the amount of the monthly payment needed to pay back the mortgage. 5. In the range C15:N15, calculate the total income from Edmund and Lydia's monthly salaries. 6. In the range C22:N22, use an absolute reference to insert the monthly mortgage payment you calculated in cell B10. 7. In the range C24:N24, calculate Edmund and Lydia's total expenses per month. 8. In the range C25:N25, calculate the couple's monthly net income by adding their income and their expenses. (Note that expenses are entered as negative values.) 9. In the range C28:C40, calculate the averages for the income and expenses from the 12-month budget. 10. In the range C28:C40, add data bars to the values. Note that negative data bars are displayed to the left of the center point in the cell, whereas positive data bars are displayed to the right. 11. In the range D28:D40, insert line sparklines using the values from the range C13:N25 to show how the different budget entries change throughout the year. 12. Create a pie chart of the income values in the range B28:C29 to show the breakdown of the family income between Edmund and Lydia. Resize the chart to cover the range E27:I40. Change the chart title to Income and apply the Style3 chart style to the chart. 13. Create a pie chart of the expenses values in the range B31:C38. Resize the chart to cover the range J27:N40. Change the chart title to Expenses and apply the Style3 chart style to the chart. Change the position of the data labels to data callouts. If any data labels appear to overlap, select one of the overlapping data labels, and drag it to another position. 14. Save the workbook, and then close it.
Shyam P.
Recommended Textbooks
Computer Science and Information Technology
Introduction to Programming Using Python
Computer Science - An Overview
Transcript
Watch the video solution with this free unlock.
EMAIL
PASSWORD