1.Switch to the Financing sheet and in cell B9, use a function to calculate the term of the loan based on the finance amount, interest rate, and payments. Hint: Remember to divide the interest rate by 12 to get the monthly rate, since you want to determine the number of monthly periods; use a negative value for Pv to represent a loan. The actual term is more than 44 months.

2. Use Goal Seek to change the payments so the term is exactly 48 months.

3. The result displays eight decimal places, but you can round this off, so decrease the decimal places to zero. The monthly payments would be $23,435 to pay off the $1M loan in four years.

4. In cell B10, enter a formula that multiplies the payments by the term and then subtracts the initial finance amount to return the total interest. The total interest cost for the loan is $124,862.03, which you will add to the total costs of the campaign on the ROI sheet.

5. Go to the ROI sheet and edit the formula in cell B14 so the total cost includes the total interest from the Financing sheet. The total costs are now $1,694,862, which reduces the overall profit to $705,138.

AutoSave Off He E8-A2-AdvertROL - Samantha Hott File Home Insert Draw Page Layout Data Formulas Review View Developer New Tab Help Power Pivot 44 NA A Data Analysis HHO 2. Al2 7 L 2 Sort Stocks Currencies Filter Text to Get Data Refresh All Outline Columns G What-If Forecast Analysis Sheet Forecast Get & Transform Data Queries & Conne... Data Types Sort & Filter & Data Tools Analysis C D E F G G H 1 J K L M M N B 1 Universal Corporate Events 2 Advertising Campaign 3 4 Financing 5 6 Finance Amount $1,066,794 7 Interest Rate 5.89% 8 Payments $25,000 9 Term 10 Total Interest 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 25 ROI Financing + AutoSave Off He E8-A2-AdvertROL - Samantha Hott File Home Insert Draw Page Layout Data Formulas Review View Developer New Tab Help Power Pivot 44 AL ZA Data Analysis NA 1 HHO @@ Sort Get Data Stocks Currencies Filter Text to Refresh All % Outline L Columns - G What-If Forecast Analysis Sheet Forecast Get & Transform Data Queries & Conne... Data Types Sort & Filter Data Tools Analysis B C D E F G H J K L M A 1 Universal Corporate Events 2 Advertising Campaign 3 4. Return on Investment 5 6 Ad Hours Airtime 7 Reach 8 Sales Rate 9 Costs Per Hour Airtime 10 Total Production Cost 11 Sales 12 Avg Sale Amount 13 Total Sales 14 Total Costs 15 Net Profit 100 2,000 1.92% $14,500 $120,000 3,840 $625 $2,400,000 $1,570,000 $830,000 Ad Hours Sales Rate $830,000 $0 $0 $0 $0 $0 $0 $80 $470,000 $490,000 $510,000 $530,000 $550,000 $570,000 $90 $543,750 $566,250 $588,750 $611,250 $633,750 $656,250 $100 $617,500 $642,500 $667,500 $692,500 $717,500 $742,500 $120 $765,000 $795,000 $825,000 $855,000 $885,000 $915,000 $150 $986,250 $1,023,750 $1,061,250 $1,098,750 $1,136,250 $1,173,750 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 25 + ROI Financing AutoSave Off He E8-A2-AdvertROL - Samantha Hott File Home Insert Draw Page Layout Data Formulas Review View Developer New Tab Help Power Pivot 44 NA A Data Analysis HHO 2. Al2 7 L 2 Sort Stocks Currencies Filter Text to Get Data Refresh All Outline Columns G What-If Forecast Analysis Sheet Forecast Get & Transform Data Queries & Conne... Data Types Sort & Filter & Data Tools Analysis C D E F G G H 1 J K L M M N B 1 Universal Corporate Events 2 Advertising Campaign 3 4 Financing 5 6 Finance Amount $1,066,794 7 Interest Rate 5.89% 8 Payments $25,000 9 Term 10 Total Interest 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 25 ROI Financing + AutoSave Off He E8-A2-AdvertROL - Samantha Hott File Home Insert Draw Page Layout Data Formulas Review View Developer New Tab Help Power Pivot 44 AL ZA Data Analysis NA 1 HHO @@ Sort Get Data Stocks Currencies Filter Text to Refresh All % Outline L Columns - G What-If Forecast Analysis Sheet Forecast Get & Transform Data Queries & Conne... Data Types Sort & Filter Data Tools Analysis B C D E F G H J K L M A 1 Universal Corporate Events 2 Advertising Campaign 3 4. Return on Investment 5 6 Ad Hours Airtime 7 Reach 8 Sales Rate 9 Costs Per Hour Airtime 10 Total Production Cost 11 Sales 12 Avg Sale Amount 13 Total Sales 14 Total Costs 15 Net Profit 100 2,000 1.92% $14,500 $120,000 3,840 $625 $2,400,000 $1,570,000 $830,000 Ad Hours Sales Rate $830,000 $0 $0 $0 $0 $0 $0 $80 $470,000 $490,000 $510,000 $530,000 $550,000 $570,000 $90 $543,750 $566,250 $588,750 $611,250 $633,750 $656,250 $100 $617,500 $642,500 $667,500 $692,500 $717,500 $742,500 $120 $765,000 $795,000 $825,000 $855,000 $885,000 $915,000 $150 $986,250 $1,023,750 $1,061,250 $1,098,750 $1,136,250 $1,173,750 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 25 + ROI Financing