Chapter 9: Projecting Financial Statements
13
6. [Multi-Year Financial Statement Projections] The Minoso Corporation anticipates a 20
percent increase in sales for 2017, 2018, and 2019. Minoso is currently operating at full
capacity and thus expects to increase its investment in both current and fixed assets in order
to support the increase in forecasted sales. The Minoso Corporation’s 2016 income and
balance sheet statements are given in problem 4.
A. Prepare an Excel spreadsheet model that projects the income statement, balance sheet, and
statement of cash flows for 2017 prior to obtaining any additional financing. Use a separate
AFN long-term financing (liability/equity) account to show the amount of financing needed to
make the balance sheet balance.
B. Extend your 2017 spreadsheet-based financial statement projections for two additional years
(2018 and 2019). What is the total amount of AFN needed over the three-year period?
Chapter 9: Projecting Financial Statements
14
MINOSO CORPORATION
Financial Statement Projections
Note: Projections are Prior to New Financing Decisions
Ch 9, Prob 6
Sales Growth Rates—––—> 20% 20% 20%
Income Statements Actual Percent Forecast Forecast Forecast
2016 of Sales Forecast Basis 2017 2018 2019
Net Sales 15000 100.0% 1.20 x Current Sales 18000.0 21600.0 25920.0
Operating Expenses -13000 86.7% .867 x Forecast Sales -15600.0 -18720.0 -22464.0
Interest* -400 Initially Held Fixed -400.0 -400.0 -400.0
EBT 1600 2000.0 2480.0 3056.0
Taxes (40%) -640 40% of EBT -800.0 -992.0 -1222.4
Net Income (NI) 960 6.4% 1200.0 1488.0 1833.6
Cash Dividends (40% of NI) -384 40% of NI -480.0 -595.2 -733.4
Added Retained Earnings 576 720.0 892.8 1100.2
Balance Sheets 2016 2017 2018 2019
Required Cash 1000 6.7% .067 x Forecast Sales 1200.0 1440.0 1728.0
Surplus Cash 0 0.0 0.0 0.0
Accounts Rec 2000 13.3% .133 x Forecast Sales 2400.0 2880.0 3456.0
Inventories 2200 14.7% .147 x Forecast Sales 2640.0 3168.0 3801.6
Total Current Assets 5200 6240.0 7495.2 8994.2
Fixed Assets, Net 6800 45.3% .453 x Forecast Sales 8160.0 9792.0 11750.4
Total Assets 12000 80.0% .800 x Forecast Sales 14400.0 17280.0 20736.0
Accounts Pay 1600 10.7% .107 x Forecast Sales 1920.0 2304.0 2764.8
Bank Loan 1800 1800.0 1800.0 1800.0
Acc Liab 1200 8.0% .080 x Forecast Sales 1440.0 1728.0 2073.6
Total Current Liab 4600 5160.0 5832.0 6638.4
Additional Funds Needed (AFN) 0 1120.0 2435.2 3984.6
Long–Term Debt 2200 2200.0 2200.0 2200.0
Common Stock 2400 2400.0 2400.0 2400.0
Retained Earnings 2800 [+ Inc in Forecast RE] 3520.0 4412.8 5513.0
Total Iiab & Equity 12000 14400.0 17280.0 20736.0
Chapter 9: Projecting Financial Statements
15
MINOSO CORPORATION
Additional Funds Needed (AFN)
Note: Financial Statement Projections are Prior to New Financing Decisions
MINOSO CORPORATION
Statement of Cash Flows 2017 2018 2019
Net Income 1200.0 1488.0 1833.6
Change in A/R -400.0 -480.0 -576.0
Change in Inv. -440.0 -528.0 -633.6
Change in A/P 320.0 384.0 460.8
Change in Acc. Liab. 240.0 288.0 345.6
CF from Operations 920.0 1152.0 1430.4
Change in Fixed Assets, Net -1360.0 -1632.0 -1958.4
CF from Investments -1360.0 -1632.0 -1958.4
Change in Bank Loan 0.0 0.0 0.0
Change in Long-Term Debt 0.0 0.0 0.0
Change in Common Stock 0.0 0.0 0.0
Payment of Cash Dividends -480.0 -595.2 -733.4
CF from Financing -480.0 -595.2 -733.4
Net Cash Flow -920.0 –1075.2 –1261.4
Beginning Cash 1000.0 1200.0 1440.0
Ending Cash Before Borrowing 80.0 124.8 178.6
Target Ending Cash 1200.0 1440.0 1728.0
Additional Funds Needed (AFN) 1120.0 1315.2 1549.4
Cumulative AFN 1120.0 2435.2 3984.6
C. Show how your spreadsheet model projections will change if the AFN from Part B is
financed by issuing additional long-term debt at a 10% interest rate.
The AFN for 2017 increases from 1120 prior to obtaining any additional debt and/or equity
Chapter 9: Projecting Financial Statements
16
MINOSO CORPORATION
Financial Statement Projections
Note: Projections Assume New 10% LTD Financing
Ch 9, Prob 6
Sales Growth Rates—–––> 20% 20% 20%
Income Statements Actual Percent Forecast Forecast Forecast
2016 of Sales Forecast Basis 2017 2018 2019
Net Sales 15000 100.0% 1.20 x Current Sales 18000.0 21600.0 25920.0
Operating Expenses -13000 86.7% .867 x Forecast Sales -15600.0 -18720.0 –22464.0
Interest -400 10% Interest Rate** -512.0 -643.5 -798.5
EBT 1600 1888.0 2236.5 2657.5
Taxes (40%) -640 40% of EBT -755.2 -894.6 -1063.0
Net Income (NI) 960 6.4% 1132.8 1341.9 1594.5
Cash Dividends (40% of NI) -384 40% of NI -453.1 -536.8 -637.8
Added Retained Earnings 576 679.7 805.1 956.7
Balance Sheets 2016 2017 2018 2019
Required Cash 1000 6.7% .067 x Forecast Sales 1200.0 1440.0 1728.0
Surplus Cash 0 0.0 0.0 0.0
Accounts Rec 2000 13.3% .133 x Forecast Sales 2400.0 2880.0 3456.0
Inventories 2200 14.7% .147 x Forecast Sales 2640.0 3168.0 3801.6
Total Current Assets 5200 6240.0 7488.0 8985.6
Fixed Assets, Net 6800 45.3% .453 x Forecast Sales 8160.0 9792.0 11750.4
Total Assets 12000 80.0% .800 x Forecast Sales 14400.0 17280.0 20736.0
Accounts Pay 1600 10.7% .107 x Forecast Sales 1920.0 2304.0 2764.8
Bank Loan 1800 1800.0 1800.0 1800.0
Acc Liab 1200 8.0% .080 x Forecast Sales 1440.0 1728.0 2073.6
Total Current Liab 4600 5160.0 5832.0 6638.4
Chapter 9: Projecting Financial Statements
17
MINOSO CORPORATION
Additional Funds Needed (AFN)
Note: Financial Statement Projections With New 10% LTD Financing
MINOSO CORPORATION
Statement of Cash Flows 2017 2018 2019
Net Income 1132.8 1341.9 1594.5
Change in A/R -400.0 -480.0 –576.0
Change in Inv. –440.0 -528.0 -633.6
Change in A/P 320.0 384.0 460.8
Change in Acc. Liab. 240.0 288.0 345.6
CF from Operations 852.8 1005.9 1191.3
Change in Fixed Assets, Net -1360.0 –1632.0 -1958.4
CF from Investments -1360.0 -1632.0 -1958.4
Change in Bank Loan 0.0 0.0 0.0
Change in Long–Term Debt 0.0 0.0 0.0
Change in New L-T Debt 1160.3 1402.9 1692.9
Change in Common Stock 0.0 0.0 0.0
Payment of Cash Dividends -453.1 -536.8 -637.8
CF from Financing 707.2 866.1 1055.1
Net Cash Flow 200.0 240.0 288.0
Beginning Cash 1000.0 1200.0 1440.0
Ending Cash Before Borrowing 1200.0 1440.0 1728.0
Target Ending Cash 1200.0 1440.0 1728.0
Additional Funds Needed (AFN) 0.0 0.0 0.0
Cumulative AFN 0.0 0.0 0.0
The Pharma Biotech Corporation spent several years working on developing a DHA product that
can be used to provide a “fatty acid” supplement to a whole variety of food products. DHA
stands for docsahexaenoic acid, an omega-3 fatty acid found naturally in cold water fish. The
benefits of fatty fish oil have been cited in studies of the brain, eyes, and the immune system.
Unfortunately, it is both difficult to consume enough fish to get the benefits of DHA and most
Pharma Biotech’s initial DHA product was designed as additives to dairy products and
yogurt. For example, the venture’s DHA product was added to cottage cheese and fruit-flavored
Chapter 9: Projecting Financial Statements
19
A. Calculate the following financial ratios (as covered in Chapter 5) for Pharma Biotech for
2016: (a) net profit margin, (b) sales-to-total-assets ratio, (c) equity multiplier, and (d)
total-debt-to-total-assets. Apply the return on assets and return on equity models.
Discuss your observations.
Pharma Biotech’s net profit margin is less than double digits. Hopefully as the venture
B. Estimate Pharma’s sustainable sales growth rate based on its 2016 financial statements.
[Hint: you need to estimate the beginning of period stockholders’ equity based on the
information provided.] What financial policy change might Pharma Biotech make to
improve its sustainable growth rate? Show your calculations.
Beginning stockholders’ equity = ending stockholders’ equity – added 2016 retained
The payment of cash dividends at a 40% of net income rate restricts Pharma Biotech
from being able to crow more rapidly without having to issue more equity capital. For
example, a policy of no cash dividends payout (i.e., a 100% retention rate) would result
in a sustainable sales growth rate of:
C. Estimate the additional funds needed (AFN) for 2017, using the formula or equation
method presented in the chapter.
Chapter 9: Projecting Financial Statements
20
= $6,000 – $1,400 – $864 = $3,736
D. Also, estimate the AFN using the equation method for Pharma Biotech for 2018. What
will be the cumulative AFN for the two-year period?
2017 sales = 22,500 (from Part C)
1,555 = 9,484
Cumulative two-year (2017 & 2018) AFN = $3,736 + $9,484 = $13,220
= $20,400 – $4,761 – $2,419 = $13,220
Part B
Pharma Biotech is seeking your assistance in preparing its projected financial statements using
the percent-of-sales method. Initial projected financial statements can be prepared by hand
using a financial calculator or by constructing spreadsheet–based solutions.
A. Prepare a projected income statement for 2017 for Pharma Biotech before obtaining any
additional financing. [Hint: for those who need help, follow the projected income
statement shown in Table 9.1 for the GameToy Company.]
Spreadsheet results are provided below.
B. Prepare a projected balance sheet for 2017 for Pharma Biotech before obtaining any
additional financing. [Hint: for those who need help, follow the projected balance sheet
shown in Table 9.2 for the GameToy Company.]
Spreadsheet results are provided below.
C. Based on your projected balance sheet for Pharma Biotech for 2017, what is your
estimate of the additional funds needed? Why does the AFN from your initial percent-of-
Chapter 9: Projecting Financial Statements
21
sales projected financial statements differ from the AFN estimated using the formula
method in Item C above?
The spreadsheet solution shows an AFN of $3,664 for 2017. This is less than the AFN
D. Prepare a projected statement of cash flows for Pharma Biotech for 2017. [Hint: for
those who need help, follow the projected statement of cash flows shown in Table 9.3 for
the GameToy Company.]
Spreadsheet results are provided below.
Part C
The following tasks or challenges are best handled by setting up spreadsheet–based methods
projecting financial statements.
A. Prepare projected income statements, balance sheets, and statements of cash flows for
Pharma Biotech for 2018 that build upon the projections for 2017 prepared in Part B
above. What is the cumulative (2017 and 2018) amount of additional funds needed?
Spreadsheet results are provided below. The amount of funds needed is $3,664 in 2017
and $9,240 in 2018. The two-year total is $12,903. The $1 difference ($12,903 versus
B. Calculate the total-debt-to-total-assets ratio and the equity multiplier ratio (covered in
Chapter 5) assuming the cumulative AFN is financed with debt funds. How would these
ratios compare with the same ratios calculated for 2016 in [Part A] Item A above?
2017 total-debt-to-total-assets ratio = (current liabilities + long-term debt + 2017 AFN) =
Chapter 9: Projecting Financial Statements
22
Summary: 2016 2017 2018
Because of the large amounts of AFN required to support rapid sales growth, financing
MINI CASE
Pharma Biotech Corporation
[$ Thousands] Sales Growth Rates 50% 80%
Income Statements Actual Percent
2016 of Sales Forecast Basis 2017 2018
Net Sales 15000 100.00% (1+growth rate) x Sales 22500 40500
Operating Expenses -13000 86.67% .8667 x Forecast Sales –19501 -35101
Interest -400 Initially Fixed -400 -400
EBT 1600 2599 4999
Taxes (40%) -640 40% of EBT –1040 -1999
Net Income (NI) 960 6.40% 1560 2999
Cash Dividends (40% of NI) -384 40% of NI -624 –1200
Added Retained Earnings 576 936 1800
Accounts Pay 1600 10.67% .1067 x Forecast Sales 2401 4321
Bank Loan 1800 1800 1800
Acc Liab 1200 8.00% .0800 x Forecast Sales 1800 3240
Total Current Liab 4600 6001 9361
Chapter 9: Projecting Financial Statements
23
Pharma Biotech Corporation
Statement of Cash Flows
2017 2018
Net Income 1560 2999
Change in A/R -999 –2399
Change in Inv. -1101 –2641
Change in A/P 801 1921
Change in Acc. Liab. 600 1440
CF from Operations 860 1320
Change in Fixed Assets, Net -3399 –8159
CF from Investments -3399 –8159
Change in Bank Loan 0 0
Change in Long–Term Debt 0 0
Change in Common Stock 0 0
Payment of Cash Dividends -624 –1200
CF from Financing -624 –1200
Net Cash Flow -3163 –8039
Beginning Cash 1000 1501
Ending Cash Before Borrowing -2163 –6539
Target Ending Cash 1501 2701
Additional Funds Needed (AFN) 3664 9240
Cumulative AFN 3664 12903