Question 1
WACC, Forecasting Financial Statements, FCF, Corporate Valuation Model It is 2010 and you are working in the finance department of BioTrend Industries. The CFO of the company knows that you just earned an MBA from Radford University, which is known for its rigorous education in Finance; hence, he asks you to determine the intrinsic value of the company alongside an assessment if the stock is currently undervalued or overvalued. Assume that the company is still in the non-growth stage, but that cash flows are expected to grow at a constant rate of 7% beginning in 2011. You start immediately with the collection of all relevant data, which are listed below: Financial statements for 2010 (see spreadsheet) The company's historical sales: Year Sales 2005 95,215 2006 143,901 2007 207,252 2008 298,065 2009 390,692 2010 455,150 2011 Assume interest rate on short-term securities: 3% Assume interest rate for short-term debt: 5% Assume interest rate on long-term debt: 8% No interest is earned on cash. Tax rate: 35% Dividends growth rate: 20% The industry average quick ratio: 2.0 The industry average debt ratio: 35% debt Target Capital structure: 40% debt and 60% common equity Bond Information: If the company issues new bonds it would do so by issuing bonds with 20 years to maturity. The company has right now semiannual coupon bonds with a 9% coupon payment outstanding. The bonds were issued 10 years ago with 30 years to maturity and sell currently for 1048. The flotation cost is assumed to be 3% of par value (remember par value is always $1,000). Stock Information: The risk free rate (Rf) is right now 3% and the return on the market 13%. The company?s beta is 0.9. The company has 5 million shares outstanding (5,000 in thousands). The current market value of the stock is $60. Use the provided spreadsheet to calculate the following (note, numbers are in thousands): A) The sales for 2011 (based on a regression equation) B) The pro-format statements for 2011 - The company needs to increase fixed assets to support sales - (Use the beginning of year debt balances to calculate net interest expense) C) The FCF for 2011 D) The WACC E) Intrinsic Value per share F) Market value added (MVA) a. Based on the actual market price b. Based on a. Assuming the historical trend continues, what will sales be in 2010? Base your forecast on a spreadsheet regression analysis of the 2004-2009 sales data above, and include the summary output of the regression in your answer. By what percentage are sales predicted to increase in 2010 over 2009? Is the sales growth rate increasing or decreasing? Here are the company's historical sales. Hint: Use the Trend function to forecast sales for 2010. Year Sales Growth Rate 2004 129,215,000 2005 180,901,000 40.0% 2006 235,252,000 30.0% 2007 294,065,000 25.0% 2008 396,692,000 34.9% 2009 455,150,000 14.7% 2010 515,465,267 % Increase in Predicted Sales for 2010 over 2009: 2009 Sales 455,150,000 2010 Sales 515,465,267 % increase 13.25% Note: This growth rate has been declining over time. b. Cumberland?s management believes that the firm will actually experience a 20 percent increase in sales during 2010. Construct 2010 pro forma financial statements. Cumberland will not issue any new stock or long-term bonds. Assume Cumberland will carry forward its current amounts of short-term investments and notes payable, prior to calculating AFN. Assume that any Additional Funds Needed (AFN) will be raised as notes payable (if AFN is negative, Cumberland will purchase additional short-term investments). Use an interest rate of 9 percent for short-term debt (and for the interest income on short-term investments) and a rate of 11 percent for long-term debt. No interest is earned on cash. Use the beginning of year debt balances to calculate net interest expense. Assume that dividends grow at an 8 percent rate. Key Input Data: Used in the forecast Tax rate 40% Dividend growth rate 8% S-T rd 9% L-T rd 11% December 31 Income Statements: (in thousands of dollars) Forecasting 2009 2010 2010 2009 basis Ratios Inputs Forecast Sales $455,150 Growth Expenses (excluding depr. & amort.) $386,878 % of sales EBITDA $68,273 Depreciation and Amortization $7,388 % of fixed assets EBIT $60,885 Net Interest Expense $8,575 Interest rate x beginning of year debt EBT $52,310 Taxes (40%) $20,924 Net Income $31,386 Common dividends $12,554 Growth Addition to retained earnings (DRE) $18,832 Cumberland Industries December 31 Balance Sheets (in thousands of dollars) Forecasting 2009 2010 2010 2009 basis Ratios Inputs Without AFN AFN With AFN Assets: Cash and cash equivalents $91,450 % of sales = Short-term investments $11,400 Previous Accounts Receivable $103,365 % of sales Inventories $38,444 % of sales Total current assets $244,659 Fixed assets $67,165 % of sales Total assets $311,824 Liabilities and equity Accounts payable $30,761 % of sales Accruals $30,477 % of sales Notes payable $16,717 Previous Total current liabilities $77,955 Long-term debt $76,264 Previous Total liabilities $154,219 Common stock $100,000 Previous Retained Earnings $57,605 Previous + DRE Total common equity $157,605 Total liabilities and equity $311,824 Required assets = Specified sources of financing = Additional funds needed (AFN) = Required additional notes payable = Additional short-term investments = c. Now create a graph depicting the sensitivity of AFN for the coming year to the sales growth rate. To make this graph, compare the AFN at sales growth rates of 5%, 10%, 15%, 20%, 25%, and 30%. We can use a data table to answer this question: Sales 2010 AFN Growth rate $0 5% 10% 15% 20% 25% 30% d. Calculate the Net Operating Working Capital (NOWC), Total Operating Capital, and NOPAT for 2009 and 2010. Also, calculate the FCF for 2010. Net Operating Working Capital NOWC09 = Operating CA - Operating CL = - = NOWC10 = Operating CA - Operating CL = - = Total Operating Capital TOC09 = NOWC + Fixed assets = + = TOC10 = NOWC + Fixed assets = + = Net Operating Profit After Taxes NOPAT09 = EBIT x ( 1 - T ) = x = NOPAT10 = EBIT x ( 1 - T ) = x = Free Cash Flow FCF10 = NOPAT - Increase in TOC = - = e. Suppose Cumberland can reduce its inventory to sales ratio to 5 percent and its cost to sales ratio to 83 percent. What happens to AFN and FCF? Input Base Case New Scenario Inv. / Sales 0.0% 5.0% Note: we used the Scenario Manager. Costs / Sales 0.0% 83.0% FCF AFN,Hello, how is it going? I have this that I have added if it helps you complete it tonight. (although some of it might not be correct) Also, I understand if this is kinda rushing it for a big assignment. I will give additional money if it is completed tonight. Thank You,Hello? Are you there? Have not received an update for some time.,Thank you Rachel!,Hey,I have completed a large portion of the homework, if you could look just look over it and make sure it is correct. Also, I am not sure if I sent you my copy that I worked on so I will try again.,Did you receive the part I had worked on? Will you be able to complete this assignment tonight?,I don't think this is correct
Question 2
All of these questions need to be done in Excel PROBLEM # 1: You have just turned 22, and you intend to start saving for your retirement. You plan to retire in 40 years when you turn 62. During your retirement you would like to have an annual income of $140,000 per year for the next 30 years (until age 92). Calculate how much you would have to save annually between now and age 62 in order to finance your retirement income. Make the following assumptions: ? Assume that the relevant compounded interest rate is 9 percent per year. ? You make the first payment today and the last payment on the day of your turn 62. ? You make the first withdrawal when you turn 62 and the last withdrawal when you turn 92. PROBLEM # 2: You are offered an asset that costs $8,000 and has cash flows of $500 every three month (end of period) of the next 10 years. a) If your cost of capital is 8 percent, should you purchase it? b) What is the IRR of the asset? c) What is the NPV of the asset? (Setup cash flows in Excel spreadsheets and use the following Excel Financial functions, IRR and NPV, to derive your answers. PROBLEM # 3: You just took a $50,000, eight-year loan. Payments at the end of each year are flat (equal in every year) at an interest rate of 8 percent. Calculate the appropriate loan table, showing the breakdown in each year between principal and interest. PROBLEM # 4: Use Excel to construct an amortization table for the following mortgage. In the amortization table, provide all the information listed below. (Assuming interest is compounded monthly and payments are due at the end of the month). For a 10-year variable-rate-level-payment mortgage (VRM) of $480,000 with the following mortgage rates: Years 1-2: 4.20%, Years 3-5: 5.45%, Years 6-10: 6.75% Compute and illustrate the following in an amortization table: ? Monthly Payment of the mortgage. ? Mortgage Balance Remaining at the end of each month (Total 120 months) ? Principal Repayment for each month. ? Interest Expenses for each month and the life of the loan.",Solution 4 is incorrect! The loan balance is suppose to be 0 at the end of the 10yrs. The monthly payment for the first 2 years is suppose to be $4663.73, the payment for years 3-5 is $5,088.56, and the payment for the last 5 years was suppose to be $5,195.92. Also you did solution 3 incorrect, even if you used a loan calculator online, you can find the correct answer and see that your solution was wrong.
Question 3
Homework Assignment: ? Word-process your solutions within this template. Do not create a new file. ? Show all steps used in arriving at the final answers. Incomplete solutions will receive partial credit. ? If needed: Word-process formulas using Equation Editor and diagrams using Drawing Tool. 1. A manufacturer of microwaves gathers information on microwave sales from a random sample of 50 stores in a large retail chain. In the sample, the mean number of microwaves sold per store last week was 19, with standard deviation 2. What is the 95% confidence interval for the mean number of microwaves sold at a retail store in the chain last week? 2. Annual family medical expenses for the families of a random sample of ten employees of a company are given below. You can assume the family medical expenses for the employees of the company are normally distributed. $2450, $1390, $2550, $2140, $1690, $2600, $2250, $2330, $2490, $1810 Compute the 95% confidence interval for the mean family dental expenses of all employees of the company. 3. If many samples of size 30 (that is, each sample consists of 30 items) were taken from a large normal population with a mean of 36 and variance of 10, what would be the sample standard deviation? 4. The average height of American woman is distributed normally, with a mean of 63.5 inches and a standard deviation 2.5 inches. Approximately what percentage of American women are taller than 58.5 inches, but shorter than 68.5 inches? 5. A company?s average accounts receivable per customer is $132.54. After a problem in the automated accounting software is suspected, a random sample of 38 accounts reveals a sample mean of $143.55 and a sample standard deviation of $38.29. In a two-sided hypothesis test to see if the average accounts receivable has changed, the best formulation of the null hypothesis is:
Question 4
Ok, hi again! Can you please answer these 3 questions for me including the solutions that were done to get the correct answer? Thank you so much in advance! 1. You were recently hired by Scheuer Media Inc. to estimate its cost of common equity. You obtained the following data: D1 = $1.75; P0 = $42.50; g = 7.00% (constant); and F = 5.00%. What is the cost of equity raised by selling new common stock? a. 10.77% b. 11.33% c. 11.90% d. 12.50% e. 13.12% 2. Sapp Trucking's balance sheet shows a total of noncallable $45 million long-term debt with a coupon rate of 7.00% and a yield to maturity of 6.00%. This debt currently has a market value of $50 million. The balance sheet also shows that the company has 10 million shares of common stock, and the book value of the common equity (common stock plus retained earnings) is $65 million. The current stock price is $22.50 per share; stockholders' required return, rs, is 14.00%; and the firm's tax rate is 40%. The CFO thinks the WACC should be based on market value weights, but the president thinks book weights are more appropriate. What is the difference between these two WACCs? a. 1.55% b. 1.72% c. 1.91% d. 2.13% e. 2.36% 3. Yonan Inc. is considering Projects S and L, whose cash flows are shown below. These projects are mutually exclusive, equally risky, and not repeatable. If the decision is made by choosing the project with the shorter payback, some value may be forgone. How much value will be lost in this instance? Note that under some conditions choosing projects on the basis of the shorter payback will not cause value to be lost. WACC: 10.25% Year 0 1 2 3 4 CFS ?$ 950 $500 $800 $ 0 $ 0 CFL ?$2,100 $400 $800 $800 $1,000 a. $24.14 b. $26.82 c. $29.80 d. $33.11 e. $36.42,Thank you!
Question 5
Using the accrual basis of accounting, prepare Majestic Caterers? Income Statement and Balance Sheet for the year ended December 31, 2008.,Okay, thanks so much. The previous question i posted (Eagle Impairment Case 10-2) I completed but needed a second opinion by tomorrow so I couldn't extend the deadline for the hrs. requested. Would you also be interested in looking at the problem as well?,This is the question to go along with my answer in the attachment I just sent,Okay, thanks so much.,I just read your recent message thread. It's okay, and thank you for your notice. I have a limited account, and noticed that the answer I am looking for is in documents on this site. Is it possible that you could send it to me from here because I don't have document access? It is called Trueblood Case 10-2 Eagle Impairment,Thank you,No, Thank you it really means a lot to me. I have re-posted it in a new thread.,Hi, I recieved an email from you saying to increase my assignment (Eagle Impairment) time to 24hrs. I only have until 3pm today. Could you please view the documents from this site where my problem is already answered so that I can view their response and compare it to my own? I have a limited account and cannot access documents. -Mariah I
