Chapter 4 - Financial Modeling - Template
xlsx
School
Iowa State University *
*We aren’t endorsed by this school
Course
301
Subject
Finance
Date
Feb 20, 2024
Type
xlsx
Pages
1
Uploaded by BarristerArt6670
FIN 301 Financial Modeling
Phum Uengaksonsamai
Scenarios for Next Year
Current
Worst
Most Likely
Best
Period
Case
Case
Case Assumptions
Sales Growth
7.0%
14.0%
21.0%
Costs
Grow at the rate of Sales Growth
Assignment Instructions:
Interest Expense
Will remain constant
1) Using the Assumptions provided in the attached Excel spreadsheet, complete income Statement Assets & Current Liabilities
Grow at the rate of Sales Growth
and Balance Sheet projections for the Worst, Most Likely and Best Case Scenarios
Long-Term Debt
Will remain constant
2) Complete all financial calculations corresponding with the "light blue" highlighted cells including:
Equity
Will fluctuate with Financing needs
a) What are the external financing needs at each scenario?
Dividend & Plowback Ratios
To remain at Current Period levels
b) What is the current plowback and dividend payout percent?
Taxes
21.0%
21.0%
21.0%
c) What is the company's Return on Assets in the Current Period?
d) What is the company's Return on Equity in the Current Period?
Income Statement
e) What is the company's Internal Growth Rate for the Current Period and all Scenarios?
Sales
395,675 423,372 451,070 478,767 f) What is the company's Sustainable Growth Rate for the Current Period and all Scenarios?
Costs
315,340 337,414 359,488 381,561 EBIT
80,335 85,958 91,582 97,205 Interest Expense
11,175 11,175 11,175 11,175 Taxable Income
69,160 74,783 80,407 86,030 Taxes
14,524 15,705 16,885 18,066 Net Income
54,636 59,079 63,521 67,964 Profit Margin %
13.8%
14.0%
14.1%
14.2%
Dividends 19,123 20,678 22,233 23,787 Additions to Retained Earnings
35,514 38,401 41,289 44,177 Assets
Total Current Assets
87,500 93,625 99,750 105,875 Net Fixed Assets
240,750 257,603 274,455 291,308 Total Assets
328,250 351,228 374,205 397,183 Liabilities
Current Liabilities
21,900 23,433 24,966 26,499 Long-Term Debt
137,350 137,350 137,350 137,350 Total Liabilities
159,250 160,783 162,316 163,849 Equity
169,000 190,445 211,889 233,334 Total Liabilities & Equity
328,250 351,228 374,205 397,183 External Financing Need
(16,957) 1,600 20,157 Calculation: Projected Equity - Beginning Equity - Additions to Retained Earnings
Dividend Payout Ratio
35%
35%
35%
35%
Calculation: Dividends / Net Income
Retention or Plowback Ratio
65%
65%
65%
65%
Calculation: 1 - Dividend Payout Ratio
Return on Assets
16.6%
16.8%
17.0%
17.1%
Calculation: Net Income / Total Assets
Return on Equity
32.3%
31.0%
30.0%
29.1%
Calculation: Net Income / Total Equity
Internal Growth Rate %
12.1%
12.3%
12.4%
12.5%
Calculation: (ROA% x Retention Ratio) / (1-(ROA% x Retention Ratio)
Sustainable Growth Rate%
26.6%
25.3%
24.2%
23.4%
Calculation: (ROE% x Retention Ratio) / (1-(ROE% x Retention Ratio)
Discover more documents: Sign up today!
Unlock a world of knowledge! Explore tailored content for a richer learning experience. Here's what you'll get:
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Documents
Related Questions
Please Solve In 20mins
arrow_forward
Assume the following ratios are constant please provide this question solution accounting
arrow_forward
Financial Accounting
arrow_forward
Assume the following ratios areconstant: Total asset turnover 2.8Profit margin 6.8 % Equitymultiplier 2 Payout ratio 30 %What is the sustainable growthrate? (Do not round intermediate
Training
calc
arrow_forward
Kindly help me Accounting question
arrow_forward
Assume the following ratios are constant:
Total asset
turnover
Profit margin
Equity multiplier
Payout ratio
2.5
6.5%
1.6
20%
What is the sustainable growth rate? (Do not round intermediate calculations and enter
your answer as a percent rounded to 2 decimal places, e.g., 32.16.)
Sustainable growth rate
%
arrow_forward
PART ONE
Assume that you have the following investment
alternatives are at your disposal, shown with their
probabilities and associated outcomes.
Condition of economy Probability Treasury Bill Nyundo Ltd Mwila Ltd
Recession
0.1
8.0%
-22%
10%
Below average
0.2
8.0%
-2%
-10%
Average
0.4
8.0%
20%
7.0%
Above average
0.2
8.0%
35%
45%
Boom
0.1
8.0%
50%
30%
Required:
1. Compute the correlation coefficient.
2. Construct the security market line.
Fill in your answers ONLY in this table. Round answers to 2 decimal places.
This table must be submitted (any student who doesn't submit this table with
only answers rounded to 2 decimal places will get zero)
The Risk Free Rate is 0.5% for every state of the economy.
Probability
Stock A
Stock B
Market Portfolio
Recession
0.1
-22%
28%
-13%
Below average
0.2
-2%
14.7%
1%
Average
0.4
20%
0%
15%
Above average
0.2
35%
-10%
29%
Boom
0.1
50%
-20%
43%
ER
?
1.74%
15%
Variance ()
401.44
?
235.20
CV
?
?
?
Beta (ẞ)
1.3
-0.87
?
S Measure
?
?
?
T Measure
?
?
?…
arrow_forward
Assume the following ratios are constant.
Total asset turnover
Profit margin
Equity multiplier
Payout ration
2.29
Sustainable growth rate
5.7%
1.76
36%
What is the sustainable growth rate?
Note: Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.
arrow_forward
Assume the following ratios are constant:
Profit margin
Total asset turnover
2.5
5.2%
Equity multiplier
1.3
Payout ratio
22%
What is the sustainable growth rate?
Note: Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g.,
Answer is complete but not entirely correct.
Sustainable growth rate
1.09
%
arrow_forward
Provide answers
arrow_forward
NO WRONG ANSWER
arrow_forward
Please give me true answer this financial accounting question
arrow_forward
Hi expert please give me answer general accounting question
arrow_forward
Please try to give correct answer this financial accounting question
arrow_forward
Need answer the accounting question not use ai
arrow_forward
Calculate the expected return on the asset on these financial accounting question
arrow_forward
The following table lists possible rates of return on Company A and B.
State of the Economy
Probability
Company A
Deep recession
0.05
-20%
Mild recession
Average
Mild boom
Strong boom
ii.
0.25
0.35
0.20
0.15
iii.
0
10
15
30
(a) Based on the above data calculate by using the appropriate formulae
the standard deviations of returns for Company A and B
i.
the covariance of returns between Company A and B
Company B
-40%
the correlation between Company A and B
10
0
25
30
(b) If you wish to diversify risk would it be advisable to form a portfolio of both
securities A and B? State your reasons. (No computations are required to answer
this part of the question.)
(c) Find the minimum variance one can get by forming a portfolio of A and B. Short-
selling either stock is allowed - i.e., weights need not be all positive.
arrow_forward
Financial Accounting Question please solve
arrow_forward
Considering the following information gathered:
State of Economy
Probability of State of Economy
Rate of Return if State Occurs
Recession
0.11
-0.03
Normal
0.45
0.16
Boom
0.44
0.29
Please Calculate the expected return.
Multiple Choice
18.65%
2.80%
19.63%
20.61%
20.42%
arrow_forward
The expected market return and risk for different
assumptions about the state of the economy is
shown below.
State of Economy Probability of State Expected
Market Return
Fast growth 0.11 35%
Slow growth 0.50 16%
No growth 0.20 5%
Recession 0.15 -22%
Depression 0.04 -32%
Compute the expected return and standard
deviation.
Note: Round your answers to 2 decimal places.
Compute the expected return and risk for the
following 2 scenarios:
Note: Round your answers to 2 decimal places.
Scenario 1:
State of Economy Probability of State Expected
Market Return
Fast growth 0.09 38%
Slow growth 0.33 16%
No growth 0.36 2%
Recession 0.19-18%
Depression 0.03 -32%
Scenario 2:
State of Economy Probability of State Expected
Market Return
Fast growth 0.16 40%
Slow growth 0.31 13%
No growth 0.38 2%
Recession 0.12-17%
Depression 0.03 -33%
arrow_forward
Computer the expected return given these three economic states, their likelihoods, and the potential returns.
Economic State Probability Return
Fast Growth 0.40 25%
Slow Growth 0.55 12%
Recession 0.05 -50%
Multiple Choice
___ -4.3 percent
___ 14.1 percent
___ 19.1 percent
___ 29.0 percent
arrow_forward
go.3
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you

Related Questions
- Assume the following ratios areconstant: Total asset turnover 2.8Profit margin 6.8 % Equitymultiplier 2 Payout ratio 30 %What is the sustainable growthrate? (Do not round intermediate Training calcarrow_forwardKindly help me Accounting questionarrow_forwardAssume the following ratios are constant: Total asset turnover Profit margin Equity multiplier Payout ratio 2.5 6.5% 1.6 20% What is the sustainable growth rate? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.) Sustainable growth rate %arrow_forward
- PART ONE Assume that you have the following investment alternatives are at your disposal, shown with their probabilities and associated outcomes. Condition of economy Probability Treasury Bill Nyundo Ltd Mwila Ltd Recession 0.1 8.0% -22% 10% Below average 0.2 8.0% -2% -10% Average 0.4 8.0% 20% 7.0% Above average 0.2 8.0% 35% 45% Boom 0.1 8.0% 50% 30% Required: 1. Compute the correlation coefficient. 2. Construct the security market line. Fill in your answers ONLY in this table. Round answers to 2 decimal places. This table must be submitted (any student who doesn't submit this table with only answers rounded to 2 decimal places will get zero) The Risk Free Rate is 0.5% for every state of the economy. Probability Stock A Stock B Market Portfolio Recession 0.1 -22% 28% -13% Below average 0.2 -2% 14.7% 1% Average 0.4 20% 0% 15% Above average 0.2 35% -10% 29% Boom 0.1 50% -20% 43% ER ? 1.74% 15% Variance () 401.44 ? 235.20 CV ? ? ? Beta (ẞ) 1.3 -0.87 ? S Measure ? ? ? T Measure ? ? ?…arrow_forwardAssume the following ratios are constant. Total asset turnover Profit margin Equity multiplier Payout ration 2.29 Sustainable growth rate 5.7% 1.76 36% What is the sustainable growth rate? Note: Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.arrow_forwardAssume the following ratios are constant: Profit margin Total asset turnover 2.5 5.2% Equity multiplier 1.3 Payout ratio 22% What is the sustainable growth rate? Note: Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., Answer is complete but not entirely correct. Sustainable growth rate 1.09 %arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
