Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Excel document edit

19/10/2020 Client: ali Deadline: 10 Days

 



  1. Open the SierraPacific-02.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.

  2. Set range names for the workbook.

    1. Select the Student Loan sheet, and select cells B5:C8.

    2. Click the Create from Selection button [Formulas tab, Defined Names group].

    3. Verify that the Left column box in the Create Names from Selection dialog box is selected.

    4. Deselect the Top row box if it is checked and click OK.

    5. Select cells E5:F7. Repeat steps a−d to create range names.

    6. Click the Name Manager button [Formulas tab, Defined Names group] to view the names in the Name Manager dialog box (Figure 2-90). Notice that the cell references are absolute.Name Manager dialog boxFigure 2-90 Name Manager dialog box

    7. Click Close.



  3. Enter a PMT function.

    1. Select C8.

    2. Click the Financial button [Formulas tab, Function Library group] and select PMT.

    3. Click the Rate box and click cell C7. The range name Rate is substituted and is an absolute reference.

    4. Type /12 immediately after Rate to divide by 12 for monthly payments.

    5. Click the Nper box and click cell C6. The substituted range name is Loan_Term.

    6. Type *12 after Loan_Term to multiply by 12.

    7. Click the Pv box and type a minus sign (-) to set the argument as a negative amount.

    8. Click cell C5 (Loan_Amount) for the pv argument. A negative loan amount reflects the lender’s perspective, since the money is paid out now (Figure 2-91).The formula is =PMT(Rate/12,Loan_Term*12,-Loan_Amount)Figure 2-91 Pv argument is negative in the PMT function

    9. Leave the Fv and Type boxes empty.

    10. Click OK. The payment for a loan at this rate is $186.43, shown as a positive value.

    11. Verify or format cell C8 as Accounting Number Format to match cell C5.



  4. Create a total interest formula.

    1. Click cell F5 (Total_Interest). This value is calculated by multiplying the monthly payment by the total number of payments to determine total outlay. From this amount, you subtract the loan amount.

    2. Type = and click cell C8 (the Payment).

    3. Type * to multiply and click cell C6 (Loan_Term).

    4. Type *12 to multiply by 12 for monthly payments. Values typed in a formula are constants and are absolute references.

    5. Type immediately after *12 to subtract.

    6. Click cell C5 (the Loan_Amount). The formula is Payment * Loan_Term * 12 – Loan_Amount. Parentheses are not required, because the multiplications are done from left to right, followed by the subtraction (Figure 2-92).Parentheses are not necessary in the formulaFigure 2-92 Left-to-right operations

    7. Press Enter. The result is $1,185.81.



  5. Create the total principal formula and the total loan cost.

    1. Select cell F6 (Total_Principal). This value is calculated by multiplying the monthly payment by the total number of payments. From this amount, subtract the total interest.

    2. Type = and click cell C8 (the Payment).

    3. Type * to multiply and click cell C6 (Loan_Term).

    4. Type *12 to multiply by 12 for monthly payments.

    5. Type immediately after *12 to subtract.

    6. Click cell F5 (the Total_Interest). The formula is Payment * Loan_Term * 12 – Total_Interest.

    7. Press Enter. Total principal is the amount of the loan.

    8. Click cell F7, the Total_Cost of the loan. This is the total principal plus the total interest.

    9. Type =, click cell F5, type +, click cell F6, and then press Enter.



  6. Set order of mathematical operations to build an amortization schedule.

    1. Click cell B13. The beginning balance is the loan amount.

    2. Type =, click cell C5, and press Enter.

    3. Format the value as Accounting Number Format.

    4. Select cell C13. The interest for each payment is calculated by multiplying the balance in column B by the rate divided by 12.

    5. Type = and click cell B13.

    6. Type *( and click cell C7.

    7. Type /12). Parentheses are necessary so that the division is done first (Figure 2-93).The formula is =B13*(Rate/12)Figure 2-93 The interest formula

    8. Press Enter and format the results (37.5) as Accounting Number Format.

    9. Select cell D13. The portion of the payment that is applied to the principal is calculated by subtracting the interest portion from the payment.

    10. Type =, click cell C8 (the Payment).

    11. Type -, click cell C13, and press Enter. From the first month’s payment, $148.93 is applied to the principal and $37.50 is interest.

    12. Click cell E13. The total payment is the interest portion plus the principal portion.

    13. Type =, click cell C13, type +, click cell D13, and then press Enter. The value matches the amount in cell C8.

    14. Select cell F13. The ending balance is the beginning balance minus the principal payment. The interest is part of the cost of the loan.

    15. Type =, click cell B13, type -, click cell D13, and then press Enter. The ending balance is $9,851.07.




    16. The image includes rows 13 through 28 and then rows 60 through72 after you complete Step 7g.Formulas in cells B13:F13B13=Loan_AmountC13=B13*(Rate/12)D13=Payment-C13E13=C13+D13F13=B13-D13



  7. Fill data and copy formulas.

    1. Select cells A13:A14. This is a series with an increment of 1.

    2. Drag the Fill pointer to reach cell A72. This sets 60 payments for a five-year loan term.

    3. Select cell B14. The beginning balance for the second payment is the ending balance for the first payment.

    4. Type =, click cell F13, and press Enter.

    5. Double-click the Fill pointer for cell B14 to fill the formula down to row 72. The results are zero (displayed as a hyphen in Accounting Number Format) until the rest of the schedule is complete.

    6. Select cells C13:F13.

    7. Double-click the Fill pointer at cell F13. All of the formulas are filled (copied) to row 72 (Figure 2-94).Formulas copied to row 72 with a zero ending balanceFigure 2-94 Formulas copied down columns

    8. Scroll to see the values in row 72. The loan balance reaches 0.

    9. Press Ctrl+Home.



  8. Build a multiplication formula.

    1. Click the Fees & Credit sheet tab and select cell F7. Credit hours times number of sections times the fee calculates the total fees from a course.

    2. Type =, click cell C7, type *, click cell D7, type *, click cell E7, and then press Enter. No parentheses are necessary because multiplication is done in left to right order (Figure 2-95).The formula is =C7*D7*E7Figure 2-95 Formula to calculate total fees per course

    3. Double-click the Fill pointer for cell F7 to copy the formula.

    4. Verify that cells F7:F18 are Currency format. Set a single bottom border for cell F18.



  9. Use SUMIF to calculate fees by department.

    1. Select cell C26.

    2. Click the Math & Trig button [Formulas tab, Function Library group] and select SUMIF.

    3. Click the Range box and select cells B7:B18. This range will be matched against the criteria.

    4. Press F4 (FN+F4) to make the reference absolute.

    5. Click the Criteria box and select cell B26.

    6. Click the Sum_range box, select cells F7:F18, and press F4 (FN+F4).

    7. Click OK. Total fees for the Biology department are 13350 (Figure 2-96).The formula is =SUMIF($B$B7:$A$18,B26,$F$7:$F$18)Figure 2-96 Function Arguments dialog box for SUMIF



  10. Copy a SUMIF function.

    1. Click cell C26 and drag its Fill pointer to copy the formula to cells C27:C29 without formatting to preserve the borders (Figure 2-97).The AutoFill Options button has an option to fill without formatting.Figure 2-97 Formula is copied without formatting

    2. Format cells C26:C29 as Currency.



  11. Use SUMPRODUCT and trace an error.

    1. Select cell D26 and click the Formulas tab.

    2. Click the Math & Trig button in the Function Library group and select SUMPRODUCT.

    3. Click the Array1 box and select cells C7:C9, credit hours for courses in the Biology Department.

    4. Click the Array2 box and select cells D7:D9, the number of sections for the Biology Department.

    5. Click OK. The Biology Department offered 98 total credit hours.

    6. Click cell D26 and point to its Trace Error button. The formula omits adjacent cells in the worksheet but it is correct.

    7. Click the Trace Error button and select Ignore Error.



  12. Copy and edit SUMPRODUCT.

    1. Click cell D26 and drag its Fill pointer to copy the formula to cells D27:D29 without formatting to preserve the borders.

    2. Click cell D27 and click the Insert Function button in the Formula bar.

    3. Select and highlight the range in the Array1 box and select cells C10:C12. The range you select replaces the range in the dialog box (Figure 2-98).The formula is now =SUMPRODUCT(C10:C12,D10:D12)Figure 2-98 Replace the ArrayN arguments

    4. Select the range in the Array2 box and select cells D10:D12.

    5. Click OK.

    6. Edit and complete the formulas in cells D28:D29 and ignore errors.



  13. Insert the current date as a function.

    1. Select cell F20.

    2. Type =to and press Tab to select the function.

    3. Press Enter.

    4. Press Ctrl+Home.



  14. Paste range names.

    1. Click the New sheet button in the sheet tab area.

    2. Name the new sheet Range Names.

    3. Press F3 (FN+F3) to open the Paste Name dialog box.

    4. Click the Paste List button.

    5. AutoFit columns A:B.



  15. Save and close the workbook (Figure 2-99).

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Buy Coursework Help
Online Assignment Help
Quality Homework Helper
Top Grade Essay
University Coursework Help
Top Writing Guru
Writer Writer Name Offer Chat
Buy Coursework Help

ONLINE

Buy Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$232 Chat With Writer
Online Assignment Help

ONLINE

Online Assignment Help

Hi dear, I am ready to do your homework in a reasonable price.

$225 Chat With Writer
Quality Homework Helper

ONLINE

Quality Homework Helper

Hi dear, I am ready to do your homework in a reasonable price.

$232 Chat With Writer
Top Grade Essay

ONLINE

Top Grade Essay

Working on this platform from a couple of time with exposure of dynamic writing skills gathered with years experience on different other websites.

$232 Chat With Writer
University Coursework Help

ONLINE

University Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$232 Chat With Writer
Top Writing Guru

ONLINE

Top Writing Guru

I am an Academic writer with 10 years of experience. As an Academic writer, my aim is to generate unique content without Plagiarism as per the client’s requirements.

$230 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Ibp conscientious eating - Project Plan Components IVR - James watson and francis crick wiki - North tees hospital wards - OS dis 6 - Shin etsu microsi g751 - California state university dominguez hills occupational therapy - Umuc europe phone number - Using your knowledge exercise 17 answers - Critical analysis template - Reset hudl 2 password - For women of afghanistan by sheema kalbasi - The advent of dvds has virtually demolished the market for videocassettes. this is an example of: - Las vegas sands swot analysis - Organisational behaviour book pdf free - Cacl2 lewis dot structure - Metarteriole and thoroughfare channel - American History 1302 - Related Diversification - The fifth discipline discussion questions - Pru co uk mypru - Three assignments and due dates - Molar mass of cu no3 2 - Advertising the magic system raymond williams - Types of strategies ppt - 6lb 3oz in kg - English - Nursing Discussion Question - Dowling festing and engle 2013 - Reply to my peers - Java program to calculate commission - Paper towns chapter 3 summary - Elements named after places - Criminal Justice - Weekly Application Post - Week 3 - Current devices of respiratory physiotherapy - Dr andrew bullock cardiologist - Rodney stark sociology 10th edition ebook - Which system is not part of the expenditure cycle - Amoeba sisters video recap asexual and sexual reproduction - Pontiac two speeches 1762 and 1763 - Information Governance discussion 5 - What authoritative literature addresses comprehensive income - Late adulthood interview paper - Concrete jungle where dreams are made of meaning - Are the major counseling theories culture bound explain - 5 principles of differentiated instruction - Final Assignment- RETAKE Anatomy and Physiology Revealed Quizzes - Essay 2 to 3 pages - Www veltechuniv edu in fee payment - The coral island sparknotes - Automotive assignment AURLTB003 - Definition essay ( due 10/19/20 by 11:59) - Illusions richard bach chapter summaries - How to find the stem of a latin noun - Seven mysteries of the kingdom of heaven - Lutron seetouch keypad spec - The marrow thieves essay topics - Coca cola target segment - Discussion questions on social media - You want custom embroidered hats UK? - Discussion Topic - Bill nye greatest discoveries plate tectonics - After you my dear alphonse analysis - Warren buffett 2005 case study - Assessment 5 - 20 meter sprint test normative data - Ecology short answer questions - Night study guide questions and answers - Airbus h135 operating cost - The thinker's guide to critical thinking - Combination of question mark and exclamation point - Article - How do you score 2 points in football - Mid latitude cyclone worksheet answers - Using social media to express, promote or spread anger and hatred. - Even though parts of the title sound the same as units that you have previously completed, there is significant difference i - Leslie matrix in r - Straw bridge challenge instructions - 1. Brochure (2-3 fold front and back) 2. Memo Format (1 page) - Robin hood mission vision values goals - 0580 41 m j 17 ms - The second thinksafe step is - Razor over comb definition - Ethics with Drug Testing in workplace - The city of portland is in oregon counterexample - Lab report on ohm's law - Multilit word attack skills recording booklet - Elegant decor company's management is trying to decide - Pr 7 3b bank reconciliation and entries - Copper ii nitrate and sodium sulfide - At the border 1979 poem analysis - WITBANK ABORTION CLINIC +27717852514 ABORTION PILLS FOR SALE IN PRETORIA, MAMELODI, BENONI, WITBANK, PRETORIA WEST, ARCADIA, WITBANK, - True false making data secure means keeping it secret - Wk4 DQ1 Discussion Question 1 – CLO 6 - Which revision corrects the faulty coordination in the sentence - Coherent theory of truth - Summary of the tempest by william shakespeare - Olivier jacque morgan stanley - Engineering project management uts