Hi, I was informed by Shakeel from order 83354 that my assignment has been missing depreciation,...

Question

Hi, I was informed by Shakeel from order 83354 that my assignment has been missing depreciation, without it, all my numbers change and I need it for my DCF valuation. I followed the 10k of this company but he suggested using yahoo finance or wsj since I might've missed it. I would like to continue to have Shakeel to do my assignment if it is okay since he knows my predicament and I'm thankful that he spotted a mistake. I am pretty much starting this from the beginning, I'll write down everything that needs to be done.
(attached excel) is what we have done so far and the template of how things should be done/look, step 1 is missing the report and step 6 is new but overall starting from scratch.The company I am using is The Boston Beer Company Inc.

Company profile. You should choose a company with a tractable record of at least five years. The company’s accounting and market information should be available for your study. Your report should include the description of its size and lines of business, a brief history of the company, its position in the industry, and some major events in recent years.

Construct an Excel file displaying the past three years of income statement, balance sheet, and the statement of cash flows. Then organize and recalculate the numbers that can be calculated. (Do not use the statement of cash flows from 10-k. Generate the cash flow statement using the indirect method we learned in class)

Add another worksheet for historical analysis & forecast assumptions. You will calculate revenue growth, % or revenue, efficiency measures (Inventory days, etc.) for some income statement and balance sheet items. Then you will make assumptions for these ratios. Below this table, use texts (words) to describe your historical analysis results and explain why you make such assumptions for the next five years of these ratios.

Based on your forecast assumptions, project the next five years of the income statement and balance sheet. Finally, project the statement of cash flow.

add another worksheet for Discounted Cash Flow (DCF) valuation. Similar to our lecture 8, you will calculate free cash flow to the firm for the next five years and the terminal value of the firm at the end of the fifth year. Then you will discount these cash flows to present and find out the business enterprise value as well as the equity value per share. Finally, based on your valuation, make investment advice for this stock.

To discount the cash flows, the discount rate will be the weighted average cost of capital. For simplicity, you don't have to estimate the WACC yourself. You can use the industry average cost of capital from thisdatabase(Links to an external site.). (http://people.stern.nyu.edu/adamodar/New_Home_Page/datafile/wacc.htm)

You need to also explain the steps and the assumptions so someone out of our class can understand. Please write on the DCF worksheet below the calculations. Don't create another worksheet.

add another worksheet to provide an overview of the project. You need to explain the overall process to value the company and refer to the relevant worksheets.

final-project-part-2-ch4bk5jg-xmzpt5na.xlsx

Shakeel · Accepted Answer

Financial Analysis.
		The Boston Beer Company Inc.
Income Statement for the Years
2018 through 2020
		(FYE February 3)
					Historical				Forecast
				2017	2018	2019	2020	2021	2022	2023	2024	2025
		Income Statement
		Revenue Annual Growth Rate		-4.75%	15.41%	25.50%	39.20%	18.84%	18.84%	18.84%	18.84%	18.84%
		Annual Absolute Value Increase		-43,000	133,000	254,000	490,000	327,847	389,620	463,031	550,275	653,956
		COGS % of Revenue		43.82%	41.95%	43.27%	45.52%	45.52%	45.52%	45.52%	45.52%	45.52%
		Depreciation: % of revenue		5.91%	5.22%	4.48%	4.20%	4.20%	4.20%	4.20%	4.20%	4.20%
		Gross Margin		52.14%	51.41%	49.92%	48.10%	48.10%	48.10%	48.10%	48.10%	48.10%
		SG&A % of Revenue		38.47%	39.76%	36.88%	32.24%	32.24%	32.24%	32.24%	32.24%	32.24%
		SG&A Annual Growth Rate		3.11%	19.28%	16.41%	21.69%	18.84%	18.84%	18.84%	18.84%	18.84%
		Annual Absolute Value Increase		10,000	64,000	65,000	100,000	105,703	125,619	149,288	177,416	210,845
		EBITDA Margin		19.58%	16.97%	16.96%	19.43%	19.43%	19.43%	19.43%	19.43%	19.43%
		Income tax		14.66%	20.69%	23.61%	21.31%	21.31%	21.31%	21.31%	21.31%	21.31%
		Balance Sheeet
		Inventory: Days in Inventory (DII)		51.42	59.28	68.00	57.68	57.68	57.68	57.68	57.68	57.68
		Account Receivable: Average Collection (DSO)		14.38	12.46	15.77	16.36	16.36	16.36	16.36	16.36	16.36
		Gross Fixed Assets: % of revenue		79.26%	73.69%	78.72%	64.43%	64.43%	64.43%	64.43%	64.43%	64.43%
		Other Current Assets: % of Revenue		1.04%	1.10%	0.88%	1.72%	1.72%	1.72%	1.72%	1.72%	1.72%
		Accumulated depreciation as % of Revenue		34.76%	34.54%	31.12%	25.23%	25.23%	25.23%	25.23%	25.23%	25.23%
		Account Payable: Days Payable Outstanding (DPO)		38.31	39.80	48.75	53.72	53.72	53.72	53.72	53.72	53.72
		Accrued Salaries & Benefits % of Revenue		1.85%	2.71%	2.80%	2.93%	2.93%	2.93%	2.93%	2.93%	2.93%
		Miscellaneous Current liabilities % of Revenue		5.21%	4.42%	5.12%	4.43%	4.43%	4.43%	4.43%	4.43%	4.43%
		Deferred tax % of Revenue		4.06%	4.92%	6.00%	5.34%	5.34%	5.34%	5.34%	5.34%	5.34%
		Other liabilities % of Revenue		1.16%	1.00%	0.56%	0.46%	0.46%	0.46%	0.46%	0.46%	0.46%
		Paid in Capital % of Revenue		43.22%	40.76%	45.76%	34.48%	34.48%	34.48%	34.48%	34.48%	34.48%
		Retained earnings % of Revenue		6.03%	5.62%	13.20%	20.52%	20.5%	20.5%	20.5%	20.5%	20.5%
		Net Working Capital
		Current Assets (less cash)		102,000	124,000	182,000	250,000	520,161	616,473	730,931	866,956	1,028,611
		Less: Current Liabilities		102,000	121,000	181,000	259,000	299,642	354,404	419,484	496,827	588,742
		Net Woring Capital		0	3,000	1,000	-9,000	220,519	262,069	311,447	370,129	439,868
		% of revenue		0.00%	0.30%	0.08%	-0.52%	-0.52%	-0.52%	-0.52%	-0.52%	-0.52%
In the current forecast we are forecasting the revenue, financial statements, and cash flow statement for the next 5 years from 2021 based on the data available from 2018 to 2020 for forecasting from 2021 to 2025. 
In assuming the income statement, we assume the revenue growth of 39% from 2021 to 2025 which is the stable growth rate as the case was in 2020. The increase in absolute value in 2021 to 2025 shall be 728,318 in 2021, 1,014,766 in 2022, 1,413,873 in 2023, 1,969,949 in 2024 and 2,744,730 in 2025. The increase in gross margin is 44% which is same gross margin from 2021 to 2025. The selling, general and administrative exp would increase on an average by 31%, which was same as 2020 and the assumption in forecast remains the same. The EBITDA margin has remained stable at 13% which had increased from 11% in 2019 to 13% in 2020 and henceforth. 
The average inventory days are 46 days which was the same as the forecast for 2021 till 2025. The average collection days were 15 days which was based on the realistic data of 2020. % of revenue of gross fixed assets shall be 34% which shall remain stable which is same as 2020. The ratio of other current assets with % of revenue shall be 1.6%. which shall remain same as 2020.

Hi, I was informed by Shakeel from order 83354 that my assignment has been missing depreciation, without it, all my numbers change and I need it for my DCF valuation. I followed the 10k of this...

Answer To: Hi, I was informed by Shakeel from order 83354 that my assignment has been missing depreciation,...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment