Question

In: Accounting

Use the pro forma financial statements to answer the questions below. Change the assumptions in the...

Use the pro forma financial statements to answer the questions below. Change the assumptions in the assumptions box as needed to answer the questions. In addition to the assumptions listed on the spreadsheet, also assume that all asset accounts will grow at the same rate as sales and that no new equity will be issued in 2018.

1. Enter a formula for external funding required in the first green box. How much external financing does Ottawa need in 2018?

2. Given your answer from (a), do you expect the sustainable growth rate to be greater than, less than, or equal to the sales growth rate for 2018? Enter a formula for the sustainable growth rate in the second green box. What is Ottawa’s sustainable growth rate?

3. At what rate does the actual sales growth rate equal the sustainable growth rate? How much external financing is required at this growth rate? (This can be determined by trial and error.)

4. Return the sales growth rate to 15%. Suppose Ottawa wants to solve the financing shortfall by increasing profit margin. How low would the ratio of cost of goods sold/sales have to go in order to make up the shortfall? With the cost of goods sold/sales at this lower level, what is the sustainable growth rate? (Hint: The Goal Seek tool can help you find this quickly. Consult Excel Help if you are unfamiliar with the Goal Seek tool.)

5. Return cost of goods sold/sales to 75%. Now suppose Ottawa wants to solve the shortfall by increasing the retention ratio. How low would the dividend payout ratio have to be in order to eliminate the financing shortfall?

OTTAWA CORP.

INCOME STATEMENT ($ millions)

BALANCE SHEET ($ millions)

Actual

Projected

Actual

Projected

2017

2018

2017

2018

Sales

$3,500

$4,025

Cash

$150

$173

Cost of goods sold

           2,775

           3,019

Accounts receivable

540

621

Operating expense

              360

              403

Inventory

1,050

1,208

EBIT

              365

              604

   Total current assets

1,740

2,001

Interest expense

                68

                80

Property, plant, & equipment

1,578

1,815

EBT

              297

              524

   Total assets

3,318

3,816

Tax

102

183

Net income

$195

$341

Total debt

1,106

1,208

Shareholders' equity  

2,212

2,416

Assumptions for 2018

Total liabilities & equity

$3318 $3,625

Sales growth rate

15.0%

Cost of goods sold/sales

75.0%

External funding required

Operating expense/sales

10.0%

Sustainable growth rate

Dividend payout ratio

40.0%

Tax rate

35.0%

Interest rate on debt

7.2%

Total debt/equity

50.0%

Solutions

Expert Solution

The answer is as below.Thanks

1 For the given 2018 projections
External funding required 3816-2416-1106= 294
Sustainable growth rate(SGR)= Return on Equity*Retention Ratio
ROE*RR
(341/2416)*60% 8.47%
SGR 8.47% < Sales growth Rate, 15%
Workings for:SGR=Sales Growth Rate
2017 Projected 2018 Equation for trial & error
Sales 3500 4025 3500*(1+x%)
Cost of goods sold 2,775 3019 0.75*(3500*(1+x%))
Operating expense 360 403 0.10*(3500*(1+x%))
EBIT 365 604 0.15*(3500*(1+x%))
Interest expense 68 80 80
EBT 297 524 0.15*(3500*(1+x%))-80
Tax 102 183
Net income 195 341 0.65*(0.15*(3500*(1+x%))-80)
Dividend payout 40% 136
Retention 60% 204
Net income=
0.65*(0.15*(3500*(1+x%))-80)
ROE=(0.65*(0.15*(3500*(1+x%))-80))/(2212+(60%*(0.65*(0.15*(3500*(1+x%))-80))))
SGR= ROE*RR
((0.65*(0.15*(3500*(1+x%))-80))/(2212+(60%*(0.65*(0.15*(3500*(1+x%))-80)))))*60%
Equating SGR to Sales growth rate,
((0.65*(0.15*(3500*(1+x%))-80))/(2212+(60%*(0.65*(0.15*(3500*(1+x%))-80)))))*60%=x%
Solving the above,
the sales growth rate equals the SGR at 7.8995%
ie. 7.90%
BALANCE SHEET ($ millions)
Actual Projected 7.90%
2017 2018(15%)
Cash 150 173 162
Accounts receivable 540               621 583
Inventory 1050            1,208 1133
   Total current assets 1740            2,001 1877
Property, plant, & equipment 1578            1,815 1703
   Total assets 3318            3,816 3580
Total debt   1,106 1106 1106
Shareholders' equity    2,212 2416 2528
   Total liabilities & equity 3318 3625 3634
EFN needed(Plug-in Fig.) 294 -54
Total of asset side 3816 3580
As the EFN is NEGATIVE,   0 (ZERO) EFN   at 7.90% sales growth rate.
2212+ 204=2416
2212+316=2528
To solve the financing shortfall by increasing profit margin Ie. No EFN at this 15% sales growth, but increased P/M by lowering COGS % * sales
Net income needed= Total assets 3816- current debt 1106-current equity 2212=3816-1106-2212= 498
So,By trial & error, COGS needs to be 56.31% of sales, as below:
Sales 4025
Cost of goods sold (56.31%)*Sales 2266
Operating expense 403
EBIT 1356
Interest expense 80
EBT 1276
Tax 447
Net income 830
Dividend payout 40% 332
Retention 60% 498
SGR =ROE*RR
ie (830/(2212+498))*60%=18.38%
To solve the shortfall by increasing the retention ratio Ie. No EFN at this 15% sales growth, but increased RR by lowering the D/Payout
Retained income needed= Total assets 3816- current debt 1106-current equity 2212=3816-1106-2212= 498
So,By trial & error :
Sales 4025
Cost of goods sold 75%*sales 3019
Operating expense 403
EBIT 604
Interest expense 80
EBT 524
Tax 183
Net income 341
As the financing shortfall (498) > the net Income (341), under this scenario, even NIL dividend will not be sufficient.

Related Solutions

Chapter 4 Problem 13: Ottawa Corp. Use the pro forma financial statements to answer the questions...
Chapter 4 Problem 13: Ottawa Corp. Use the pro forma financial statements to answer the questions below. Change the assumptions in the assumptions box as needed to answer the questions. In addition to the assumptions listed on the spreadsheet, also assume that all asset accounts will grow at the same rate as sales, and that no new equity will be issued in 2018. Enter a formula for external funding required in the first green box. How much external financing does...
Prepare the following Pro Forma Financial Statements for the proposed new location (pro forma statements in...
Prepare the following Pro Forma Financial Statements for the proposed new location (pro forma statements in this case are budgeted statements for 2018 based on the new location scenario at the bottom of the page) Pro Forma Income Statement Pro Forma Balance Sheet PEYTON APPROVED PRO FORMA INFORMATION The company is planning to open another location in 2018 . Prepare pro forma financials for 2018 for the new location using the following information: 1. Cost of leasing commercial space: $1,500...
A. What do pro forma financial statements show? B. What are pro forma financial statements based...
A. What do pro forma financial statements show? B. What are pro forma financial statements based on? C. What are the strategic benefits of making financial projections on pro forma statements?
Which of the following budgeted pro forma financial statements is prepared first? A. Pro forma statement...
Which of the following budgeted pro forma financial statements is prepared first? A. Pro forma statement of cash flows B. Pro forma income statement C .Pro forma balance sheet D. May be prepared in any order explain why please
Integrative: Pro forma statements Red Queen Restaurants wishes to prepare financial plans. Use the financial statements...
Integrative: Pro forma statements Red Queen Restaurants wishes to prepare financial plans. Use the financial statements and the other information provided below to prepare the financial plans. The following financial data are also available: The firm has estimated that its sales for 2020 will be $900,000. The firm expects to pay $35,000 in cash dividends in 2020. The firm wishes to maintain a minimum cash balance of $30,000. Accounts receivable represent approximately 18% of annual sales. The firm’s ending inventory...
Integrative: Pro forma statements Red Queen Restaurants wishes to prepare financial plans. Use the financial statements...
Integrative: Pro forma statements Red Queen Restaurants wishes to prepare financial plans. Use the financial statements and the other information provided below to prepare the financial plans. The following financial data are also available: The firm has estimated that its sales for 2020 will be $900,000. The firm expects to pay $35,000 in cash dividends in 2020. The firm wishes to maintain a minimum cash balance of $30,000. Accounts receivable represent approximately 18% of annual sales. The firm’s ending inventory...
FORECASTING FINANCIAL STATEMENTS - Below is a pro-forma income statement and balance sheet for Company A...
FORECASTING FINANCIAL STATEMENTS - Below is a pro-forma income statement and balance sheet for Company A for a 5-year period and a terminal year, based on various assumptions, which already have been completed. Company A Income Statement For the Years Ended 2017 2018 2019 2020 2021 2022 Terminal year 2023 Sales     550.00            825.00               990.00                  1,138.50                    1,252.35                    1,340.01                        1,393.62                 1.50 (825*120%) (990*115%) (1138.50*110%) (1252.35*107%) (1340.01*104%) Cost of Sales     275.00            288.75              ...
Discuss the differences between GAAP financial statements and pro forma statements?
Discuss the differences between GAAP financial statements and pro forma statements?
Ottawa Corporation Instructions: Financial Statements, 2013 and Projected 2014 ($ millions) Use the pro forma financial...
Ottawa Corporation Instructions: Financial Statements, 2013 and Projected 2014 ($ millions) Use the pro forma financial statements to answer the questions below. Change the assumptions in the assumptions box as needed to answer the questions. In addition to the assumptions listed on the spreadsheet, also assume that all asset accounts will grow at the same rate as sales, and that no new equity will be issued in 2014. INCOME STATEMENT BALANCE SHEET Questions: Actual Projected Actual Projected a. Enter a...
What role do revenue and expense assumptions play in the development of pro forma statements
What role do revenue and expense assumptions play in the development of pro forma statements
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT