FIN/571
1- Ratio Analysis Problems
Ratio Analysis
(Individual Assignment)
You may use excel or word.doc format for this assignment.
Please post your homework as a word.doc or excel file in the class discussion section below by the due date.
1.Analysis of cost of goods sold problem.
199219931994
Gross Profit Margin 60%55%51%
What is happening to cost of goods sold?As was done in the week 2 online lecture on ratio analysis, please assume sales of 1 dollar each year as you do your analysis.This problem follows the process shown in the Week 2 Ratio Analysis online lecture section titled: "Another Income Statement Analytical Approach: Percent of Sales"
(5 points)
2.Overhead (or Sales, General and Administrative Expense) problem.
199219931994
Gross Profit Margin 40%39%41%
Operating Margin (NOI/Sales) 15%10%5%
What is happening to S,G and A (or overhead expenses)?Please set up an illustration assuming sales of 1.00 dollar each year just as you did in problem number one.
(5 points)
3. Balance Sheet Problem
199219931994
Annual Sales Growth (over prior yr) + 1%0%+1%
Current Ratio3.5X2X1.2X
Average Collection Period25 days30 days55 days
What is happening to liquidity? Why?What are some follow-up questions your would ask? (5 points)
4.Using the data provided below, which is the better managed company?Why?Please support your answers by calculating appropriate ratios. (5 points)
Company A Company B
Sales10 million dollars20 million dollars
Net Income 1 million dollars2 million dollars
Total Assets10 million dollars 15 million dollars
2. Interpreting Financial Results
Each student should obtain 3 consecutive years of income statements and balance sheets from a company whose stock is traded on the New York Stock Exchange or NASDAQ Exchange. Two potential sources of information are the investor relations section of the firm's website or the EDGAR site which is run by the SEC. The EDGAR site contains the annual and quarterly financial statements of publicly traded companyies.
Calculate the following ratios for 3 years: Gross Profit Margin, Operating Profit Margin, Profit Margin After Taxes, Current Ratio, Acid Test or Quick Ratio, Average Collection Period (or Days Sales Outstanding), Debt Ratio and Return on Assets. Interpret those results against historical data and industry benchmarks.
Place the three years of income statements and balance sheets in a table in the back of your paper. Also create a table showing the 3 years of ratios and place it in the back of your paper.
Write a minimum 700-word summary of your analysis. Make sure to discuss your conclusions on each of the seven ratios listed above. You may use trends and, if available, industry averages.
3. GOAL OF THE ASSIGNMENT
Chapter 19 discusses how to do pro forma or forecasting of income statements and balance sheets. However, the discussion can be confusing. The goal of this assignment is to have students actually do a pro forma forecast and then apply a financial plan that the company has determined to the forecast.
Resources:
1. Chapter 19 in the text
2. My guidelines and tips for completing the week four Pro Forma assignment. (a word.doc file which is week four required material.
3. An excel spreadsheet showing an example of the assignment. This is a practice example. You can see how the calculations are done by referencing the cells in the spreadsheet. It is an excel file which is located in the week four required material.
4. The income statement and balance sheet data is found in the student materials part of the week four Analyzing Pro Forma Statements assignment. There are two files, one for the balance sheet and one for the income statement. These statements are to be considered the Current Year part of the analysis. You will be doing a forecast of the income statement and balance sheet that shall be titled Next Year Forecast.
Part 1a. (35 points) Complete a Pro Forma forecast of the income statement and balance sheet. Assume a five percent increase in sales from the current period to next year.
Set up your spreadsheet using the same format as is shown in the practice example in the excel file.
Part 1b. (5 points) Calculate the "Amount the Balance Sheet is Out of Balance".
Set up your calculation using the same format as is shown in part 1b of the practice example.
Part 2 (10 points) Financial Plan for Forecasted Year
The amount the balance sheet is out of balance represents forecasted cash that can be used by the company. The firm has decided to do two things with this forecasted money. First they want to pay off all long-term debt (both the current portion of the long-term debt and the long-term debt that is due beyond the next 12 months. These amounts are found in the forecasted balance sheet under the liabilities section.
Any remaining cash shall be used to increase the cash balance of the company.
Please prepare a spreadsheet layout just like the Part 2 layout in the excel practice example. As is shown in the practice example, show the breakdown of cash that will go to pay down the long term debt items (current portion and long term debt) and the amount of cash that will be added to the cash balance.
Next prepare a forecasted balance sheet with three columns as shown below:
Forecasted Balance Sheet Adjustments Forecasted Balance Sheet
Out of balance In Balance
You will know that part 2 is done correctly when you see the In Balance "Total Assets" equal the "Total Liabilities and Equity" amounts.
Please note that No Paper is required for this assignment. It should be done on an excel spreadsheet.
Please post your work as one spread sheet with Parts 1a, 1b and 2 clearly shown as separate sections. The format shown in the practice example should be followed in your posted work.
4. Discounted Cash Flows and WACC Homework Problems
The homework assignment is in the materials section. Please complete it using an excel spreadsheet. Practice exercises similar to the problems in this homework are found in the week five required section.
Discounted Cash Flows and WACC Homework Problems
Please post the answers (and show your work) in the assignments section by midnight the last day of the week assigned.
- Calculate the future value of 1,535 invested today for 8 years at 6 percent.
(5 points)
- What is the total present value of the following cash stream, discounted at 8 percent? (5 points)
Year Amount
1 400
2 750
3 945
4 145
5 78
3. If you invested $2,000 per year into an IRA for 30 years and received 6 percent return each year, what would the account balance be in 30 years? (5 points)
4. A friend gives you a proposition. If you give him 1,500 dollars today, he will guarantee your receive 12 percent a year for the next 5 years. How much money will you receive from him at the end of 5 years? (5 points)
5. You want to buy a new Computer Aided Design (CAD) system for your business. The cost of the system is $150,000 and you expect to save over $40,000 per year in reduced labor costs. Please calculate the net present value of the CAD if your required return is 10 percent and the life of the system is expected to be 5 years. (10 points)
6. Your company is considering converting its heating system in the main office from coal to heating oil. The initial cost of removing the coal fired furnace and installing an new oil fired unit is $60,000. The life of the analysis is 7 years. In the past you spent $25,000 per year on coal. The new company says you will spend no more than $15,000 per year on heating oil. If your required return is 12 percent, should you make this investment? Please calculate the net present value of this project. (10 points)
7. You have collected the following information:
a. the yield on your company’s preferred stock 8%
b. the yield on your company’s debt 10%
c. the required return on your company’s common stock and internal equity 12%
d. debt total $5,000,000
e. preferred stock current market value $10,000,000
f. common stock and retained earnings total value $20,000,000
Please calculate the pre-tax weighted average cost of capital (WACC) for your company.
(10 points)
8. Your company’s marginal income tax rate is 40%. Please calculate the post tax WACC from the information provided in problem 7. (10 points)
5. HOMEWORK
Please complete the homework problems found in the material on the right. These problems are very similar to the practice exercises located in the week six required section. These problems replace the Wiley Plus problems (which have a one point value). The Wiley Plus problem solutions are available on the internet so I have created similar ones whose answers are not found online.
Homework
Complete the following problems. You should do this work in excel. A set of ungraded practice problems similar to the homework is provided in the week six material to help you do this assignment.
Please post your answers in the classroom discussion section underneath the week six assignment.
1.Net Present Value: Johnson Complex Fabrications is a metal parts manufacturing company. It has developed a new process for producing extruded aluminum tubing. The process requires $1,968,450 initial investment. It expected to have a life of five years and would produce net cash inflows for each of the next five years: year 1 $512,496; year 2 $242,637; year 3 $814,558; year 4 $887,225 and year 5 $712,642.
What is the Net Present Value (NPV) if the discount rate is 15.9 percent? (10 points)
2. Free Cash Flow (FCF) and NPV for a project. Daniels Agricultural Products is considering buying a new farm that it plans to operate for ten years. The farm will require an initial investment of 12 million dollars. The investment will consist of 2 million dollars for land and 10 million dollars for trucks and other equipment. The land, all trucks and all other equipment is expected to be sold at the end of ten years for a price of five million dollars, which is two million dollars above book value. The farm is expected to produce revenue of 2 million dollars each year and annual cash flow from operations is projected to be 1.8 million dollars. The marginal tax rate is 35 percent and the company’s required return or discount rate is 10 percent. Calculate the NPV of this investment. (10 points)
3. Replace an existing asset: Davis Plumbing is considering updating its current manual accounting system with a high end electronic system. While the new accounting system would save the company money, the initial purchase cost of the system continues to decline. The company’s opportunity cost of capital or discount rate is 10 percent. The initial investment costs and annual savings made at different times in the future are shown below: (10 points)
Year Initial Investment Savings Per Year
0 5,000 7,000
1 4,500 7,000
2 4,000 7,000
3 3,600 7,000
4 3,300 7,000
5 3,100 7,000
When should Davis replace the system to have the highest present value of the NPV? (10 points)
4. Scenario analysis: Park City Boutique Brewery management forecasts that if the firm sells each case of Special Homebrewed for 20 dollars then the demand for the product will be 15,000 cases per year. If they raised the price per case ten percent then they expect demand to fall to 90 percent of the expected sales at the current price of 20 dollars per case. The firm’s variable cost per case is ten dollars and the total fixed cash costs for the year are 100,000 dollars. Depreciation and amortization charges are 20,000 dollars and the firm has a 30 percent marginal tax rate. Management anticipates it will need to increase working capital by 3,000 dollars for the year, regardless of the pricing of the product. What will be the effect of the price increase on the firm’s free cash flow for the year? (10 points)
5. WACC for a firm: Acme Co. has a capital structure, based on current market values, that consists of 50 percent debt, 10 percent preferred stock and 40 percent common stock. If the returns required by investors are 8 percent for the debt, 10 percent for the preferred stock and 15 percent for the common stock, what is Acme’s after tax weighted average cost of capital. Assume that the firm’s marginal tax rate is 40 percent. (10 points)