Help me create a three-stage dividend discount model. Excel encouraged, but by hand is cool too. Please just show formulas, and/or work calculations. Thanks :)

Crescent, Inc. Case George Shoemaker, a security analyst at Better Investment was reviewing the 20 17 financial statements of Crescent, Inc. to address the questions of one of their most important clients, Andy Rich, who has expressed his interest in Crescent's stock. Crescent Inc. is a relatively young company in the electric car industry , promising an extraordinary growth potential for the next five years due to their newest model, Atlas, introduced about a year ago. Electric cars are propelled by electric motors that use energy stored in rechargeable batteries. Atlas comes with an extraordinary battery life than its major competitors' operating in the same industry. Another important feature of Atlas is that it requires significantly shorter charging time with several new charging stations being made available regularly across country. Recently, in a press conference, the management team of Crescent expressed a keen interest to eventually offer its somewhat modified, lesser luxury version at prices affordable to the average customer. Shoemaker's analyses and his subsequent presentation to Andy Rich included several important financial aspects of the company. Crescent's most recent reported earnings (Net Income) at the end of 2017 was $1.2 billion, roughly 20% higher than previous year's earnings The general sentiment of financial analysts following the industry is that the company should be able to maintain this growth for another five years. Crescent paid $240 million in dividends in 2017. Andy seemed to be concerned about the amount of dividends paid relative to company's earnings. Furthermore, George's additional analyses revealed that after five years the growth rate will decline linearly each ye ar to a stable growth rate of 5% due to intense competition in the electric car industry. Shoemaker also noted that according to analysts' reports payout ratio was expected to remain unchanged from 2017 to 2022, after which it would increase each year equally to reach 70% in steady state. Crescent's stock is expected to have a beta of 1, 75 during the high growth period, after which the beta would decline each year (again, linearly) to reach 1 by the time the firm becomes stable. Currently the Treasury bond rate is 6.25%, and the market risk premium has historically been around 5.5%. By the end of the meeting, Mr. Rich was quite overwhelmed with the amount of information presented. He asked George to give him an estimate of the intrinsic value of the stock . Crescent currently has 400,000,000 shares outstanding. George assured him that he will have the value of the stock ready next morning by using a "sophisticated model (as he called it) which involved three-stage dividend discount ing process. After Andy left, George took another sip from his coffee and got ready to prepare a full, fresh pot that he was going to need for the rest of the night. Crescent, Inc. Case George Shoemaker, a security analyst at Better Investment was reviewing the 20 17 financial statements of Crescent, Inc. to address the questions of one of their most important clients, Andy Rich, who has expressed his interest in Crescent's stock. Crescent Inc. is a relatively young company in the electric car industry , promising an extraordinary growth potential for the next five years due to their newest model, Atlas, introduced about a year ago. Electric cars are propelled by electric motors that use energy stored in rechargeable batteries. Atlas comes with an extraordinary battery life than its major competitors' operating in the same industry. Another important feature of Atlas is that it requires significantly shorter charging time with several new charging stations being made available regularly across country. Recently, in a press conference, the management team of Crescent expressed a keen interest to eventually offer its somewhat modified, lesser luxury version at prices affordable to the average customer. Shoemaker's analyses and his subsequent presentation to Andy Rich included several important financial aspects of the company. Crescent's most recent reported earnings (Net Income) at the end of 2017 was $1.2 billion, roughly 20% higher than previous year's earnings The general sentiment of financial analysts following the industry is that the company should be able to maintain this growth for another five years. Crescent paid $240 million in dividends in 2017. Andy seemed to be concerned about the amount of dividends paid relative to company's earnings. Furthermore, George's additional analyses revealed that after five years the growth rate will decline linearly each ye ar to a stable growth rate of 5% due to intense competition in the electric car industry. Shoemaker also noted that according to analysts' reports payout ratio was expected to remain unchanged from 2017 to 2022, after which it would increase each year equally to reach 70% in steady state. Crescent's stock is expected to have a beta of 1, 75 during the high growth period, after which the beta would decline each year (again, linearly) to reach 1 by the time the firm becomes stable. Currently the Treasury bond rate is 6.25%, and the market risk premium has historically been around 5.5%. By the end of the meeting, Mr. Rich was quite overwhelmed with the amount of information presented. He asked George to give him an estimate of the intrinsic value of the stock . Crescent currently has 400,000,000 shares outstanding. George assured him that he will have the value of the stock ready next morning by using a "sophisticated model (as he called it) which involved three-stage dividend discount ing process. After Andy left, George took another sip from his coffee and got ready to prepare a full, fresh pot that he was going to need for the rest of the night