create a pivot table general guidance. Lab 1: Creating a PivotTable Report and PivotChart Report Note: To
Fantastic news! We've Found the answer you've been seeking!
Question:
create a pivot table
Transcribed Image Text:
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.com/ct/studentdownload 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. 14 Instructions: Perform the following tasks. 1. Open the workbook Lab 9-1 Consolidation Loan from the Data Files for Students, and then save the workbook as Lab 9-1 Consolidation Loan Complete. BHDLC LIN 2. Enter a loan amount of 22,000 (cell G4), interest rate of 8.5% (cell G5), and months of 48 (cell G6) in the payment calculator portion of the workbook (Figure 9-92). X4 . Interest 7.00% 7.50% 8.005 4.50% PART FAGE LAYOUT Trabhat MS BIN 9.00% 9.501 10.00% X Interest Rate Schedule FORMULAS K RA F*CK Formula Auditing, Data Validation, and Complex Problem Solving Excel Chapter 9 EX 613 Monthly Payment Total Cost 5542 26 $26.028.61 52.82 531.94 25,532.32 537,08 25,780.05 Total Interest Loan amount interest rate $4,028.61 3,217.23 Months 3,532.92 wonthly payment 3,780.05 Total interest 4,028.61 542.26 26,028.61 547,47 26,278.40 4,278.60 552.71 24,530.03 4,530.03 Calidation Loan Caloter O VEW DW Text Come VV dand Consolidation Loan Payment Calculator C Figure 9-92 Sy $22,000,00 8.501 40 Paduca monthly payment to 5430 15422ce morply peynt to 550 $4,028 81 aduce length Dette format 4144 H THI 14 Payment and rest Paid Sign - R P 3. Enter the Monthly Payment, Months, and Total Interest Paid from the Consolidation Loan Payment Calculator in the Original loan terms row of the Loan Payoff Options table found in the range 14:19. 4. Select cell G7 and then display the Goal Seek dialog box by tapping or clicking the What-If Analysis button (DATA tab | Data Tools group) and then tapping or clicking Goal Seek on the What-If Analysis menu. Determine how many months you need to pay off the loan if you pay $450.00 per month by setting cell G7 to the value 450 and changing cell G6. Update the Loan Payoff Options table with the results of this Goal Seek. 5. Use Goal Seek to determine how many months you need to pay off the loan if you pay $350.00 per month. Update the Loan Payoff Options table with the results of this Goal Seek. 6. Reduce the loan term to 42 months and record the monthly payment and total interest paid in the Loan Payoff Options table. 7. If requested by your instructor, enter your name in cell 14, before the text Loan Payoff Options. 8. Save the workbook. Submit the revised workbook as specified by your instructor. 9. Use Goal Seek to determine the Monthly Payment and Months for the final option in the Loan Payoff Options table, row 9. Which variable did you choose to change? What other variable could you change to reach a Total Interest figure of $4,500? STUDENT ASSIGNMENTS Excel Chapter 9 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.com/ct/studentdownload 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. 14 Instructions: Perform the following tasks. 1. Open the workbook Lab 9-1 Consolidation Loan from the Data Files for Students, and then save the workbook as Lab 9-1 Consolidation Loan Complete. BHDLC LIN 2. Enter a loan amount of 22,000 (cell G4), interest rate of 8.5% (cell G5), and months of 48 (cell G6) in the payment calculator portion of the workbook (Figure 9-92). X4 . Interest 7.00% 7.50% 8.005 4.50% PART FAGE LAYOUT Trabhat MS BIN 9.00% 9.501 10.00% X Interest Rate Schedule FORMULAS K RA F*CK Formula Auditing, Data Validation, and Complex Problem Solving Excel Chapter 9 EX 613 Monthly Payment Total Cost 5542 26 $26.028.61 52.82 531.94 25,532.32 537,08 25,780.05 Total Interest Loan amount interest rate $4,028.61 3,217.23 Months 3,532.92 wonthly payment 3,780.05 Total interest 4,028.61 542.26 26,028.61 547,47 26,278.40 4,278.60 552.71 24,530.03 4,530.03 Calidation Loan Caloter O VEW DW Text Come VV dand Consolidation Loan Payment Calculator C Figure 9-92 Sy $22,000,00 8.501 40 Paduca monthly payment to 5430 15422ce morply peynt to 550 $4,028 81 aduce length Dette format 4144 H THI 14 Payment and rest Paid Sign - R P 3. Enter the Monthly Payment, Months, and Total Interest Paid from the Consolidation Loan Payment Calculator in the Original loan terms row of the Loan Payoff Options table found in the range 14:19. 4. Select cell G7 and then display the Goal Seek dialog box by tapping or clicking the What-If Analysis button (DATA tab | Data Tools group) and then tapping or clicking Goal Seek on the What-If Analysis menu. Determine how many months you need to pay off the loan if you pay $450.00 per month by setting cell G7 to the value 450 and changing cell G6. Update the Loan Payoff Options table with the results of this Goal Seek. 5. Use Goal Seek to determine how many months you need to pay off the loan if you pay $350.00 per month. Update the Loan Payoff Options table with the results of this Goal Seek. 6. Reduce the loan term to 42 months and record the monthly payment and total interest paid in the Loan Payoff Options table. 7. If requested by your instructor, enter your name in cell 14, before the text Loan Payoff Options. 8. Save the workbook. Submit the revised workbook as specified by your instructor. 9. Use Goal Seek to determine the Monthly Payment and Months for the final option in the Loan Payoff Options table, row 9. Which variable did you choose to change? What other variable could you change to reach a Total Interest figure of $4,500? STUDENT ASSIGNMENTS Excel Chapter 9
Expert Answer:
Related Book For
Data Analysis and Decision Making
ISBN: 978-0538476126
4th edition
Authors: Christian Albright, Wayne Winston, Christopher Zappe
Posted Date:
Students also viewed these accounting questions
-
Dr. Jenny Brown is the new CEO of a hotel chain, Hassle Free Hotel. The hotels headquarter is based in Atlanta, Georgia in the U.S. The company was established in 2004. It has a central reservation...
-
The file S03_59.xlsx lists the results of about 20,000 runners in the 2008 New York Marathon. a. For all runners who finished in 3.5 hours or less, create a pivot table and corresponding pivot chart...
-
The final project is designed to incorporate many of the techniques we have learned throughout the course and also to enhance your ability to communicate findings derived from data analysis. The data...
-
A certain apple bruises if a net force greater than 9 . 5 N is exerted on it . Would a 0 . 1 3 k g apple be likely to bruise if it falls 1 . 8 m and stops after sinking 0 . 0 5 m into the grass?...
-
In the Third Battle of Bull Run, for which a CPM/PERT network was developed in Problem 16, General Beauregard would have won if his preparations had been completed in 15 days. What would the...
-
The decision-making environment is called uncertainty if the probabilities are a. impossible. b. unknown. c. certain. d. all the above.
-
How does an auditor verify recorded shareholders' equity balances?
-
The Cookenwythe Gas Company pumps propane gas to the nearby Noxious Chemicals, Inc., polypropylene production plant. The gas is metered at the noxious plant at 400m 3 /h at 4.7atm gauge and 30C. The...
-
Bartosiewicz Clinic uses client-visits as its measure of activity. During January, the clinic budgeted for 3,900 client-visits, but its actual level of activity was 3,880 client-visits. The clinic...
-
Quigley Corporations trial balance at December 31, 2020, is presented below. All 2020 transactions have been recorded except for the items described below. Unrecorded transactions and adjustments: 1....
-
Draw up a cash budget for Amben Ltd showing the balance at the end of each month from the following information for the six months ended 31 December 2022 a. Opening cash balance (including bank)...
-
In what ways is your companys environmental sustainability strategy in the best long-term interest of shareholders? Does it contribute to your companys competitive advantage or profitability?
-
Suppose that \(\$ 1,000\) is invested at \(7 \%\) interest compounded monthly. Use the formula \[ A=P\left(1+\frac{r}{n}ight)^{n t}\] a. How long (to the nearest month) before the value is \(\$...
-
If your company opted to pursue a strategy of related diversification, what industries or product categories could your company diversify into that would allow your company to capitalize on using...
-
Using your university librarys business research resources and Internet sources, identify and discuss three key strategies that Volkswagen is using to compete in China.
-
Dad and Grandpa are each baking chocolate chip cookies. Each gives you two cookies. One of Dad's cookies has 14 chips in it and the other has Grandpa's cookies have 6 and 8 chips. a. Estimate the...
-
hey can ypls answerhttps://chegg-study.app.link/?%24randomized_bundle_token=1178706551087942599&feature=share_qna&campaign=share_qna&type=0&duration=0&source=android&data=eyIk 1 answer
-
Nitrogen monoxide reacts with hydrogen as follows: 2NO(g)+ H2(g) N2O(g) + H2O(g) The rate law is [H2]/ t = k[NO]2[H2], where k is 1.10 107 L2/(mol2s) at 826oC. A vessel contains NO and H2 at...
-
A manufacturing company makes two products. Each product can be made on either of two machines. The time (in hours) required to make each product on each machine is listed in the file S13_33.xlsx....
-
The grades on the midterm examination given in a large managerial statistics class are normally distributed with mean 75 and standard deviation 9. The instructor of this class wants to assign an A...
-
The daily demand for six-packs of Coke at Mr. Ds supermarket follows a normal distribution with mean 120 and standard deviation 30. Every Monday the Coke delivery driver delivers Coke to Mr. Ds. If...
-
Tesco plc is one of the worlds largest food retailers. Fiscal year 2014 (the year ended February 28, 2015) was a rocky year for the retailer. The companys sales and margins had come under pressure as...
-
Exhibit P2 displays a selected set of financial ratios for the years 20152017 of three fashion retailers: USbased Gap, UK-based Next, and Japan-based Uniqlo (Fast Retailing). Using this set of...
-
In 2009, following the worldwide credit crisis, several US-based car manufacturers, such as Chrysler and General Motors, approached bankruptcy and needed to be bailed out by the US government and...
Study smarter with the SolutionInn App