Economics AFN Solutions

Developing Pro Forma Financial Statements
1) Develop a sales forecast.
2) Based on forecasted sales, create a pro forma income statement.
3) Develop a preliminary forecasted balance sheet. In doing so, you must predict how the forecasted change in sales will affect the need
for additional assets (both current and fixed assets). Additionally, will changes in sales precipitate any additional liabilities? This must also be incorporated
into the balance sheet forecast. Finally, leave capital accounts (i.e., equity and long-term debt) unchanged.
4) After the first 3 steps, you will notice that this pro forma balance sheet will be unbalanced (i.e., assets not equal to liabilities plus shareholders equity).
5) The gap in the balance sheet may be eliminated by using one or a combination of plugs:
a. incorporating additional (or subtracting) financing (i.e., assuming more or less debt OR more or less equity) into the balance sheet forecast.
If you take this approach, you should also adjust for any financing feedbacks.
Or if you assume less equity, will the firm pay less in dividends?
b. Adjusting cash as necessary.
c. A combination of plugs.
6) Management assesses the plan, and may call for changes.
An additional way to forecast would be to analyze the historical changes in assets as sales grow (or decline). This would best be accomplished through a regression methodology.
Style’s Pro Forma Financial Statements
Interest rate on new debt
Let’s assume
Income Statement:
Sales
Op costs
2020
10%
Assumed growth rate in (Sales, Op. Costs,
Cash, A/R, Inv, Net PPE, A/P, Accruals)
15%
Dividend payout ratio
45%
Pro forma
2019
2020
Adjusted
$
36,000 $
41,400 $ 41,400
$
32,440 $
37,306 $ 37,306
2020
Initially keep Interest = Constant and N/P = Constant
LT Debt =Constant
Equity = Constant
Let’s forecast
2020
DEMONSTRATE HOW GROWTH HAS FINANCIAL IMPLICATIONS
line items
Q1. What changed by the same percentage as Sales?
1
EBIT
Interest
$
$
3,560
560
$
$
4,094
560
$
$
4,094
560
2
3
EBT
Taxes (40%)
$
$
3,000
1,200
$
$
3,450
1,413.6
$
$
3,534
1,414
4
Q2. What are the funding sources?
Net inc
$
1,800
$
4,864
$
2,120
Divs (45%)
Add to RE
$
$
810
990
$
$
365
1,139
$
$
954
1,166
Balance Sheet:
Assets:
Cash
A/R
Inv
Cur assets
Net PPE
Total assets
Transfer cells Ds to Es
1
2
$
$
$
1,080
6,480
9,000
$
$
$
1,242
7,452
10,350
$
$
$
1,242
7,452
10,350
$
$
$
16,560
12,600
29,160
$
$
$
19,044
14,490
33,534
$
$
$
19,044
14,490
33,534
(and therefore into equation d)), we can break this down into 2 equations and 2 unknowns (AFN and addition to R/E).
Ultimately, we can define AFN (incorporating financing feedbacks) as:
AFN =
g ( A * ? L*) ? ( EBIT )(1 + g )(1 ? T )(1 ? d ) + I 0 (1 ? T )(1 ? d )
1 ? i (1 ? T )(1 ? d )
Where
A* = assets that increase proportionally with sales
L* = liabilities that increase proportionally with sales
2019
2020
Adjusted
4,320
2,880
2,100
$
$
$
4,968
3,312
2,100
$
$
$
4,968
3,312
25,404
Cur liabilities
LT debt
Common stock
RE
$
$
$
$
9,300
3,500
3,500
12,860
$
10,230
$
$
$
$
33,684
14,026
Total L&E
$
29,160
$
10,230
$
47,710
$
23,304
g = growth rate in sales
EBIT = operating income
T = tax rate
d = dividend payout ratio
I0 = interest expense (ignoring any additional financing)
i = interest rate on additional funds borrowed
AFN
Assets – L&E
$
(14,176)
NOTES:Use Tools Solver (add-in) to set Difference cell equal to 0
by changing the AFN cell. Note that total interest relies
on the amount of AFN, thus impacting addition to RE.
3.51%
6.44%
78.24%
NI
)
TA
NI
1 ? b(
)
TA
b(
Internal Growth rate
Ratio of Liabilities to Equity
4
Q3. HOW CAN WE ACCOUNT FOR ADDED INTEREST?
1) Estimate the following system of equations (NOTE: the following process assumes that all new funds are borrowed at the beginning of the yr)
a) Additional interest = additional funds borrowed (AFN) x cost of new borrowing
b) (1+g) x Total assets = (1+g) x (A/P + Accruals) + (N/P + AFN) + LT debt + common stock + (R/E + Addition to R/E)
c) NI = (Sales Operating costs) x (1-T) Interest x (1-T)
d) Addition to R/E = NI x (1 dividend payout ratio)
In equation a), additional interest depends upon AFN (unknown). In equation b), we have 2 unknown variables, AFN and addition
to R/E. Eq. d) shows that addition to R/E depends on NI (which is affected by how much interest is paid. So, substituting equation a) into c)
Liabilities & Equity:
A/P
$
Accruals
$
N/P
$
Internal growth rate:
Sustainable growth rate:
3
#DIV/0!
Sustainable Growth Rate
b( ROE )
1 ? b( ROE )
Style’s Pro Forma Financial Statements
Interest rate on new debt
10%
Assumed growth rate
6.44%
Dividend payout ratio
45%
Rate of equity increase
6.51%
Pro forma
Income Statement:
2019
2020
Adjusted
Sales
$ 36,000 $ 38,319 $ 38,319
Op costs
$ 32,440 $ 34,529 $ 34,529
EBIT
Interest
$
$
3,560 $
560 $
3,789 $
560 $
3,789
560
EBT
Taxes (40%)
$
$
3,000 $
1,200 $
3,229 $
1,292 $
3,229
1,292
Net inc
$
1,800 $
1,938 $
1,938
Divs (45%)
Add to RE
$
$
810 $ 871.91 $
990 $ 1,066 $
872
1,066
Balance Sheet:
Assets:
Cash
A/R
Inv
Cur assets
Net PPE
Total assets
2019
$
$
$
$
$
$
Liabilities & Equity:
A/P
$
Accruals
$
N/P
$
Cur liabilities
LT debt
Common stock
RE
Total L&E
AFN
Assets – L&E
$
$
$
$
$
2020
1,080 $
6,480 $
9,000 $
HOW MUCH ASSETS CAN GROW BY MAINTAINING A CONSTANT DEBT TO EQUITY FINANCING MIX?
The firm will borrow ST and LT Debt to keep a constant Liabilities/Equity ratio
Lets’ forecast 2015 line items
Adjusted
1,150 $
6,897 $
9,580 $
1,150
6,897
9,580
16,560 $ 17,627 $ 17,627
12,600
$
29,160 $ 17,627 $ 17,627
4,320 $
2,880 $
2,100 $
9,300
3,500
3,500
12,860
29,160
4,598 $ 4,598
3,066 $ 3,066
2,235 $ (11,634)
$ 9,899
$ 3,725
$ 3,500
$ 13,926
$ 31,050
$ (3,970)
$ 3,725
$ 3,500
$ 13,926
$ 17,181
$ (13,869)
Additional interest-bearing liabilities
Additional liabilities
Ratio of
liabilities to
equity
78.24%
$
446
$ (13,509)
$ (13,045)
-1224.09% Sustainable growth rate allows the user to ascertain how much assets can grow while still maintaining a stable debt ratio.
Style’s Pro Forma Financial Statements
Interest rate on new debt
Assumed growth rate
Dividend payout ratio
Pro forma
Income Statement:
2019
2020
Sales
$ 36,000 $
41,400
Op costs
$ 32,440 $
37,306
EBIT
Interest
$
$
3,560
560
$
$
4,094
644
EBT
Taxes (40%)
Net inc
$
$
$
3,000
1,200
1,800
$
$
$
3,450
1,380
2,070
Divs (45%)
Add to RE
$
$
810
990
$
$
932
1,139
Balance Sheet:
Assets:
Cash
A/R
Inv
Cur assets
Net PPE
Total assets
2019
$
$
$
1,080
6,480
9,000
$
$
$
1,242
7,452
10,350
$
$
$
16,560
12,600
29,160
$
$
$
19,044
14,490
33,534
4,320
2,880
2,100
$
$
$
4,320
2,880
2,100
9,300
3,500
3,500
12,860
29,160
$
$
$
$
$
9,300
3,500
3,500
13,999
30,299
$
3,236
Liabilities & Equity:
A/P
$
Accruals
$
N/P
$
Cur liabilities
LT debt
Common stock
RE
Total L&E
AFN
2020
$
$
$
$
$
10%
15.00%
45%
HOW MUCH ASSETS CAN GROW IF FIRM HAS LIMITED ACCESS TO CAPITAL AND IT WILL USE ONLY
INTERNALLY GENERATED FUNDS?
In this case, we assume the growth rate is 15% and we will keep A/P,Accruals, N/P, LT Debt, Equity constant.
Modified example – Revised IGR
Style’s Pro Forma Financial Statements
Interest rate on new debt
Assumed growth rate
Dividend payout ratio
Pro forma
Income Statement:
2019
2020
Sales
$ 36,000 $ 37,809
Op costs
$ 32,440 $ 34,070
10%
5.02%
45%
HOW MUCH ASSETS CAN GROW IF I ALLOW FOR CERTAIN WORKING CAPITAL
LIABILITIES TO GROW SPONTANEOUSLY?
We allow A/P and Accruals to grow spontaneously and
Cash does not grow
EBIT
Interest
$
$
3,560 $
560 $
3,739
560
Use Revised IGR formula
EBT
Taxes (40%)
$
$
3,000 $
1,200 $
3,179
1,272
Revised IGR
Net inc
$
1,800 $
1,907
Divs (45%)
Add to RE
$
$
810 $
990 $
858
1,049
Balance Sheet:
Assets:
Cash
A/R
Inv
2019
2020
$
$
$
1,080 $
6,480 $
9,000 $
1,080
6,806
9,452
$
$
$
16,560 $
12,600 $
29,160 $
17,338
13,233
30,571
Liabilities & Equity:
A/P
$
Accruals
$
N/P
$
4,320 $
2,880 $
2,100 $
4,537
3,025
2,100
Cur assets
Net PPE
Total assets
Cur liabilities
LT debt
Common stock
RE
Total L&E
$
$
$
$
$
9,300
3,500
3,500
12,860
29,160
AFN
Assets – L&E
Internal growth rate:
5.02%
$
$
$
$
$
9,662
3,500
3,500
13,909
30,571
$
–
(1 ? d ) * NI
( A * ? L*) ? EBIT * (1 ? T )(1 ? d )
Style’s Pro Forma Financial Statements
Interest rate on new debt
10%
Assumed growth rate
15.0%
Dividend payout ratio
45%
Pro forma
Income Statement:
2019
2020
2021
2022
2023
Sales
$
36,000 $
41,400 $ 47,610 $ 54,752 $ 62,964
Op costs
$
32,440 $
37,306 $ 42,902 $ 49,337 $ 56,738
EBIT
Interest
$
$
3,560 $
560 $
4,094 $ 4,708 $ 5,414 $ 6,226
780 $ 1,038 $ 1,339 $ 1,690
EBT
Taxes (40%)
$
$
3,000 $
1,200 $
3,314 $ 3,670 $ 4,075 $ 4,536
1,326 $ 1,468 $ 1,630 $ 1,815
Net inc
$
1,800 $
1,988 $ 2,202 $ 2,445 $ 2,722
Divs (45%)
Add to RE
$
$
810 $
990 $
895 $
991 $ 1,100 $ 1,225
1,094 $ 1,211 $ 1,345 $ 1,497
Balance Sheet:
Assets:
Cash
A/R
Inv
2019
2020
2021
2022
2023
$
$
$
1,080 $
6,480 $
9,000 $
1,242 $ 1,428 $ 1,643 $ 1,889
7,452 $ 8,570 $ 9,855 $ 11,334
10,350 $ 11,903 $ 13,688 $ 15,741
$
$
$
16,560 $
12,600 $
29,160 $
19,044 $ 21,901 $ 25,186 $ 28,964
14,490 $ 16,664 $ 19,163 $ 22,037
33,534 $ 38,564 $ 44,349 $ 51,001
Liabilities & Equity:
A/P
$
Accruals
$
N/P
$
4,320 $
2,880 $
2,100 $
4,968 $ 5,713 $ 6,570 $ 7,556
3,312 $ 3,809 $ 4,380 $ 5,037
4,300 $ 6,877 $ 9,889 $ 13,401
Cur assets
Net PPE
Total assets
Cur liabilities
LT debt
Common stock
RE
Total L&E
AFN
Assets – L&E
$
$
$
$
$
9,300
3,500
3,500
12,860
29,160
$
$
$
$
$
$
$
12,580
3,500
3,500
13,954
33,534
$
$
$
$
$
16,399
3,500
3,500
15,165
38,564
$
$
$
$
$
20,839
3,500
3,500
16,510
44,349
$
$
$
$
$
25,994
3,500
3,500
18,007
51,001
2,200 $ 2,577 $ 3,011 $ 3,513
(0) $
(0) $
$
(0)
Using the Plug: Forecasting Additional Financing Needed
Go to Blackboard under class 4 materials and use the data from the spreadsheet we
discussed in class. (The base year is in column C):
1) Use the additional funds needed (AFN) equation illustrated in class to find
Styles forecasted AFN under the following different scenarios (presuming
AFN is borrowed or reflects debt repaid):
growth rate = 25%
growth rate = 0%
payout ratio = 20% (with growth rate = 15%)
payout ratio = 70% (with growth rate = 15%)
interest rate = 4% (with growth rate = 15% and payout ratio = 45%)
interest rate = 20% (with growth rate = 15% and payout ratio = 45%)
2) For each scenario, what are the AFNs if cash (not Notes Payable) is the
balancing figure?
g ( A * ? L*) ? ( EBIT )(1 + g )(1 ? T )(1 ? d ) + I 0 (1 ? T )(1 ? d )
1 ? i(1 ? T )(1 ? d )
OBS: In the above mentioned scenarios only g, d and i are allowed to vary.
Show your calculations.
3) How does AFN vary with:
growth rate?
payout ratio?
4). Assuming that in 2018 (base year) fixed assets had been operated at only 75%
capacity, under the scenario of 25% growth rate what can you say about the
AFN?
5). If Sales would increase to $50,000 what would the Fixed Assets requirement
be?
6). How would excess/deficit capacity affect the forecasted ratios?

Purchase answer to see full
attachment

Our Service Charter

1. Professional & Expert Writers: Homework Free only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed of papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Homework Free are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Homework Free is known for timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit in all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Homework Free, we have put in place a team of experts who answer to all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.