Question

Hello, can I please get a step by step solution through excel? Thank you!

You own 2,200 shares of stock in Avondale Corporation. You will receive a dividend of $1.80 per share in one year. In two years, the company will pay a liquidating dividend of $45 per share. The required return on the company's stock is 20 percent. a. Ignoring taxes, what is the current share price of your stock? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) b. If you would rather have equal dividends in each of the next two years, how many shares would you sell in one year? (Do not round intermediate calculations and round your answer to the nearest whole number, e.g., 32.) c. What would your cash flow be for each year for the next two years if you create equal homemade dividends? Hint: Dividends will be in the form of an annuity. (Do not round intermediate calculations and round your answer to the nearest whole number, e.g., 32.) Share price b. Number of shares c. Cash flow

Answer 1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

WITH ALL CELL REFERENCES, EXPLAINED. I HAVE ALSO GIVEN ONE MORE EASY EXPLANATION FOR THE SUM BUT WITH NO CELL REFERENCES USED.

PE EPS, STOCK DIVIDEND Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins Σ AutoSum - Cut A A Wrap Text Calibri 11 General ECopy Fill Merge & Center Paste +.0 .00 .00 0 Conditional Format Cell Insert Delete Format Sort & Find & Filter Select BIU % Format Painter Clear Formatting as Table Styles Number Clipboard Font Alignment Styles Cells Editing fox B1 A В C E 1 2 D1 1.8 . 4 D2 45 ke 5 20% 6 7 NO OF SHARES 2200 8 9 STEP 1 PRESENT VALUE OF DIVIDENDS = PO [=C3/(1+C5) +C4/(1+C5)^2] 32.75 10 ANSWER 1 PO 11 12 STEP 2 WE NEED EQUAL DIVIDEND IN BOTH YEARS [=C10/(1/(1+C5)+1/(1+C5)^2)] 13 D E 21.44 14 [C4/(1+C5) 15 STEP 3 P1 37.50 16 17 STEP 4 IN PRESENT SCENARIO 18 AS YOU HAVE 2200 SHARES [=C7*C13] 19 YOU WANT 47160 =C7*C3] 20 BUT YOU WILL GET ONLY 3960 21 call put Sheet1 bilbo MINT Sheet6 EXPAND Sheet3 BRIGHAN CAP GAIN SENSITIVITY FROS SUN MERGER HICKОСK BILBO EXCEL m.v.p, DISNEY Rea 120% FEF erences WA 01:15 ENG 29-11-2019

PE EPS, STOCK DIVIDEND Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins Cut Σ AutoSum - A A Wrap Text Calibri 11 General Copy Fill Merge & Center Paste в IU* +.0 .00 .00 0 Conditional Format Cell Insert Delete Format Sort & Find & Filter Select % Format Painter Clear Formatting as Table Styles Number Clipboard Font Alignment Styles Cells Editing fox B35 A В C D E 16 17 STEP 4 IN PRESENT SCENARIO AS YOU HAVE 2200 SHARES 18 47160 C7*C13] 19 YOU WANT 3960 [=C7*C3] 20 BUT YOU WILL GET ONLY 21 [=C19-C20] 22 SHORTFALL IN CASH FLOW 43200 23 SO NO OF SHARES TO BE SOLD 1152 =C22/C15] 24 ANSWER 2 25 26 STEP 5 C7*C3 +C24*C15] 27 ANSWER 3 CASHFLOW IN YEAR 1 47160 [=(C7-C24)*C4] 28 CASHFLOW IN YEAR 2 47160 29 30 31 32 33 34 35 36 call put Sheet1 EXPAND Sheet6 MINT bilbo Sheet3 BRIGHAN CAP GAIN SENSITIVITY FROS SUN MERGER HICKОСK BILBO EXCEL m.v.p, DISNEY Rea 120% erences FEF WA 01:15 ENG 29-11-2019

ANOTHER EASY METHOD OF EXPLANATION BUT NO EXCEL CELL REFERENCES USED, IT IS FOR UNDERSTANDING PURPOSE

PE EPS, STOCK DIVIDEND Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins E AutoSum - Cut A A Wrap Text Calibri 11 General Copy Fill Merge & Center Paste +.0 .00 Conditional Format Cell Insert Delete Format Sort & Find & BIU % .00 0 Format Painter Clear Filter Select Formatting as Table Styles Number Clipboard Font Alignment Styles Cells Editing fox N5 F G Н J K М P Q 5 PO= PRESENT VALUE OF DIVIDENDS D1/(1+ke) D2/(1+ke)^2 PO = D1- 1.80, D2 = 45, ke-20% 1.80/(1+0.20) 45/(1+0.20)^2 7 PO = PO= 32.75 9 10 b NOW WE NEED EQUAL DIVIDEND FOR BOTH THE YEARS SO D/(1+0.20) D/(1+0.20)^2 11 32.75 12 D(0.8333)D(0.6944) 32.75 = D(1.52777778) 13 32.75 14 D= 21.44 15 16 NOW PRICE OF STOCK AFTER A YEAR = P1 = 45/(1+0.20) 37.50 17 P1 18 19 NOW YOU HAVE 2200 SHARES SO YOU WANT 47160.00 20 2200 X 21.44 = 21 BUT YOU WILL GET ONLY 2200 X 1.80 = 3960 22 SO SHORTFALL IN CASH FLOW = 43200.00 call put bilbo FROS SUN MERGER Sheet1 EXPAND Sheet6 Sheet3 BRIGHAN CAP GAIN MINT SENSITIVITY HIСКОСК BILBO EXCEL m.v.p, DISNEY Rea erences 130% FEF WA 01:17 ENG 29-11-2019

PE EPS, STOCK DIVIDEND Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins E AutoSum - Cut A A Wrap Text Calibri 11 General Copy Fill B IU Paste +.0 .00 Conditional Format Cell Insert Delete Format Sort & Find & % aMerge & Center .00 0 Format Painter Clear Filter Select Formatting as Table Styles Clipboard Font Alignment Number Styles Cells Editing fr N36 F G H K М O Q 18 19 NOW YOU HAVE 2200 SHARES SO YOU WANT 2200 X 21.44 = 47160.00 20 3960 21 BUT YOU WILL GET ONLY 2200 X 1.80 = SO SHORTFALL IN CASH FLOW = 22 43200.00 23 SO YOU HAVE TO SELL SOME SHARES TO GET ADDITIONAL CASH FLOW 24 43200/37.50= 25 NO OF SHARES TO BE SOLD = 1152.00 26 NO OF SHARES TO BE SOLD = 1152 27 (2200 X 1.80) + (1152 X 37.50) 28 CASH FLOW IN YEAR 1 47160 C CASH FLOW IN YEAR 2 (2200 1152) x 45 = 29 47160 30 31 32 33 34 35 36 call put bilbo Sheet1 Sheet6 EXPAND Sheet3 BRIGHAN CAP GAIN MINT SENSITIVITY FROS SUN MERGER HIСКОСК BILBO EXCEL m.v.p, DISNEY Rea erences 130% FEF WA 01:17 ENG 29-11-2019