Research needs to be done. need a 5 year historical and 5 year projections. The requirements are in...

Question

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

Himanshu · Accepted Answer

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.

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...

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

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment

Sun	Mon	Tue	Wed	Thu	Fri	Sat
30	31	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	1	2	3