[SOLVED] Excel finance MBA 542: Managerial Finance, Spring 2017

MBA 542: Managerial Finance, Spring 2017
Company Analysis & Valuation Report

STUDENT NAME: .
Submitted to Dr. Chen Liu through email: [email protected]
Due May 6 @ 10pm
Please attach (1) this report, (2) Valuation excel file, and (3) Excel files of financial statements from Seeking Alpha in one email.

1. Background of your company:
(1) Brief business description: include the general sector, specific industry, what does the company do, the main activities through which the company generates revenue.
Type your answers starting from here:

(2) List the companys five main competitors, if your company does not have five competitors, list top three. Comment on the companys competitive advantage, focus on what the company does better than its competitors and how long you think the company will keep the competitive advantage. Or if the company is not doing better than its competitors, discuss the situation and whether you see the potential for your company to improve.
Type your answers starting from here:

(3) Whether and to what degree will your company affected by Presidents Donald Trumps economic policy. (Business tax cut, immigration, etc, Google it)
Type your answers starting from here:

(4) Your companys stock history in the past 3 years: download your companys daily stock price in the last 3 years along with S&P 500 in the last 3 years (April 18 2014-April 18 2017). Calculate the average return and standard deviation of your companys stock and S&P 500. Insert a graph on your companys return and S&P 500 return in the past 3 years. All your calculation should be done on the sheet Stock Price (3yr) on the Excel.
Type your answers starting from here:

2. Company Analysis
First of all, get the financial statement from the last two years, copy & paste balance sheet and income statement to the excel sheet Ratios. Then do all your calculations from there. For stock price, just find the price at the financial statement report day.
(1) Short-Term Solvency: Calculate the companys current ratio, quick ratio, and defensive interval ratio (DIR); and comment the companys overall short-term solvency.
Type your answers starting from here:

(2) Long-Term Debt Policy: Calculate the companys total debt ratio, EBIT interest coverage ratio, cash coverage ratio. Comment on the companys capital structure.
Type your answers starting from here:

Based on both the trade-off theory and the pecking-order theory of debt, should the company use high or low level of debt?
Type your answers starting from here:
Trade-off theory: tax shield benefit (high profitability, high tax bracket) vs financial distress cost (profitability, risk, tangible assets).
DELL:
profitability is very low, do not need to borrow a lot to provide tax shield;
because profitability is low, financial distress probability is high;
risk is high, because of competition;
tangible asset okay.
should use low debt (note: this is too brief, more details)

Pecking-order theory:
Dell does not have enough internal money (low profit margin)have to use external money if they have to
Use debt first. It is cheap for Dell to borrow because it is a large company.
Also, information asymmetry is lowif Dell ends up issuing additional stocks, it will not reduce its current stock price too much.

Give advice to the company in terms of its debt policyshould the company reduce its debt, issue more debt, or stay in the current situation, explain your answers in details.
Type your answers starting from here:

(3) Asset Utilization: Calculate and comment on the companys total asset turnover, NWC turnover, and fixed asset turnover. If you see any problem, suggest two ways through which the company could improve its asset utilization; if you dont see any problem, you dont need to make any suggestion.
Type your answers starting from here:

(4) Profitability: Calculate and comment on the companys gross profit margin, operating margin, and net profit margin. Suggest two ways through which the company could improve its profit margin, if necessary; if you think the companys current profit margin is fine, you can also suggest two ways to keep it or even further increase it
Type your answers starting from here:

Calculate and comment on the companys ROA and ROE.
Type your answers starting from here:

(5) Creditors decision: If you work for a bank and the company approaches you for debt financing. Which ratios are important considerations? Will you issue loans to the company? Why?
Type your answers starting from here:
Debt ratio: 80% very high
Interest coverage (Times interest earned and cash coverage ratio): 23 good, but be careful because debt ratio is very high
Short-term solvency: current & quick ratios good; DIR & conservative DIR very good.

(6) Shareholders decision: If you are a stock investor, which of the ratios you calculated above are important considerations? Would you consider buying the companys stock? Why? (here, just make decisions without looking at DCF valuation, just by looking at ratios)
Type your answers starting from here:
1. P/E ratio: 6.72 (S&P 500 is around 16) very low either low growth potential or undervalued, have to check with g.
2. Growth rate (g): 42% (if calculate g=retention ratio * ROA = 8.4%, medium to high growth)
Judging from PE and g, the company is undervalued!!! (but this undervaluation judgement is only based on PE)
3. ROE: 42% high, but extremely high risk from 80% debt ratio.
4. Net Profit Margin: too low
5. Dividend matters to some degree, esp. for income investors.

(7) Operating Cycle and Cash Cycle:
Discuss at least 5 ways through which your company could reduce its cash cycle. Please note that we discussed a complete list of ways to reduce cash cycle in class, but not all items would be applied to your companychoose the ones that will work for your company and discuss in details how each way could be achieved.
Type your answers starting from here:

(8) Short-Term Finance Policy
Should the company use a high or low level of current assets? Compare your answer with the companys current situation, what can you find?
Type your answers starting from here:
Growth potential: low if in PC, high in game computers & other innovative areas more cash & marketable securities
Cost of borrowing: low because it is a large company, but given 80% already, hard to raise additional debtmore cash & marketable securities
Account receivables: low in PC (mostly individual customers), if they do business service, extend creditrelatively high A/R, esp. business service
Inventory: carrying cost for inventory is high (electronics & computers, hard to handle); also their strategy on customized computersrelatively low inventory
Compare what they should have vs. what they actually have, consistent.

Should the company rely more on short-term debt or long-term debt? Compare your answer with the companys current situation, what can you find?
Type your answers starting from here:
PC: short term, short-term debt
Expansion to business service: need to do a lot of R&D, may need some long-term
Credit market condition: (1) relative short-term vs. long-term rate, (2) cost of carrying cash, (3) liquidity of commercial paper market, if very liquid, low rollover risk.

3. Valuation:
DCF Valuation: do the valuation on the spreadsheet, but you need to justify the following
(1) Key value drivers and your assumptions behind them: annual revenue growth rate, annual pre-tax operating margin, annual sales to capital ratio
Type your answers starting from here:

(2) Consider the risks of your businesswhat might change and how it would affect your valuation
Type your answers starting from here:

4. Valuation Enhancement:
From a CEO/CFOs perspective, understand your value driver and suggest how the company could increase its value
Type your answers starting from here:

5. Recommendation:
Make a recommendation (buy, sell, hold) of your stock
Your recommendations should be both from long-term investors perspective and short-term investors perspective
Type your answers starting from here: