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?...
-
Observing Pizza Hut Restaurant/Fast Food Restaurant's business operations: 1. Describe all business activities from the time a customer arrives to the time the customer departs. 2. List all costs you...
-
Business is going well for Valley Forest Products Inc. The board of directors of this family owned company believes that the company could earn an additional $9,000,000 in income after interest and...
-
Celestial Inc. is organized into 3 distinct divisions. The company is currently contemplating the elimination of either Division 1 or Division 2 because they are showing a pre-tax loss. An annual...
-
Ken Ramsing has been in the lumber business for most of his life. Ken's biggest competitor is Pacific Woods. Through many years of experience, Ken knows that the ordering cost for an order of plywood...
-
1-Tastee Mart sells Frostee Flakes. Demand for Frostee Flakes is 1,200 boxes per month. Tastee Mart has a holding cost of 35 percent and incurs a fixed cost of $216 for each replenishment order it...
-
Southeastern Foods has hired you to analyze their distribution-system design. The company has 11 distribution centers, with monthly volumes as listed below. Seven of these sites can support...
-
Describe how accounting for a held-to-maturity investment, an available-for-sale investment, and an equity-method investment is affected by a company electing the fair value option.
-
In "The Only fit food for a man is half a lemon: Kafka's Plea and Other Culinary Aberrations" by Steven G. Kellman explores how food acts as a marker of identity, as well as a marker of morality and...
-
Your principal requests to meet with you on Friday afternoon to discuss a new student coming to the school in two weeks. The principal explains that your new student is from Nepal and speaks Nepali....
-
Topic: social media and the effects on mental health Audience: college students/young adults Explain why you think this topic relates to your audience. What persuasive strategies (such as ethos,...
-
When we explore the art of fiction writing, we learn about traditional story arcs, and the six elements of story writing. In "Systems," author Charles Yu plays with structure and writes a story that...
-
Compare the grapevine, employees social media usage, and the formal communication network at your current or former company, and cite a story where the grapevine became problematic. Are there...
-
How can terminal velocity of a steel ball dropped in water be calculated when drag coefficient is not known? Also drag coefficient needs to be calculated?
-
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...
-
The following information is available from the financial statements of the Philips Company. Compute the current ratio and the debt-to-total-assets ratio and comment on any trends observed between...
-
The following information is available for the Owner Company: Prepare a statement of stockholders equity for the Owner Company. How much of the total equity is considered contributed capital and how...
-
The following information is available from the financial statements of the Philips Company. Compute the free cash flow and comment on any trends observed between 2018 and 2019. Net sales. Net...
Study smarter with the SolutionInn App