In this assignment, we are going to look at the 'piano incorporated' company. They are looking at buying a truck to move pianos. There are 2 ways to look at their cost for the truck. We are going to look at the cost based on different interest rates (one variable data table) and then we will look at how much can be afforded with a fixed interest rate and $500 (goal seek). This assignment has 2 parts. You will create: One variable data table worksheet (15 pts) Goal seek worksheet(15 pts) Download the data file and complete the following: Data Table: . Create a PMT function in cell B7. Make sure to use the current interest rate and time period given. B7: - PMT(B3"(13/12),B5,-B2) o B8: -B7'B5 o B9: B8-B2 o C12: -B7 o C13:=B8 o C14:=B9 Create a one-variable data table . Solution D G H 1 Piano Incorporated 2 Loan Amount: $27,500.00 3 Annual Interest Rate: 8.00% 4 Pmt. Period (months): 5 Number of Periods: 60 6 7 Payment Amount: $465.83 3 Total Payments $27.949.68 9 Total interest: $$49.68 10 11 12 Payment Amount: 13 Total Payments: 14 Total Interest: 6.0% 6.5% 7.056 7.5% 8.0% $465.83 $452.54 $463.22 $461.06 $464.92 $165.83 $27,909.68 $27,752 37 $27,796.33 $27,843.85 $27.99 $27.949.68 $109.68 $25237 $296 33 $343.86 $39.98 $449.68 Make sure that you format the cells to match what is given. Goal Seek In this example, we are going to see how much we can afford with $500 a month. Complete the following: Create a PMT function in cell B9. Make sure to use the current interest rate and time period given. B7: -PMT(B7/12,38*12,-36) Perform the goal seek analysis. You should get $29,176 as a value in cell B4. Please note that cell B7 is 5.5% rounded to 6%. Solution B 1 Plano Incorporated 2 3. PMT Function. The Purchase of Plano Moving Truck 4 Purchase Price $29,176 5 Down Payment $3,000 6 Amount to Finance $26,176 7 Interest Rate 6% 8 Term (years) 5 9 Monthly Payment $500.00