09SolPrepQ Modeling Financial Statements Professor Julian Yeo Requirements for (Optional) Final Exam For your final exam, you assigned company is Adobe Inc and its ticker is ADBE. Aim: Build a dynamic...

1 answer below »
Research needs to be done. need a 5 year historical and 5 year projections. The requirements are in the pdf. the 7.2 can be used as a guide.


09SolPrepQ Modeling Financial Statements Professor Julian Yeo Requirements for (Optional) Final Exam For your final exam, you assigned company is Adobe Inc and its ticker is ADBE. Aim: Build a dynamic financial model that forecasts diluted EPS. Deliverable: Excel file Due: Thursday (5/13) at 11.55pm EST. No late submission will be accepted. Required: 1. Build a dynamic financial model with 5 years of historical data on the Income Statements and Balance Sheets. 2. The dynamic financial model should include forecasts of Income Statements, Balance Sheets and Cash Flow Statements for Proj 1 to Proj 5. 3. On your income statements (IS), you should use the line items on the latest 10-K to set up your model. In addition to the line items on the 10-K, you need to separate out (1) Stock-Based Compensation Expense, (2) Depreciation and (3) Amortization of Purchased Intangibles. Make sure you have Diluted EPS calculated on the IS tab. 4. On your balance sheets (BS), you should use the line items on the latest 10-K to set up your model. You need to incorporate the concept of a Revolver on your balance sheets. 5. The dynamic financial model should include (but not limited to) the following supporting schedules:  Working capital  PP&E and Intangible  Debt  Equity 6. In your excel file (instead of a separate PPT file like the group project), whenever appropriate, explain how key ratios and assumptions are determined. 7. For capitalized leases, you are required to assume that the company will not have new leases for the projection periods. 8. Create an additional tab comparing your EPS forecasts with analysts’ EPS forecasts. Explain why/how your EPS forecasts are similar/different from analysts’ EPS forecasts. 9. This is an individual assessment. You are NOT allowed to collaborate with others. 10. Since this is a graded assessment, to be fair to all students, your TA and Professor Yeo will not be able to review any individual’s excel spreadsheet or answer any question that will lead to an answer for the final exam. Note: I remove the valuation components just like the project for your final exam as well. There is a curve for this class, your submission is being assessed relative to others. Here are the grading criteria (where you will be assessed against the best submission). max 1 Correct setups for statements 10 2 Correct formatting 5 3 Correct modeling of line items 40 4 Incorporation of footnotes/10-K information 10 5 Justification of ratios & assumptions 30 6 Additional analyses 5 Total 100 After the due date, we will show the submission with the highest grade.
final-exam-requirements-04d4n5x3.pdf ce72-kmb-fy2020-using-capital-iq-m2-2-2bexb2fg.xlsx
Answered 3 days AfterMay 06, 2021

Answer To: 09SolPrepQ Modeling Financial Statements Professor Julian Yeo Requirements for (Optional) Final Exam...

Himanshu answered on May 10 2021
124 Votes
Adobe Estimates
    Adobe INC (NYSE:KMB) > Estimates > Consensus
    Currency: Reported Currency    Conversion: Today's Share Price
    Units: Capital IQ (Default)    Decimals: CapitalIQ (Default)
    Consolidation: Consolidated    Acctg. Standard: US GAAP
    Current Fiscal Year End: Dec-31-2021 | FQ1 2021 Earnings Release Date: Apr-23-2021
    NYSE:ADBE (USD)    Mean    Median    High/Low    Std. Dev.    No. of Estimates.        NYSE:ADBE - Recommendation: Hold (2.94)
    Target Price    550    545    348.00/536.88    13.15    16/16        1 - Buy    2
    LT Growth    2.25%    2.25%    2.50%/2.00%    0.22    2/2        2 - Outperform    2
    Price Volatility    Average (3.00)                        3 - Hold    10
    Industry Recommendation    Hold (3.00)                        4 - Underperform    1
                                5 - Sell    2
                                0 - No Opinion    0
    Market Summary    NYSE:ADBE            NYSE:ADBE (US GAAP|USD)
    Currency    US Dollar                Current Quarter    Current Year    NTM
    Latest Price/Last Close Price    488.46/489.55            EPS Normalized    2.64    11.94    9.77
    52 Wk. High/Low    348.00/536.88             Guidance    -    7.75/-/8.00    -
    Potential Upside/Diff. from Target Price    5.33 %/6.800            Company Level (US GAAP|USD)
                    Revenue    9,897.0    13,682.0    13250
                     Guidance    -    19,905.60/-/20,288.40    -
                    EBITDA    3,971.0    5,049.0    4427.85
        Fiscal Years
    NYSE:ADBE (USD)    2020    2021    2022    2023    2024    2025
    EPS Normalized    7.74 A    7.78 E+    8.20 E+    8.49 E+    8.92 E+    8.99 E+
     Final Est.    7.67 E+    -    -    -    -    -
     Median    7.65 E+    7.80 E+    8.20 E+    8.41 E+    8.75 E+    8.99 E+
     High    7.91 E+    8.00 E+    8.51 E+    8.94 E+    9.44 E+    9.17 E+
     Low    7.54 E+    7.01 E+    7.83 E+    8.03 E+    8.57 E+    8.80 E+
     Std. Dev.    0.08    0.22    0.19    0.27    0.37    0.19
     No. of Estimates    17/17    16/16    15/15    10/10    3/3    2/2
     Guidance Low    7.50 GL    7.75 GL    -    -    -    -
     Guidance    -    -    -    -    -    -
     Guidance High    7.65 GH    8.00 GH    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    DPS    4.28 A    4.53 E+    4.72 E+    4.96 E+    5.18 E+    5.39 E+
     Final Est.    4.28 E+    -    -    -    -    -
     Median    4.28 E+    4.56 E+    4.74 E+    4.97 E+    5.20 E+    5.39 E+
     High    4.56 E+    4.56 E+    4.84 E+    5.22 E+    5.22 E+    5.44 E+
     Low    4.20 E+    4.40 E+    4.57 E+    4.76 E+    5.12 E+    5.33 E+
     Std. Dev.    0.09    0.05    0.08    0.12    0.04    0.06
     No. of Estimates    13/13    11/11    11/11    10/10    3/3    2/2
     Guidance Low    -    -    -    -    -    -
     Guidance    -    -    -    -    -    -
     Guidance High    -    -    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Company Level (USD)    2020    2021    2022    2023    2024    2025
    Revenue    19,140.00 A    19,990.70 E+    20,402.68 E+    20,806.44 E+    21,389.00 E+    21,561.50 E+
     Final Est.    19,034.37 E+    -    -    -    -    -
     Median    19,026.00 E+    19,990.20 E+    20,338.01 E+    20,764.64 E+    21,357.00 E+    21,561.50 E+
     High    19,237.10 E+    20,297.00 E+    21,075.70 E+    21,547.70 E+    22,015.00 E+    21,941.00 E+
     Low    18,889.00 E+    19,721.00 E+    20,054.20 E+    20,281.52 E+    20,795.00 E+    21,182.00 E+
     Std. Dev.    83.44    147.98    283.66    388.42    498.58    379.5
     No. of Estimates    14/14    13/13    12/12    10/10    3/3    2/2
     Guidance Low    18,819.00 GL    19,905.60 GL    -    -    -    -
     Guidance    -    -    -    -    -    -
     Guidance High    19,003.50 GH    20,288.40 GH    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    EBITDA    4,378.00 A    4,427.85 E+    4,594.78 E+    4,706.34 E+    4,953.00 E+    5,013.00 E+
     Final Est.    4,462.05 E+    -    -    -    -    -
     Median    4,455.50 E+    4,470.00 E+    4,620.00 E+    4,730.00 E+    4,953.00 E+    5,013.00 E+
     High    4,575.00 E+    4,555.00 E+    4,803.40 E+    4,956.70 E+    5,026.00 E+    5,013.00 E+
     Low    4,384.15 E+    4,000.20 E+    4,373.20 E+    4,464.60 E+    4,880.00 E+    5,013.00 E+
     Std. Dev.    53.96    143.08    107.22    139.08    73.0    -
     No. of Estimates    11/11    11/11    11/11    9/9    2/2    1/1
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    EBIT    3,582.00 A    3,571.44 E+    3,713.05 E+    3,792.91 E+    3,960.67 E+    3,975.00 E+
     Final Est.    3,604.71 E+    -    -    -    -    -
     Median    3,604.00 E+    3,604.50 E+    3,731.00 E+    3,796.00 E+    3,919.00 E+    3,975.00 E+
     High    3,691.50 E+    3,674.74 E+    3,840.90 E+    3,972.60 E+    4,111.00 E+    4,026.00 E+
     Low    3,547.00 E+    3,230.20 E+    3,555.06 E+    3,585.29 E+    3,852.00 E+    3,924.00 E+
     Std. Dev.    42.97    111.18    82.24    114.62    109.76    51.0
     No. of Estimates    13/13    12/12    12/12    10/10    3/3    2/2
     Guidance Low    -    3,653.64 GL    -    -    -    -
     Guidance    -    -    -    -    -    -
     Guidance High    -    -    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Interest Expense    ( 244.00 A)    ( 262.83 E+)    ( 254.54 E+)    ( 248.95 E+)    ( 250.00 E+)    ( 252.00 E+)
     Final Est.    ( 257.84 E+)    -    -    -    -    -
     Median    ( 255.35 E+)    ( 264.00 E+)    ( 251.80 E+)    ( 250.40 E+)    ( 250.00 E+)    ( 252.00 E+)
     High    ( 290.00 E+)    ( 278.00 E+)    ( 266.00 E+)    ( 266.00 E+)    ( 250.00 E+)    ( 252.00 E+)
     Low    ( 244.00 E+)    ( 251.00 E+)    ( 248.00 E+)    ( 229.00 E+)    ( 250.00 E+)    ( 252.00 E+)
     Std. Dev.    13.97    9.82    6.06    13.2    -    -
     No. of Estimates    8/10    7/9    7/9    4/6    1/2    1/2
     Guidance Low    -    -    -    -    -    -
     Guidance High    -    -    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    EBT (Excl. Excep)    3,304.00 A    3,272.71 E+    3,435.69 E+    3,503.89 E+    3,570.00 E+    3,663.50 E+
     Final Est.    3,320.53 E+    -    -    -    -    -
     Median    3,323.26 E+    3,293.90 E+    3,485.00 E+    3,484.34 E+    3,570.00 E+    3,663.50 E+
     High    3,400.92 E+    3,397.22 E+    3,545.50 E+    3,681.70 E+    3,615.00 E+    3,733.00 E+
     Low    3,242.00 E+    2,948.40 E+    3,263.55 E+    3,291.51 E+    3,525.00 E+    3,594.00 E+
     Std. Dev.    47.59    124.28    91.79    119.73    45.0    69.5
     No. of Estimates    10/10    9/9    11/11    8/8    2/2    2/2
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    EBT (GAAP)    2,930.00 A    3,199.25 E+    3,398.50 E+    3,497.50 E+    3,636.33 E+    3,663.50 E+
     Final Est.    2,892.33 E+    -    -    -    -    -
     Median    2,888.00 E+    3,187.50 E+    3,379.50 E+    3,484.50 E+    3,615.00 E+    3,663.50 E+
     High    2,988.00 E+    3,268.00 E+    3,485.00 E+    3,597.00 E+    3,769.00 E+    3,733.00 E+
     Low    2,832.96 E+    3,154.00 E+    3,340.00 E+    3,386.00 E+    3,525.00 E+    3,594.00 E+
     Std. Dev.    47.86    42.9    49.64    72.12    100.75    69.5
     No. of Estimates    6/6    4/5    6/6    6/6    3/3    2/2
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Net Income (Excl. Excep)    2,651.00 A    2,630.14 E+    2,744.23 E+    2,818.99 E+    2,944.33 E+    2,946.00 E+
     Final Est.    2,629.27 E+    -    -    -    -    -
     Median    2,627.40 E+    2,654.50 E+    2,754.00 E+    2,808.00 E+    2,893.00 E+    2,946.00 E+
     High    2,707.30 E+    2,701.00 E+    2,819.60 E+    2,950.10 E+    3,090.00 E+    2,987.00 E+
     Low    2,581.00 E+    2,375.70 E+    2,625.60 E+    2,654.60 E+    2,850.00 E+    2,905.00 E+
     Std. Dev.    30.92    85.0    61.19    89.28    104.49    41.0
     No. of Estimates    12/12    11/11    11/11    9/9    3/3    2/2
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Net Income (GAAP)    2,352.00 A    2,526.40 E+    2,702.83 E+    2,781.67 E+    2,877.67 E+    2,946.00 E+
     Final Est.    2,295.87 E+    -    -    -    -    -
     Median    2,279.00 E+    2,531.00 E+    2,699.00 E+    2,784.00 E+    2,890.00 E+    2,946.00 E+
     High    2,358.00 E+    2,547.00 E+    2,792.00 E+    2,886.00 E+    2,893.00 E+    2,987.00 E+
     Low    2,269.00 E+    2,507.00 E+    2,598.00 E+    2,687.00 E+    2,850.00 E+    2,905.00 E+
     Std. Dev.    30.82    14.28    61.09    61.3    19.6    41.0
     No. of Estimates    7/7    5/6    6/6    6/6    3/3    2/2
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Net Debt    8,061.00 A    8,079.85 E+    7,801.79 E+    7,493.88 E+    6,147.00 E+    6,512.00 E+
     Final Est.    8,103.09 E+    -    -    -    -    -
     Median    8,148.14 E+    8,035.27 E+    7,789.00 E+    7,551.43 E+    6,147.00 E+    6,512.00 E+
     High    8,529.80 E+    8,554.00 E+    8,510.08 E+    8,501.49 E+    6,979.00 E+    6,512.00 E+
     Low    7,380.86 E+    7,486.00 E+    6,825.00 E+    6,104.00 E+    5,315.00 E+    6,512.00 E+
     Std. Dev.    360.12    323.74    427.69    655.54    832.0    -
     No. of Estimates    8/8    9/9    9/9    8/8    2/2    1/1
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Net Asset Value    -    -    -    -    -    -
     Final Est.    -    -    -    -    -    -
     Median    -    -    -    -    -    -
     High    -    -    -    -    -    -
     Low    -    -    -    -    -    -
     Std. Dev.    -    -    -    -    -    -
     No. of Estimates    -    -    -    -    -    -
     Acctg. Standard    -    -    -    -    -    -
    Depreciation & Amortization    796.00 A    849.88 E+    877.89 E+    902.57 E+    943.33 E+    974.00 E+
     Final Est.    855.09 E+    -    -    -    -    -
     Median    835.00 E+    854.00 E+    882.00 E+    911.00 E+    940.00 E+    977.00 E+
     High    954.00 E+    926.90 E+    962.50 E+    984.10 E+    961.00 E+    987.00 E+
     Low    801.00 E+    770.00 E+    770.50 E+    777.90 E+    929.00 E+    958.00 E+
     Std. Dev.    46.45    46.98    52.17    58.9    13.27    12.03
     No. of Estimates    9/9    9/9    9/9    7/7    3/3    3/3
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Cash From Operations    3,729.00 A    3,501.93 E+    3,650.69 E+    3,697.72 E+    3,786.50 E+    3,899.50 E+
     Final Est.    3,381.64 E+    -    -    -    -    -
     Median    3,387.00 E+    3,478.00 E+    3,617.00 E+    3,649.00 E+    3,786.50 E+    3,899.50 E+
     High    3,584.00 E+    3,725.00 E+    3,863.00 E+    3,931.60 E+    3,825.00 E+    3,949.00 E+
     Low    3,147.00 E+    3,374.00 E+    3,478.00 E+    3,594.00 E+    3,748.00 E+    3,850.00 E+
     Std. Dev.    150.75    115.2    142.95    124.62    38.5    49.5
     No. of Estimates    7/8    7/8    7/8    5/6    2/2    2/2
     Guidance High    -    3,700.00 GH    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Capital Expenditure    ( 1,217.00 A)    ( 1,218.48 E+)    ( 1,111.80 E+)    ( 1,069.11 E+)    ( 1,155.00 E+)    ( 1,191.50 E+)
     Final Est.    ( 1,200.94 E+)    -    -    -    -    -
     Median    ( 1,236.30 E+)    ( 1,245.96 E+)    ( 1,125.60 E+)    ( 1,036.84 E+)    ( 1,155.00 E+)    ( 1,191.50 E+)
     High    ( 1,250.00 E+)    ( 1,280.00 E+)    ( 1,302.00 E+)    ( 1,325.00 E+)    ( 1,349.00 E+)    ( 1,374.00 E+)
     Low    ( 1,000.00 E+)    ( 1,008.00 E+)    ( 923.00 E+)    ( 941.00 E+)    ( 961.00 E+)    ( 1,009.00 E+)
     Std. Dev.    78.83    81.56    120.31    126.72    194.0    182.5
     No. of Estimates    8/8    8/8    8/8    6/6    2/2    2/2
     Guidance Low    ( 1,200.00 GL)    ( 1,200.00 GL)    -    -    -    -
     Guidance    -    -    -    -    -    -
     Guidance High    ( 1,300.00 GH)    ( 1,300.00 GH)    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Free Cash Flow    2,512.00 A    2,282.95 E+    2,560.20 E+    2,666.20 E+    2,632.00 E+    2,708.50 E+
     Final Est.    2,201.99 E+    -    -    -    -    -
     Median    2,175.00 E+    2,238.00 E+    2,585.50 E+    2,666.50 E+    2,632.00 E+    2,708.50 E+
     High    2,567.00 E+    2,717.00 E+    2,901.00 E+    2,983.00 E+    2,864.00 E+    2,940.00 E+
     Low    1,980.00 E+    2,094.00 E+    2,204.00 E+    2,324.00 E+    2,400.00 E+    2,477.00 E+
     Std. Dev.    194.67    178.05    235.23    219.51    232.0    231.5
     No. of Estimates    7/7    8/8    8/8    6/6    2/2    2/2
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    Same Store Sales Growth %    5.20 E+    1.30 E+    2.10 E+    1.90 E+    -    -
     Median    5.20 E+    1.30 E+    2.10 E+    1.90 E+    -    -
     High    5.20 E+    1.30 E+    2.10 E+    1.90 E+    -    -
     Low    5.20 E+    1.30 E+    2.10 E+    1.90 E+    -    -
     Std. Dev.    -    -    -    -    -    -
     No. of Estimates    1/1    1/1    1/1    1/1    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    -    -
    Gross Margin %    37.12 A    35.38 E+    36.05 E+    35.90 E+    -    -
     Final Est.    37.22 E+    -    -    -    -    -
     Median    37.45 E+    35.40 E+    36.20 E+    35.70 E+    -    -
     High    38.00 E+    37.00 E+    37.10 E+    37.40 E+    -    -
     Low    35.50 E+    33.90 E+    34.60 E+    34.60 E+    -    -
     Std. Dev.    0.8    1.02    0.97    1.15    -    -
     No. of Estimates    6/7    6/7    6/7    3/4    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    -    -
    Effective Tax Rate %    22.70 A    22.89 E+    23.01 E+    23.07 E+    22.70 E+    22.70 E+
     Final Est.    23.05 E+    -    -    -    -    -
     Median    23.00 E+    23.00 E+    23.00 E+    23.00 E+    22.70 E+    22.70 E+
     High    23.40 E+    23.00 E+    24.00 E+    24.00 E+    23.00 E+    23.00 E+
     Low    22.80 E+    22.50 E+    22.40 E+    22.40 E+    22.40 E+    22.40 E+
     Std. Dev.    0.17    0.18    0.43    0.47    0.3    0.3
     No. of Estimates    8/9    8/9    8/9    6/6    2/2    2/2
     Guidance Low    23.00 GL    22.00 GL    -    -    -    -
     Guidance    -    -    -    -    -    -
     Guidance High    24.00 GH    24.00 GH    -    -    -    -
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    ROE %    894.10 A    305.94 E+    194.53 E+    139.20 E+    116.70 E+    101.70 E+
     Final Est.    2,583.18 E+    -    -    -    -    -
     Median    2,553.71 E+    306.61 E+    197.16 E+    145.60 E+    116.70 E+    101.70 E+
     High    2,994.94 E+    328.80 E+    218.40 E+    146.11 E+    116.70 E+    101.70 E+
     Low    2,200.89 E+    282.42 E+    165.40 E+    125.90 E+    116.70 E+    101.70 E+
     Std. Dev.    324.84    18.94    19.01    9.41    -    -
     No. of Estimates    3/4    3/5    4/5    3/4    1/1    1/1
     Acctg. Standard    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP    US GAAP
    ROA %    16.16 A    14.50 E+    14.71 E+    14.49 E+    14.90 E+    15.10 E+
     ...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?