EXCEL SPREADSHEET

COMPLETE LAB 1 FROM SCRATCH IN EXCEL 11/15/2015 Microsoft® Excel® 2013: Comprehensive, 1e PRINTED BY: [email protected]. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. T_L3ILI2LIL14ALk 2 vi rti EX 612 Excel chapter 9 formula AUditielg, Data validatio.n., and Oompl.ax problem Scolw[rig Ana time, eorruck. I enp rowel emir:mired 1. Correct a. The formula for the ending balance in column H does not properly include the annual return on investment listed in column G. Update the formula correctly to include an annual rate of return. b. Update the workbook to reflect the client's planned contributions, withdrawals, and her goals for interest earned outlined earlier. c. Start Solver to view the Solver parameters for the worLheet. The Solver target is set to maximize the first annual contribution in cell E4. Change the Solver target to set cell F119 to 200,000 to reflect the client's stated goal. The changing cells should be set to change cell E4, rather than cell D4. d. The constraints set for Solver are not necessary. Delete these constraints. e. The solving method should be Simplex LP. Tap or click the Solve button in the Solver Parameters dialog box to run Solver, Comte an answer report. Name the answer report Answer Report - Correct. 2. IrriprOme a. The client is interested in how she would have to change her saving plan in order to ensure that at the end of year 22 there is a $50,000 balance to provide support for graduate school, if either of her children choose to pursue graduate degrees. She will consider increasing her initial yearly contribution to a maximum of $5,400, but would prefer not to increase her initial deposit of $2.500 unless necessary. Use Solver to provide the client with an answer report outlining how her annual contribution and initial deposit would need to change in order to provide that opportunity. Name the answer report Answer Report - Improve. b. If requested by your instructor, change the sheet tab currently named College Savings to <yourname> College Savings, replacing <yourname> with your name. c. Save the workbook. Submit the revised document in the format specified by your instructor. 3. 0 Can you think of another tool you could use to calculate the solution found in Step le? Explain why you can use this tool for the situation. In the Labs Design and/or create a workbook using the guidelines, concepts, and skills presented in this chapter. Labs 1 and 2, which increase in difficulty, require you to create solutions based on what you learned in the chapter, Lab 3 requires you to create a solution, which uses cloud and web technologies, by learning and investigating on your own from general guidance. Lab 1: Creating a PivotTable Report and PivotChart Report Note: To complete this assignment, you will be required to use the Data Files for Students. Visit www,cengage.corn/ctfstudentdownload for detailed instructions or contact your instructor for information about accessing the required files, Problem: You want to select the best payback options for a debt consolidation loan you will be getting. You have created a data table to examine the effects of interest rates from 7.0% to 10.00% for a loan. You want to use Goal Seek to determine how many months it will take to pay off a loan of $22,000.00 with an interest rate of 8.5% using different monthly payment goals. NEna Capp Liam, J 5115. Itmaryrd. litry Dr km miss!. masd, rr dmplicmmL m .4 k,. ioprL bs.storhms righk, amc Lhanl armkol crmy 63.. FTRE irmsdar drepwk ...FF.!. E.101.1 re 4, has deem, ENE as, a.pret,ed coleus ...A came,. Rai art &rt. Ian rpm rel., Coy... YQ re..di MrljhlM rernoe 4141reoul ontm Y GII}bIfrt RiYFol k91 IIQItl F11.621.1 ref re h. http://jigsaw.vitalsource.com/api/v0/books/9781285953151/print?from=EX%20612&to=EX%20613 1/2 Interest Rate Schedule Consolidation Loan Payment Calculator Monthly Payment Total Cost Total Interest Loan amount $22,000.00 Loan Payoff Options Monthly Payment Months Total Interest Paid Interest $542.26 $26,028.61 $4,028.61 Interest rate 8.50% Original loan terms 7.00% 526.82 25,287.23 3,287.23 Months 48 Reduce monthly payment to $450 7.50% 531.94 25,532.92 3,532.92 Monthly payment $542.26 Reduce monthly payment to $350 8.00% 537.08 25,780.05 3,780.05 Total Interest $4,028.61 Reduce length of loan to 42 months 8.50% 542.26 26,028.61 4,028.61 Reduce total interest paid to $4500 9.00% 547.47 26,278.60 4,278.60 9.50% 552.71 26,530.03 4,530.03 10.00% 557.98 26,782.89 4,782.89