Mini case study - Executive Fruit Financial planning model
Not what you're looking for?
George Hedderwick spent his morning developing a financial planning model for Executive Fruit (see Figure 18-2). Now he needed to run out the projections to 2007. In particular, he wanted to check what would happen if the firm continued to expand at 10 percent and relied on new issues of debt to make up any required external financing. Would the standard measures of leverage, such as the debt ratio and the interest cover start to spin out of control? Executive Fruit's bank had stipulated that the company's debt ratio should not exceed 60 percent, and George wanted to see whether there was any risk that this condition would be breached. It might be OK if interest rates stayed at their current level, but it looked as if the Fed could raise rates in the near future. George decided that he would also develop some projections assuming that the interest rate increased from 10 percent to 15 percent.
Although the CEO appeared reluctant to raise new equity, George thought he would take the opportunity to explore other financial strategies. In particular, he was interested to see how things might look if Executive Fruit kept to a constant 40 percent debt ratio. The return on equity might not look so good in this case, but it would certainly keep the company's bankers happy.
1. Determine what type and amount of financing may be required, taking into account the impact on leverage ratios
2. Determine the impact of rising interest rates on future performance
Purchase this Solution
Solution Summary
Illustrates in Excel format how to do the financial projections and answers other questions to solve the mini case.
Solution Preview
See the attached file for complete solution. The text here may not be copied exactly as some of the symbols / tables may not print. Thanks
Model Inputs Base Year
Income Statement 2002 2003 2004 2005 2006 2007 First Calculate
Growth Rate 0.1 Revenue 2,000 2,200.00 2,420.00 2662 2928.2 3221.02 1. This is simply last years revenue * (1+ growth rate)
Tax Rate 0.4 Cost of Goods Sold 1,800 1,980.00 2,178.00 2395.8 2635.38 2898.918 2. This is simply last years cost of goods sold * (1+ growth rate)
Interest Rate 0.1 EBIT 200 220 242 266.2 292.82 322.102 3. '=Revenue - Cost of goods sold
NWC Sales Ratio 0.1 Interest Expense 40 46.8 54.3 62.69 71.9298 82.152996 10. Long term debt*10%
Fixed Assets /Sales 0.4 Earnings Before Taxes 160 173.2 187.7 203.51 220.8902 239.949004 11. EBIT - interest
COGS/Sales 0.9 Taxes 64 69.3 75.1 81.404 88.35608 95.9796016 12. Earings before tax*40%
Payout Ratio two to three Net Income 96 103.9 112.6 122.106 132.53412 143.9694024 13. Earnings before tax - tax
Dividends 64 69.3 75.1 81.404 88.35608 95.9796016 15. 2/3 0f net income
Retained Earnings 32 34.6 37.5 40.702 44.17804 47.9898008 16. 1/3 of net income
Balane Sheet (Start of Year)
Assets Then calculate
Net Working Capital 200 220 242 266.2 292.82 322.102 4.'=NWC for last year*(1+growth rate)
Fixed Assets 800 880 968 1064.8 1171.28 1288.408 5.'=Fixed assets for last year*(1+growth rate)
Total Assets 1,000 1,100.00 1,210.00 1331 1464.1 1610.51 6.'=Total assets for last year*(1+growth rate)
Liabilities and Equity
Long Term Debt (note a) 400 468 543.4 626.9 719.298 821.52996 9. Use the balance for the debt so that the assets = liability
Shareholders' Equity (note b) 600 632 666.6 704.1 744.802 788.98004 8. Last year's equity + last year's retained earnings
Total Liab. & Share. Equity 1,000 1,100.00 1,210.00 1331 1464.1 1610.51 7. This is eqaul to total assets
Required External Financing 68 75.4 83.5 92.398 102.23196
Calculate Debt ...
Purchase this Solution
Free BrainMass Quizzes
Marketing Management Philosophies Quiz
A test on how well a student understands the basic assumptions of marketers on buyers that will form a basis of their marketing strategies.
Team Development Strategies
This quiz will assess your knowledge of team-building processes, learning styles, and leadership methods. Team development is essential to creating and maintaining high performing teams.
Basic Social Media Concepts
The quiz will test your knowledge on basic social media concepts.
Understanding Management
This quiz will help you understand the dimensions of employee diversity as well as how to manage a culturally diverse workforce.
Lean your Process
This quiz will help you understand the basic concepts of Lean.