6. [Multi-Year Financial Statement Projections] The Minoso Corporation anticipates a 20
percent increase in sales for 2020, 2021, and 2022. 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 2019 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 2020 prior to obtaining any additional financing. Use a separate
B. Extend your 2020 spreadsheet-based financial statement projections for two additional years
(2021 and 2022). What is the total amount of AFN needed over the three-year period?
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
Added Retained Earnings 576 720.0 892.8 1100.2
Balance Sheets 2019 2020 2021 2022
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
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 2020 increases from 1120 prior to obtaining any additional debt and/or equity
financing to 1160.3 after financing the initial 1120 with long-term debt at a 10 percent
The total (cumulative) amount of AFN needed over the 2020-22 three-year period assuming
the AFN is financed with long-term debt (LTD) at a 10 percent interest rate is estimated to be
4256.1 or an additional 271.5 (4256.1 – 3984.6) to cover financing the AFN with LTD at a 10
percent interest rate.
MINOSO CORPORATION
Additional Funds Needed (AFN)
Note: Financial Statement Projections are Prior to New Financing Decisions
MINOSO CORPORATION
Statement of Cash Flows 2020 2021 2022
Net Income 1200.0 1488.0 1833.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
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
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
2019 of Sales Forecast Basis 2020 2021 2022
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 2019 2020 2021 2022
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
MINI CASE: PHARMA BIOTECH CORPORATION
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.
MINOSO CORPORATION
Additional Funds Needed (AFN)
Note: Financial Statement Projections With New 10% LTD Financing
MINOSO CORPORATION
Statement of Cash Flows 2020 2021 2022
Net Income 1132.8 1341.9 1594.5
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
Pharma Biotech, however, is concerned with forecasting its financial statements for next
year because it is uncertain as to the amount of additional financing of assets that will be needed
as the venture ramps up sales next year. Pharma Biotech expects to introduce a DHA product
Part A
Pharma Biotech is interested in developing an initial “big picture” of the size of financing
that might be needed to support its rapid growth objectives for 2020 and 2021.
A. Calculate the following financial ratios (as covered in Chapter 5) for Pharma Biotech for
2019: (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.
(a) net profit margin: 960/15,000 = .0640 = 6.40%
(b) sales-to-total-assets: 15,000/12,000 = 1.2500 times
(c) equity multiplier: 12,000/5,200 = 2.3077 times
B. Estimate Pharma’s sustainable sales growth rate based on its 2019 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 2019 retained
earnings = (2,400 + 2,800) – 576 = 5,200 – 576 = 4,624
C. Estimate the additional funds needed (AFN) for 2020, using the formula or equation
method presented in the chapter.
2020 sales = 15,000 x 1.50 = 22,500
Change in sales = 22,500 – 15,000 = 7,500
Retention Rate (RR) = 1 – Dividend Payout Ratio = 1 – .40 = .60
D. Also, estimate the AFN using the equation method for Pharma Biotech for 2021. What
will be the cumulative AFN for the two-year period?
2020 sales = 22,500 (from Part C)
2021 sales = 22,500 x 1.80 = 40,500
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 2020 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.
additional financing has not been obtained. The AFN formula assumes that the profit
margin will remain constant, which means that all expenses are variable or move directly
with sales.
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 2021 that build upon the projections for 2020 prepared in Part B
above. What is the cumulative (2020 and 2021) amount of additional funds needed?
Spreadsheet results are provided below. The amount of funds needed is $3,664 in 2020
and $9,240 in 2021. The two-year total is $12,903. The $1 difference ($12,903 versus
$12,904) is due to rounding each of the annual AFN to the nearest dollar. Recall that the
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 2019 in [Part A] Item A above?
2020 total-debt-to-total-assets ratio = (current liabilities + long-term debt + 2017 AFN) =
(6,001 + 2200 + 3664)/18,000 = 11,865/18,000 = .6592 = 65.92%
MINI CASE
Pharma Biotech Corporation
[$ Thousands] Sales Growth Rates 50% 80%
Income Statements Actual Percent
2019 of Sales Forecast Basis 2020 2021
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
Balance Sheets Actual
2019 Forecast Basis 2020 2021
Cash & Mkt Sec 1000 6.67% .0667 x Forecast Sales 1501 2701
Accounts Rec 2000 13.33% .1333 x Forecast Sales 2999 5399
Inventories 2200 14.67% .1467 x Forecast Sales 3301 5941
Total Current Assets 5200 7801 14041
Fixed Assets, Net 6800 45.33% .4533 x Forecast Sales 10199 18359
Total Assets 12000 18000 32400
Pharma Biotech Corporation
Statement of Cash Flows
2020 2021
Net Income 1560 2999
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