Answered step by step
Verified Expert Solution
Question
00
1 Approved Answer
hello everyone. i really need your help with this assignment to make the earnings model for cocacola company. the instructions are in the screen shots
hello everyone. i really need your help with this assignment to make the earnings model for cocacola company. the instructions are in the screen shots i have uploaded. kindly help me with this asap. i need to find the 10k income statment of the company and create an earnings model. i will truly appreciate your efforts. -The assignment requires a copy of the latest annual report (10-K) of your assigned company. You will need to use the financial statements portion to complete the assignment, which can be obtained through several sources. Some major sources include: 1) The company's Investor Relations Department. 2) A business library, such as the Baruch College Library or the Business and Science Library in New York City 3) The company's web site, where a copy can usually be downloaded directly. 4) A web site that accesses EDGAR, the SEC's corporate filing center. This can be accessed from Yahoo (under the company's profile) or through sites such as Free EDGAR (www.freeedgar.com) and EDGAR Online (www.edgar-online.com). 5) An annual report service, which is provided by major financial information companies such as Dow Jones. -A pro forma Income Statement must be prepared in MS Excel, using a minimum projection of three years, as based on pro forma projections discussed in Chapter 18. You need to include the most recent income statement for your company that is part of the 10-K. This information can then be used to calculate the fair value of the company's stock price, as based on the stock w eco2d1eadow 20.pdf to M9 Tue 8:19 PM Meer 1 / 3 144% 20 > 4) A web site that accesses EDGAR, the SEC's corporate filing center. This can be accessed from Yahoo (under the company's profile) or through sites such as Free EDGAR (www.freeedgar.com) and EDGAR Online (www.edgar-online.com). 5) An annual report service, which is provided by major financial information companies such as Dow Jones. -A pro forma Income Statement must be prepared in MS Excel, using a minimum projection of three years, as based on pro forma projections discussed in Chapter 18. You need to include the most recent income statement for your company that is part of the 10-K. This information cani then be used to calculate the fair value of the company's stock price, as based on the stock valuation formulas discussed in Chapters 7 and 20. All assumptions and formulas used for the pro forma projections and the company's fair value must be included at the end of your model. Once a fair value is reached, this can be compared to a recent quote of the company's stock price to determine whether or not the company is fairly valued at its most recent trading price. A separate attachment provides an example of how this should be constructed. w ON 0241svagdow.... eco241 svlearnin... X 16 deg-2017.pdf 93% Tue 8-420 na 6 a 1 2 144 XYZ Corporation Earnings Model Income & Pro Forma Income Statements 12/31/19 and PF 12/31/20 to 12/31/22 (Values in Millions of Dollars) 2019CS 2020PF 2021PF 2022PF 2019 Sales Cost of Goods Sold Depreciation EBIT Interest Income Before Taxes Taxes, 35% Net Income $2,500 ($750) ($250) $1,500 ($150 $1,350 ($473) $878 100.0% 30.0% 10.0% 60.0% 6.0% 54.0% 18.9% 35.1% $2,875 ($863) ($288) $1,725 ($173) $1,553 $543) $1.009 $3,306 $3,802 ($992) ($1,141) ($331) ($380) $1.984 $2,281 ($198) ($228) $1,785 $2,053 ($625) ($719) $4160 $1.385 Dividends Retained Earnings ($219) $658 25.0%* 75.0%* ($252) $757 ($290) S879 ($334) $1.001 Dividends Per Share (DPS) $2.19 $2.52 $2.90 $3.34 * -Dividends and RE are calculated as a percentage of Net Income. X 0 W & 1 12 6 144 L -Dividends and RE are calculated as a percentage of Net Income. Earnings Model Projections and Assumptions: -Pro forma sales growth is 15% per year - Common Size values are calculated as a percentage of sales, unless noted otherwise -DPS is calculated as Dividends / 100 Million Shares Outstanding -Dividends are expected to grow at a constant rate of 10% after pf2022 -The required return is 20% -The value of this company is calculated as PO = D1/(1 + r) + D2/(1+r)^2 + D3 / (1+r)^3 +P3 / (1+r)^3 Where P3 = D3 (1 +g) /r-g or D4/r-8 Using the Projections from XYZ Corporation's Earnings Model, The required return is 20% -The value of this company is calculated as PO = D1/(1+r) + D2 / (1+r)^2 + D3 / (1+r)^3 +P3 / (1+r)^3 Where P3 - D3 (1 +g)/r-g or D4/r-g Using the Projections from XYZ Corporation's Earnings Model, PO = 2.52/(1+0.20) + 2.90 / (1 + 0.20)^2 + 3.34/(1+0.20)^3 +36.70/(1+0.20) 3 Where P3 = $3.67 / 0.20 - 0.10 PO = $2.10 + $2.01 + $1.93 + $21.39 PO = $27.28 Once an estimated value is established there are three possible -Once an estimated value is established, there are three possible v scenarios when it is compared to a recent quote, as follows: Earnings Model Value PO = $27.28 PO = $27.28 PO = $27.28 XYZ Most Recent Quote (Three Scenarios) Secenario 1) $20, Undervalued Secenario 2) $50, Overvalued Secenario 3) $27.50, Fairly Valued MacBook Air -The assignment requires a copy of the latest annual report (10-K) of your assigned company. You will need to use the financial statements portion to complete the assignment, which can be obtained through several sources. Some major sources include: 1) The company's Investor Relations Department. 2) A business library, such as the Baruch College Library or the Business and Science Library in New York City 3) The company's web site, where a copy can usually be downloaded directly. 4) A web site that accesses EDGAR, the SEC's corporate filing center. This can be accessed from Yahoo (under the company's profile) or through sites such as Free EDGAR (www.freeedgar.com) and EDGAR Online (www.edgar-online.com). 5) An annual report service, which is provided by major financial information companies such as Dow Jones. -A pro forma Income Statement must be prepared in MS Excel, using a minimum projection of three years, as based on pro forma projections discussed in Chapter 18. You need to include the most recent income statement for your company that is part of the 10-K. This information can then be used to calculate the fair value of the company's stock price, as based on the stock w eco2d1eadow 20.pdf to M9 Tue 8:19 PM Meer 1 / 3 144% 20 > 4) A web site that accesses EDGAR, the SEC's corporate filing center. This can be accessed from Yahoo (under the company's profile) or through sites such as Free EDGAR (www.freeedgar.com) and EDGAR Online (www.edgar-online.com). 5) An annual report service, which is provided by major financial information companies such as Dow Jones. -A pro forma Income Statement must be prepared in MS Excel, using a minimum projection of three years, as based on pro forma projections discussed in Chapter 18. You need to include the most recent income statement for your company that is part of the 10-K. This information cani then be used to calculate the fair value of the company's stock price, as based on the stock valuation formulas discussed in Chapters 7 and 20. All assumptions and formulas used for the pro forma projections and the company's fair value must be included at the end of your model. Once a fair value is reached, this can be compared to a recent quote of the company's stock price to determine whether or not the company is fairly valued at its most recent trading price. A separate attachment provides an example of how this should be constructed. w ON 0241svagdow.... eco241 svlearnin... X 16 deg-2017.pdf 93% Tue 8-420 na 6 a 1 2 144 XYZ Corporation Earnings Model Income & Pro Forma Income Statements 12/31/19 and PF 12/31/20 to 12/31/22 (Values in Millions of Dollars) 2019CS 2020PF 2021PF 2022PF 2019 Sales Cost of Goods Sold Depreciation EBIT Interest Income Before Taxes Taxes, 35% Net Income $2,500 ($750) ($250) $1,500 ($150 $1,350 ($473) $878 100.0% 30.0% 10.0% 60.0% 6.0% 54.0% 18.9% 35.1% $2,875 ($863) ($288) $1,725 ($173) $1,553 $543) $1.009 $3,306 $3,802 ($992) ($1,141) ($331) ($380) $1.984 $2,281 ($198) ($228) $1,785 $2,053 ($625) ($719) $4160 $1.385 Dividends Retained Earnings ($219) $658 25.0%* 75.0%* ($252) $757 ($290) S879 ($334) $1.001 Dividends Per Share (DPS) $2.19 $2.52 $2.90 $3.34 * -Dividends and RE are calculated as a percentage of Net Income. X 0 W & 1 12 6 144 L -Dividends and RE are calculated as a percentage of Net Income. Earnings Model Projections and Assumptions: -Pro forma sales growth is 15% per year - Common Size values are calculated as a percentage of sales, unless noted otherwise -DPS is calculated as Dividends / 100 Million Shares Outstanding -Dividends are expected to grow at a constant rate of 10% after pf2022 -The required return is 20% -The value of this company is calculated as PO = D1/(1 + r) + D2/(1+r)^2 + D3 / (1+r)^3 +P3 / (1+r)^3 Where P3 = D3 (1 +g) /r-g or D4/r-8 Using the Projections from XYZ Corporation's Earnings Model, The required return is 20% -The value of this company is calculated as PO = D1/(1+r) + D2 / (1+r)^2 + D3 / (1+r)^3 +P3 / (1+r)^3 Where P3 - D3 (1 +g)/r-g or D4/r-g Using the Projections from XYZ Corporation's Earnings Model, PO = 2.52/(1+0.20) + 2.90 / (1 + 0.20)^2 + 3.34/(1+0.20)^3 +36.70/(1+0.20) 3 Where P3 = $3.67 / 0.20 - 0.10 PO = $2.10 + $2.01 + $1.93 + $21.39 PO = $27.28 Once an estimated value is established there are three possible -Once an estimated value is established, there are three possible v scenarios when it is compared to a recent quote, as follows: Earnings Model Value PO = $27.28 PO = $27.28 PO = $27.28 XYZ Most Recent Quote (Three Scenarios) Secenario 1) $20, Undervalued Secenario 2) $50, Overvalued Secenario 3) $27.50, Fairly Valued MacBook Air
hello everyone. i really need your help with this assignment to make the earnings model for cocacola company. the instructions are in the screen shots i have uploaded. kindly help me with this asap. i need to find the 10k income statment of the company and create an earnings model.
i will truly appreciate your efforts.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started