1466003 – Pearson Education Limited ©

Table 8-4 Template Considerations in Developing Financial Statements

The Free Excel Strategic Planning Template at www.strategyclub.com

Many practitioners and students of strategic planning use the template to develop existing and projected

financial statements to reveal the expected impact of proposed recommendations for the firm. Do not

expect the template to do the thinking for you in terms of projecting each row on both the income

statements and balance sheets. The template does the calculating for you but you must supply the

thinking regarding the amount to alter each row given the expected impact of your recommendations. For

anyone using the template to develop actual and projected financial statements, Table 8-4 provides

author comments.

Almost every row of the template’s projected financial statements is to be forecasted by the

user to reveal the expected impact of his or her recommendations for the firm. However, a few

rows in the template’s projected statements are automatically calculated (rather than

forecasted) as discussed below.

1. Make Conversion. Convert (enter) your firm’s most recent Form 10K actual income

statements and balance sheets into the template-formatted statements. Doing this will

greatly facilitate development of projected financial statements and ratios. In making this

conversion, use the nonrecurring events row to house extraneous items on the income

statements; on the balance sheets, use the other current or long-term assets, the other

current or long-term liabilities rows.

2. Income Statements. Use the nonrecurring events row to adjust for divesting a division or

any other extraneous events in order for your firm’s Form 10K revenues, cost of goods sold,

operating expenses, and net income to match perfectly with the template statement. If

having an issue with EBIT perfectly matching the Form 10K, simply subtract EBIT from

Gross Profit on the Form 10K and enter this number for Operating Expenses. This

procedure will ensure your template EBIT matches the EBIT from your firm’s Form 10K

because sometimes the Operating Expenses line is difficult to transfer over perfectly; it is

okay if your template Operating Expenses do not perfectly match the Form 10K as long as

EBIT does. Net income is automatically calculated from your other income statement

forecasts, so the net income row is not forecasted.

3. Retained Earnings (RE) Row. The RE row on the template projected balance sheets is

automatically calculated because the RE row is determined by carrying over the net income

less dividends annually from the income statement to the RE row near the bottom of the

balance sheet.



1466003 – Pearson Education Limited ©

4. Balance Sheets. Work row-by-row from the bottom to the top making changes as needed

to reflect the impact of your recommendations on each row. Leave forecasted items and

rows the same as the prior year if you expect no change in that row given your

recommendations. The template has a note to the right of each row to aid in how to enter

the numbers. Get the total liabilities plus stockholders’ equity number, then transfer that

number to the total assets row, and then move further upward towards the cash row

forecasting each item/row given your recommendations.

5. Cash. The template uses the balance sheet cash row as a plug figure to make the projected

balance sheet balance, so this row is automatically calculated. Often the cash figure will be

abnormally high after forecasting all other rows; in this situation make appropriate

adjustments in the liability or equity sections, usually using the long-term-debt or paid-in-

capital rows, to offset the amount you increase or decrease the cash row to keep liquidity

in line with industry averages. Making adjustments, even to other items on the balance

sheet, is good practice and routinely done by all accountants, so do not feel you are

cheating the system until you get the numbers reasonable. Projected statements are a

good-faith estimate, and a high cash figure only indicates other estimates need to be

reconsidered. Also, check for typos or miscalculations on both the projected income

statement and balance sheet. For example, it is possible you are over- or underestimating

the 1) revenue impacts of your recommendations or 2) the amount of capital you need to

implement your recommendations. Also, experiment with adjusting the historical

percentages on both asset and liability items such as accounts payable or accounts

receivable; it is possible the historical number needs to be increased on asset line items or

decreased on liability items if the firm is operating more efficiently, and the result will bring

cash down. Also, double check plant property and equipment because a high cash figure

may indicate you are underestimating how much these assets will cost. Once you make

one change, revaluate the cash figure on the new projected balance sheet and continue

until you find the cash number reasonable. Accountants do this all the time, striving for

excellent, reasonable, truthful projected statements. Ethics Capsule 8 and Global Capsule

8 address the truthfulness or reasonableness aspect of projected financial statements.

6. Financial Ratios. The template will automatically calculate actual and forecasted financial

ratios once income statement and balance sheet data are entered. When compared to prior

years and industry averages, financial ratios provide valuable insights for implementing

strategies. (Financial ratios were discussed in Chapter 4 .)







Ethics Capsule 8

Projected Financial Statement Manipulation

1466003 – Pearson Education Limited ©

How Realistic Are These Numbers?

Casper1774 Studio/Shutterstock

Investors, shareholders, and others need to know that top executives can legally

manipulate financial statements to inflate or deflate expected results, and they

often do. Firms may inflate or present an overly rosy picture of projected

financial statements to garner support from a wide range of constituencies for a

variety of reasons. But you may wonder why would executives present a deflated

or unfavorable picture of the future? Reasons to deflate include (1) to discourage

potential acquirers or (2) to load bad financial information into a particular

accounting period so that periods beyond will look better. In the famous book

Financial Shenanigans (2002) by Dr. Howard Schilit, five ways are discussed in

which top executives manipulate financial statements, as follows:

1. Record revenue prematurely.

2. Record fictitious revenue.

3. Increase net income with one-time gains.

4. Shift current expenses to an earlier or later period.

5. Failure to record certain liabilities.

The Securities and Exchange Commission (SEC) has taken steps to curtail

projected financial statement manipulation, but this remains an ethical issue in

corporate America (and globally).

1466003 – Pearson Education Limited ©

Global Capsule 8

The Least (and Most) Corrupt Countries in the World for Doing Business

New Zealand Is a Winner

Ruslan Olinchuk/123RF

As a part of its Global Coalition Against Corruption, Transparency International

publishes annual corruption-perception indices that rank countries according to

their relative corruption levels. The 2016 corruption-perception scores averaged

1466003 – Pearson Education Limited ©

Table 8-5 Corruption-Perception Indices (2016)

43, and more than two-thirds of the 176 included countries fell below the

midpoint of 50 (0 = very corrupt; unethical; 100 = very clean; ethical). Highly

ranked countries avoid the most obvious forms of corruption that negatively

impact the daily lives of its citizens; whereas, lower-ranked countries are

plagued by corruption because laws are frequently ignored and citizens often

face bribery, extortion, or other corrupt experiences. Table 8-5 shows the

rankings of the top 20 (least corrupt) countries, as well as the bottom 10 (most

corrupt) countries.

2016 Rank Country 2016 Score

1 Denmark 90

1 New Zealand 90

3 Finland 89

4 Sweden 88

5 Switzerland 86

6 Norway 85

7 Singapore 84

8 Netherlands 83

9 Canada 82

10 Germany 81

10 Luxembourg 81

10 United Kingdom 81

13 Australia 79

14 Iceland 78

15 Belgium 77



1466003 – Pearson Education Limited ©

15 Hong Kong 77

17 Austria 75

18 United States 74

19 Ireland 73

20 Japan 72

166 Venezuela 17

168 Guinea-Bissau 16

169 Afghanistan 15

170 Libya 14

170 Sudan 14

170 Yemen 14

173 Syria 13

174 Korea (North) 12

175 South Sudan 11

176 Somalia 10

Source: Based on information at https://www.transparency.org/news/feature/corruption_perceptions_index_2016

1466003 – Pearson Education Limited ©

Steps to Develop Projected Financial Statements

Whether you use the free, Excel strategic-planning template, projected financial statement analysis can

be explained in seven steps:

Prepare the projected income statement before the balance sheet. Start by forecasting sales

(revenues) as accurately as possible to reveal the expected impact of recommendations being

implemented. In forecasting revenues, do not blindly push historical revenue growth

percentages into the future without considering ventures the firm undertook in prior years to

achieve those results. What a firm did previously to achieve those past sales increases may or

may not be appropriate for the future, depending on whether your recommendations take similar

or analogous actions (e.g., such as opening a similar number of stores). If dealing with a

manufacturing firm, also be mindful that if the firm is operating at 100 percent capacity running

three 8-hour shifts per day, then probably new manufacturing facilities (land, plant, and

equipment) will be needed to increase sales further.

Use the percentage-of-sales method to project cost of goods sold (COGS) and the Operating

Expenses in the income statement. For example, if COGS is 50 percent of sales in the prior year

(as it is in Table 8-5 ), then use a similar percentage to calculate COGS in the future year—

unless there is a reason to use a different percentage. Items such as interest, dividends, and

taxes must be treated independently and cannot be forecasted using the percentage-of-sales

method.

Calculate the projected net income (NI).

Subtract from the NI any dividends to be paid. The remaining NI is retained earnings (RE). Bring

the RE amount over to the balance sheet by adding it to the prior year’s RE amount on the

balance sheet. In other words, every year, a firm adds its RE (which is NI – Dividends for that

particular year) to its historical RE total on the balance sheet. Therefore, the RE amount on the

balance sheet is a cumulative number rather than money available for strategy implementation.

Note that RE is the first projected balance sheet item to be entered. As a result of this accounting

procedure in developing projected financial statements, the RE amount on the balance sheet is

usually a large number; it is a cumulative number of dollars reinvested into the company over

many years; it is not cash in the bank; it can be a low or even negative number if the firm has

been incurring losses or paying dividends that exceed NI. In fact, the most common ways for RE

to decrease from one year to the next on the balance sheet is (1) if the firm incurred an earnings

loss that year or (2) the firm had positive NI for the year but paid out dividends in excess of the

net income. Be mindful that RE is the key link between a projected income statement and

projected balance sheet, so be careful to make this calculation correctly.

Step 1.

Step 2.



Step 3.

Step 4.

1466003 – Pearson Education Limited ©

Project the balance sheet items working from the bottom to the top; begin with the RE row; if

using the template, the RE number is calculated automatically based on the amount of

dividends paid; then forecast the remaining equity items, followed by forecasting the long-term

liabilities, current liabilities, long-term assets, and current assets (in that order), working from the

bottom to the top.

Use cash as the plug figure—that is, project every line item on the projected balance except cash

(and RE); use the cash account to make the assets equal to the sum of the liabilities and

shareholders’ equity. Then make appropriate adjustments. For example, if the cash needed to

balance the statements is too small (or too large), considering making appropriate changes to

borrow more (or less) money than planned and reevaluate line items such as inventory,

accounts receivable, plant property and equipment and/or other asset lines. For example, if cash

is too high, (1) consider paying off some long-term debt; (2) reevaluate the projected percentage

increase of various liabilities because it is possible the firm may be operating more efficiently or

have greater economies of scale, or (3) increase your treasury stock. Rarely is the cash account

number perfect on the first pass-through, so adjustments are needed and made.

List commentary or notes below the projected statements to clarify for the reader why

significant changes were made on particular items or rows in one year versus the next. Notes

are essential for a reader to understand the changes made on certain rows. The template does

not prepare notes because notes reflect your thinking regarding the impact of your

recommendations and your associated costs.

Step 5.

Step 6.

Step 7.

1466003 – Pearson Education Limited ©

Table 8-6 P&G’s Actual Income Statements for 2016–2017 (in millions)

Table 8-7 P&G’s Actual Balance Sheets for Fiscal 2016 and 2017 (in millions)

P&G’s Actual Financial Statements

To further explain projected financial statement analysis, let’s work through an actual example for P&G.

P&G’s actual income statement and balance sheets for 2016 and 2017 are given in Table 8- 6 and

Table 8-7 , respectively. Notice that P&G’s actual Form 10K statements are converted or condensed into

the “template format.” (NOTE: About 95% percent of all strategic-management students using this

textbook, as well as thousands of corporate practitioners, use this template for doing strategic planning

and developing projected financial statements; if you use it put on your resume that you “gained

experience using strategic-planning software.”)

Table 8-6 Full Alternative Text







1466003 – Pearson Education Limited ©

Table 8-7 Full Alternative Text

Notice in Tables 8-6 and 8-7 that P&G’s actual Form 10K revenues declined in 2017 and their current

ratio (current assets divided by current liabilities) was less than 1.0. Thus, let’s propose that P&G go

forward with the following four recommendations with associated costs and comments; then we follow

the impact of the costs on the firm’s projected financial statements:

1. Five new plants will be built in each of the three forecasted years (2018, 2019, 2020) at $250

million each ($1,250 million per year attributed to plant, property, and equipment) with

adjustments to long-term debt and paid in capital, as described in # 4 below.

2. Raise an additional $1,250 million to finance research and development (R&D) and new

advertising. The total amount of capital being raised is $5 billion.

3. Increase dividends by 1 percent in each of the 3 forecasted years (these funds are derived from

net income so no need to raise additional capital for them).



 

1466003 – Pearson Education Limited ©

4. Forty percent of the capital needed will be financed through debt ($2,000 million) in projected

year 2018 and 60 percent ($3,000) through equity in projected year 2019. Note $2,000 + $3,000

= $5,000, which is the total cost of recommendations not considering dividends.

Note on P&G’s actual income statements in Table 8-6 the large increase in nonrecurring events in 2017;

this change was because of the company divesting various businesses to streamline the firm and better

align its activities with its mission. Divestitures explain much of the large increase in P&G net income for

fiscal 2017 and the large increase in retained earnings on the balance sheet. Without further similar

divestitures moving forward, net income will decline the following year, whereas revenues actually

increase. This example illustrates the importance of studying the Annual Report to determine what likely

caused the changes in the current financial statements; then carefully consider how your

recommendations will impact the projected statements. Simply pushing forward historical numbers on

every line item is a common mistake in projected financial statement development.

Note on P&G’s actual balance sheets in Table 8-7 the $11.5 billion increase in treasury stock from 2016

to 2017, indicating substantial stock buybacks by P&G. Lately, many companies have been aggressively

buying their own stock, reflecting optimism about their future. However, some analysts argue that stock

buybacks eat cash that a firm could better use to grow the firm. Stock buybacks do however reduce a

firm’s number of shares outstanding, thus increasing EPS; so firms reap this “intangible benefit” with

stock buybacks. Sometimes firms will even increase their Treasury Stock near the end of the quarter, or

near the end of the year, to “artificially” inflate their EPS to meet/beat EPS projections to perhaps avoid a

stock price decline. This may be why Home Depot, in 2017–2018, bought back $15 billion of its stock,

Honeywell bought $8 billion of its shares, Bank of America bought back $17 billion in stock, Mastercard

repurchased $4 billion of its common shares, and T-Mobile began a $1.5 billion buyback.

Note on P&G’s actual balance sheets in Table 8-7 that more than 50 percent of total assets are in the

form of goodwill and intangibles; some financial experts suggest these items to be simply “smoke”

because they are not physical assets that can easily be transferred into cash. P&G’s history of acquiring

firms for over book value (market capitalization value) is the reason for the firm’s large goodwill values.









1466003 – Pearson Education Limited ©

Table 8-8 P&G’s Projected Income Statements for 2018, 2019, and 2020 (in millions)

Table 8-9 P&G’s Projected Balanced Sheets for 2018, 2019, and 2020 (in millions)

P&G’s Projected Financial Statements

Table 8-8 and Table 8-9 reveal P&G’s projected income statements and balance sheets, respectively,

given the four recommendations with associated costs listed previously and the annual retained earnings

carried forward to the projected balance sheet. Note, on the projected income statement in Table 8-8

both projected revenues and net income are increasing, but projected net income is down significantly

from the prior year, largely because (as mentioned) there are no major divestitures (recorded as

nonrecurring events on the income statement) in any projected years.

Projected Income Statement 6/30/18 6/30/19 6/30/20

Revenues $66,359 $68,350 $72,451

Cost of Goods Sold 33,180 34,175 36,225

Gross Profit 33,180 34,175 36,225

Operating Expenses 20,571 21,188 22,460

EBIT 12,608 12,986 13,766

Interest Expense 565 562 559

EBT 12,043 12,424 13,207

Tax 2,770 2,858 3,302

Non-Recurring Events 0 0 0

Net Income $9,273 $9,567 $9,905

Projected Balance Sheet 12/31/15 12/31/17 12/31/18

 



1466003 – Pearson Education Limited ©

Assets

Cash and Equivalents $3,015 $7,409 $9,552

Accounts Receivable 4,645 4,784 5,072

lnventory 4,645 4,784 5,072

Other Current Assets 11,945 12,303 13,041

Total Current Assets 24,250 29,281 32,736

Property Plant & Equipment 21,143 22,393 23,643

Goodwill 44,699 44,699 44,699

Intangibles 24,187 24,187 24,187

Other Long-Term Assets 5,309 5,468 5,796

Total Assets 119,588 126,028 131,061

Liabilities

Accounts Payable 15,926 16,404 17,388

Other Current Liabilities 11,281 11,619 12,317

Total Current Liabilities 27,207 28,023 29,705

Long-Term Debt 20,038 20,038 20,038

Other Long-Term Liabilities 14,599 15,037 15,939

Total Liabilities 61,844 63,098 65,682

Equity

Common Stock 4,009 4,009 4,009

Retained Earnings 98,089 100,275 102,725

1466003 – Pearson Education Limited ©

Table 8-10 Author Comments Regarding P&G’s Financial Statements (in millions of

USD)

Treasury Stock (93,715) (93,715) (93,715)

Paid in Capital & Other 49,360 52,360 52,360

Total Equity 57,743 62,929 65,379

Total Liabilities and Equity 119,588 126,028 131,061

Note on the projected balance sheets in Table 8-9 , the increase in long-term debt from fiscal 2017 to

fiscal projected 2018 indicating the proportion (40 percent) of debt financing based on the

recommendations along with the increase in paid-in-capital from fiscal projected 2018 to fiscal projected

2019 representing the proportion (60 percent) of common stock financing per our recommendations.

Table 8-10 provides author comments regarding the projected income statement and balance sheet

changes to reveal rationales for various changes in the statements.

Projected

Income

Statement

Comments Fiscal 2018 Fiscal 2019 Fiscal

2020

Revenues Revenues were flat

from fiscal 2016 to

fiscal 2017 partly due

to significant

divestitures and other

nonrecurring events

totaling more than $5

billion offsetting gains

in revenues.

2% increase

based on current

operations and

new revenues

generated from

increased

advertising.

3%

increase

based on

current

operations

and new

revenues

generated

from

increased

advertising.

6%

increase

based on

current

operations

and new

revenues

generated

from

increased

advertising

and from

new plants

providing

new

products

to sell.





1466003 – Pearson Education Limited ©

Cost of

Goods Sold

Historically 50% of

revenues

50% 50% 50%

Operating

Expenses

Historically 29% of

revenues adjusted to

31% to account for

increased R&D

expenditures and

increased advertising

31% 31% 31%

Interest

Expense

Financing $2,000

million by debt in

fiscal 2018 at 5%.

Slight interest

deduction in fiscal

2019 and 2020 as

debts are paid, but

interest expense is

much higher than in

2017 when P&G did

not service this new

debt.

Tax Tax rate remains

constant at 23% until

fiscal 2020 when

increased revenues

eventually move PG

into higher tax

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.

Do you need an answer to this or any other questions?

Order Plagiarism Free Answer Here

About Wridemy

We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.

How It Works

To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Are there Discounts?

All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.

Hire a tutor today CLICK HERE to make your first order

Related Tags

Academic APA Writing College Course Discussion Management English Finance General Graduate History Information Justify Literature MLA