Using the assignment instructions and the spreadsheet below, how should the formulas for cells D2:F2 be written? I know how to complete the data table but I am unable to figure out what formula connects the advertising budgets in cells D2:F2 to the equity valuation in cell C66.

Instructions: Build a data table and graph to show what happens to the equity value for different

advertising budgets in years 1-3 if the same amount is spent on advertising in each of

those three years. Use the shaded regions starting in A69 with advertising amounts from

$0 to $30 in $1 increments. You will need to alter cells D2:F2 to complete the analysis. The company is unable to spend more than $100 in any given year on advertising.

Spreadsheet Given:

B C D E G PRO FORMA FINANCIAL MODE 2 ADDVERTAdvertising Expense $ 20.00 $ 20.00 $ 20.00 $ 20.00 $ 2 20.00 6-Year Sum = 120.00 CA/S Current assets/Sales 15% 4 CL/S Current liabilities/Sales 89% 5 NFA/'S Net fixed assets/Sales 779 CGS/S Costs of goods sold/Sales 50% Dg Depreciation rate 10% 8 DEBT Interest rate on debt 10.009% 9 CASH Interest paid on cash and marketable securities 8.00% 10 Tax rate 40% 11 PAYOUT Dividend payout ratio 409 12 Year 0 2 3 5 13 Income statement -4.20% -4.209% -4.20% -4.20% -4.20% 25 22 19