projected income statement

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

brackets in some

markets; some

countries have higher

tax rates than the 21%

U.S. corporate rate.

23% 23% 25%

Nonrecurring

Events

No selling of

properties or any

other major one year

changes such as

divestures

0 0 0

$2,000×0.05=$100 −$3 −$3

1466003 – Pearson Education Limited ©

Projected

Balance

Sheet

Comments Fiscal 2018 Fiscal 2019 Fiscal

2020

Cash Cash is the plug figure

used to balance the

projected balance

sheets and is

calculated

automatically with the

template. No entry or

estimations for cash

are required whether

using the template or

performing by hand.

Simply enter the

number for Cash that

creates balance

where Total Assets =

Total Liabilities +

Owners Equity.

Accounts

Receivable

Historically 7% of

revenue

7% 7% 7%

Inventory Historically 7% of

revenue

7% 7% 7%

Other

Current

Assets

Historically 18% of

revenue

18% 18% 18%

Property

Plant &

Equipment

Adding five new

plants per year at

$250 million each.

Note P&G will not see

substantial revenue

from the new plants

until the plants are

completed in fiscal

2020.

$1,250 $1,250 $1,250

1466003 – Pearson Education Limited ©

Goodwill No new acquisitions 0 0 0

Intangibles No new patents 0 0 0

Other Long-

Term Assets

Historically 8% of

revenue

8% 8% 8%

Liabilities

Accounts

Payable

Historically 26% of

revenue; forecasting

firm operates more

efficiently after

restructuring over

fiscal 2016 and 2017.

24% 24% 24%

Other

Current

Liabilities

Historically 21% of

revenue; expecting

firm to operate more

efficiently after

restructuring over

fiscal 2016 and 2017.

17% 17% 17%

Long-Term

Debt

Adjust fiscal 2018 to

account for debt

financing. No debt

financing in either

fiscal 2019 or 2020.

$2,000 0 0

Other Long-

Term

Liabilities

Historically 25% of

revenue; expecting

firm to operate more

efficiently after

restructuring over

fiscal 2016 and 2017.

22% 22% 22%

Equity

Common

Stock

Function of par value

when the stock was

issued × shares

0 0 0

1466003 – Pearson Education Limited ©

outstanding. Keep at

0.

Treasury

Stock

No stock buybacks

recommended by firm.

0 0 0

Paid in

Capital and

Other

Financed $3,000

million through equity

in fiscal year 2019

0 $3,000 0

Additional

Retained

Earnings

Net Income Current

Year (or latest

projected year) ‒

current dividends to

be paid = new

additional retained

earnings

$1,965 $2,186 $2,450

Total

Dividends to

Pay

Increase dividends by

1% each year

$7,308 $7,381 $7,455

1466003 – Pearson Education Limited ©

Table 8-11 P&G’s Retained Earnings Data Table (in millions)

P&G’s Retained Earnings Data Table

As indicated in the prior pages, the key link between a projected income statement and balance sheet is

the annual transfer of a firm’s net income less dividends = retained earnings (RE) to the firm’s balance

sheet. In light of the importance of this transaction, a P&G RE data table given in Table 8-11 on page

256 shows specifically how this annual transfer of money is made.

Dividend Information Balance Sheet Information

1 2 3 4 5

Net

Income

− Dividends

Paid

= RE + Prior Year

BSheet RE

= Current Year

BSheet RE

2018 $ 9,273 $ 7,308 $

1,965

$ 96,124 $ 98,089

2019 $ 9,567 $ 7,381 $

2,186

$ 98,089 $ 100,275

2020 $ 9,905 $ 7,455 $

2,450

$ 100,275 $ 102,725



1466003 – Pearson Education Limited ©

Corporate Valuation

Evaluating the worth of a business is central to strategy implementation because firms acquire other

firms, divisions of other firms, or even divest part of their own firm. Thus, thousands of transactions

occur each year in which businesses are bought or sold for some dollar amount in the United States and

around the world. In fact, about $1.5 trillion in mergers are consummated in the United States annually.

For example, CVS Healthcare is trying to buy Aetna Inc. and Walt Disney is trying to buy a segment of

21st Century Fox. In all such cases, it is necessary to establish the financial worth or cash value of a

business to successfully implement strategies.

Corporate valuation is not an exact science; value is sometimes in the eye of the beholder. Companies

desire to sell high and buy low, and negotiation normally takes place in both situations. The valuation of

a firm’s worth is based on financial facts, but common sense and good judgment enter into the process

because it is difficult to assign a monetary value to some factors—such as a loyal customer base, a

history of growth, legal suits pending, dedicated employees, a favorable lease, a bad credit rating, or

valuable patents—that may not be fully reflected in a firm’s financial statements. Also, different valuation

methods will yield different totals for a firm’s worth. Evaluating the worth of a business truly requires both

qualitative and quantitative skills.

With the recent merger and acquisition boom, S&P 500 firms experienced a 70 percent increase in

goodwill from $1.7 trillion in 2007 to more than $3 trillion in 2017. For example, with the acquisition of

SAB Miller, Anheuser-Busch InBev recently increased its goodwill to $141 billion, up from $65 billion just

18 months prior, currently accounting for over 50 percent of total assets. Sometimes it is OK to pay more

for a company than its book value (market capitalization = number of shares outstanding × stock price)

if the firm has technology or patents you need or economies of scale you desire, possibly to gain a better

hold on distribution or even to reduce competitive pricing pressure, as in the Anheuser example. However,

buying a company is like buying a house in that paying a “premium” (defined as the amount of money

paid for an acquisition over the market capitalization amount) is usually not financially prudent.

In addition to preparing to buy or sell a business, corporate valuation analysis is often performed when

dealing with the following finance and accounting issues: bank loans, tax calculations, retirement

packages, death of a principal, partnership agreements, and IRS audits. Practically, it is just good

business to know what your firm is worth. This knowledge protects the interests of all parties involved. To

Determine the cash value of the firm, or a division of the firm, using four corporate evaluation methods. LO 8.3

1466003 – Pearson Education Limited ©

estimate the value of a division or segment of a firm, some analysts calculate the total corporate value

and multiply that number by the percentage of revenues the division contributes to the firm.

Corporate Valuation Methods

There are numerous methods to determine the worth of a company, but four methods are most often

used, as described:

Method 1 The Net Worth Method = Shareholders’ Equity (SE) ‒ (Goodwill + Intangibles)

Other terms for SE are Owners’ Equity, Total Equity, or Net Worth, but this line item near the bottom of a

balance sheet represents the sum of common stock, additional paid-in capital, retained earnings, treasury

stock, and other equity items. After calculating total SE, subtract goodwill and intangibles if these items

appear as assets on the firm’s balance sheet. Whereas intangibles include copyrights, patents, and

trademarks, goodwill arises when a firm acquires

PART I

	Welcome to the Free Excel Student Template Version 18.1
	Dear Student,
	By using this Template, you hereby agree to the Copyright terms and conditions. This Template should save you considerable time and allow for your presentation to be more professional. Do not mistake this Template for doing all of the work. Your assignment is to analyze and present strategies for the next three years. You will still need to do the research and enter key internal and external information into the Template. The Template does not gather or prioritize information. It does however assimilate information you enter in a professional way and does many calculations for you once that critical information is entered. Refer to the David & David textbook for conceptual guidelines for developing all matrices and analyses included in this Template. Best of luck with your project. This Template is designed for Textbook editions 17ed and 18th.
	Instructions for Using the Template
1	Please read all Template instructions below carefully before you start each new section of this Template. Only type in the green boxes. Refer to the David, David & David textbook for conceptual guidelines for every matrix and analysis in this Template.
2	This Template is organized into three primary parts: Part I, Part II, and the respective data output pages for your respective matrices. All data entered will be entered into Part I or Part II. Part I consists of data entry in developing matrices, where Part II consists of data entry for your financial information, including ratios, financial statements, and projected financial statements. Blue buttons are provided for navigating within and to Part I, yellow buttons are for navigating within and to Part II, orange buttons are for navigating to the respective matrices and pink buttons are for navigating to your financial output tables. The navigation buttons along the top of Part I and Part II may not be visible for Apple users but all other features should work without any problems.
	Strengths and Weaknesses
1	Enter into the Template exactly 10 strengths and 10 weaknesses, no more and no less. Your factors should be detailed and actionable rather than vague. For example, the strength: "Sales up nicely" is too vague and not actionable; "Sales were up 15% on women's apparel in China during 2018" is stated far better. Always be thinking in terms of divisions when writing strengths and weaknesses. Note women's apparel could be a division for Nike. All divisions do not need to be treated equally; allow more coverage for divisions with more revenue and those most pertinent to your strategic plan.
2	Weights reveal how important a factor is to being successful in the industry. All weights are "industry-based." A factor of 0.10 for example is 5 times more important than a factor of 0.02 for being successful in the industry. Do not be afraid to include factors with lower weights though. To have a factor make your top 10 list (10 strengths for example out of the 100s the firm likely has), justifies its importance, yet it still may be relatively a lot less important to the industry than others factors you include. Also, be mindful with respect to what industry your firm operates. A moderate priced casual hamburger restaurant may have more in common with a moderate priced chicken restaurant than with McDonalds (cheaper fastfood). Automatically considering McDonalds, Burger King, and Wendy's as the "industry" just because they all sell hamburgers may not be appropriate. Here, casual moderated priced restaurants may serve better as the "industry." After entering in the weights, check to make sure the sum of your weights equals 1.0 for your internal factors. Also, arrange your strengths with highly weighted factors listed first; arrange your Weaknesses also with highest weighted factors listed first.
3	In contrast to weights that are industry-based, ratings are company-based and reveal how well your firm is performing. Use the coding scheme given below for ratings in an IFE Matrix: If your strengths are being cut off, simply drag your cursor between the two row numbers on the left to widen the row.
	1 = "the response is poor"
	2 = "the response is average
	3 = "the response is above average"
	4 = "the response is superior"
	Strengths		Weight		Rating
1
2
3
4
5
6
7
8
9
10
	Weaknesses		Weight		Rating
1
2
3
4
5
6
7
8
9
10
	Total Weight (Must Equal 1.00)		0.00
	Opportunities and Threats
1	Enter into this Template exactly 10 opportunities and 10 threats, no more no less. Your factors should be detailed and actionable rather than vague. Keep in mind both opportunities and threats should be external in nature. Ask yourself "Does the firm have control over this factor?" If the answer is yes, then it cannot be an opportunity or threat. For example, as a clothing retailer you may have an opportunity to "start selling clothes in China." This is not an opportunity for two reasons: 1) the firm has internal control over doing business in China, and 2) the statement is a strategy. The underlying opportunity may be "Women in China spent 20% more on athletic apparel in 2018." Note how this opportunity is specific, actionable, divisional, and external (we cannot control the culture or demand for female athletic apparel). All divisions do not need to be treated equally, allow more coverage for divisions with more revenue and those most pertinent to your strategic plan.
2	Weights reveal how important a factor is to being successful in the industry. Read over the #2 tip under strengths and weaknesses above since the same logic applies for the external factors. After entering in the weights, check to make sure your sum of weights equals 1.0 for all 20 external factors. List factors according with highest weight items first.
3	Ratings again are company-based and reflect how well the firm is addressing the particular factor. Use the coding scheme given below for ratings in an EFE Matrix. If your opportunities are being cut off, simply drag your cursor between the two row numbers on the left to widen the row.
	1 = the response is poor"
	2 = "the response is average"
	3 = "the response is above average"
	4 = "the response is superior"
	Opportunities		Weight		Rating
1
2
3
4
5
6
7
8
9
10
	Threats		Weight		Rating
1
2
3
4
5
6
7
8
9
10
	Total Weight (Must Equal 1.00)		0.00
	Competitive Profile Matrix (CPM)
1	To perform the CPM, enter up to 12 critical success factors. You may use some of the ones listed below if you like but try to use ones that are more pertinent to your company. For example, if your case is Delta Airlines, perhaps include on time arrival, extra fees, and frequent flyer points as factors, rather than the canned factors below. In a CPM, factors do not need to be overly specific, but they should be divisional in nature to the extent possible. If Pepsi Co. is your firm, your factors should be about the firm's soda business, Frito Lay business, bottling business, etc. (Pepsi Co competes in a lot more than just soda) rather than just general "advertising." Advertising for what division (business) are you referring to? Frito Lay's advertising, soda marketing, etc. All divisions do not need to be treated equally; allow more coverage for divisions with more revenue and those most pertinent to your strategic plan.
2	After entering in your critical success factors, enter in a weight for each factor; weights are industry-based. Be sure to check the bottom of the "Enter Weight Below" column, to make sure your sum weight is equal to 1.00. It is okay for some factors to receive a low weight and a factor or two to receive a high weight of say 0.20.
3	After entering in your weights, type the name of your company and two other competitors in the corresponding boxes.
4	After entering in the weights and identifying your company and two rival firms, then enter in a Rating (company-based) in the "Enter Rating Below" column for each organization. DO NOT ASSIGN THE COMPANIES THE SAME RATING; TAKE A STAND; MAKE A CHOICE. In a CPM, use the coding scheme provided below for ratings.
	1 = "the response is poor"
	2 = "the response is average"
	3 = "the response is above average"
	4 = "the response is superior"
	Enter 12 Factors Below		Weight		Chick-Fil-A		McDonald's		Popeye's
					Enter Ratings Below
	Customer Service		0.15		3		4		3
	Customer Loyalty		0.15		3		4		4
	Organization Structure		0.05		3		2		1
	Advertising		0.05		4		2		2
	Delivery Time & Cost		0.05		3		3		4
	Global Expansion		0.05		4		4		3
	Sustainability		0.05		3		4		4
	Sales Growth		0.10		3		4		4
	Price Competitiveness		0.10		3		4		4
	Product Quality		0.10		4		4		3
	E-Commerce		0.15		2		3		4
			1.00
	Boston Consulting Group (BCG) Matrix
1	This Template allows for up to 5 divisions. If your company has more than 5 divisions, combine the divisions with the least amount of revenue into division 5, and mention the adjustment to the class during your presentation, or simply focus on the 5 divisions your 3-year plan centers around; check with your professor. <See your firm's Form 10K or Annual Report to find divisional information, and those documents of your rivals> It is excellent to develop a BCG/IE by geographic region, and construct another one by product (if you have the data).
2	In each division, enter a name, followed by the dollar amount in revenues for that division. Do not include M or B for millions or billions, but do drop off zeros. For example, for $100,000,000, you could enter $100,000 or $100, just be consistent.
3	After completing Step 2 in developing a BCG, enter in the dollar amount in revenues for the top rival firm for each division. Note, the top rival may be you and in this situation enter in your company's revenue for that division. Also, note the top rival may be different for different divisions. For example, if your firm is Avon, Avon's top rival in its lipstick division may be Revlon, but for nail polish, the top rival in the industry may be L'Oréal, and in makeup, Avon may be the market leader. There is no need to label the top rival by name, but you could mention in class as part of your presentation. Be sure to enter in all numbers in the same $ format you used in Step 2 above. If you do not have a perfect apples to apples comparison, (possibly a rival firm combines lipstick and makeup, where your firm separates the two) then estimate as best you can and make note in your presentation.
4	Finally, enter in the industry growth rate (IGR) for each division. Generally, taking the top 2 or 3 rivals for each division (along with your firm), adding their numbers together for the current year and the previous year and using the equation (Current Year – Previous Year) / Previous Year is sufficient to estimate guess of the industry growth rate. This is because generally the top 3 players dominate an industry. Note, using this process also weights larger firms more, which is exactly what you desire. Do not use total revenues; instead, use divisional revenues. Division industry growth rates (IGR) must be between -0.20 and 0.20. If outside these ranges, simply use -0.20 or 0.20 and mention during your presentation.
5	Everything is calculated and positioned for you (Other than Industry Growth Rate in Step 4) including the Relative Market Share Position (RMSP). The BCG matrix in this Template does not produce pie slices to show profits. You may wish to discuss divisional profits in your presentation.
	Enter in division names below (If less than 5, leave the other spaces blank and no circles will appear)		Your Firm's Division Revenues		Top Firm in Industry Division Revenues		Division Market Growth Rate (Step 4)		Relative Market Share Position
									NA
									NA
									NA
									NA
									NA
	Internal – External (IE) Matrix
1	This Template allows for up to 5 divisions. If the company has more than 5 divisions, combine the divisions with the least amount of revenue into division 5, and mention the adjustment to the class during your presentation, or simply focus on the 5 divisions that your 3-year plan centers around; check with your professor.
2	Company wide EFE and IFE scores are automatically entered once you complete the EFE and IFE Matrices.
3	Enter in estimated EFE and IFE Scores for your respective divisions.
4	This Template's IE matrix does not produce pie slices to show profits.
	Enter The Name Of Your Firm
	Chick-Fil-A
	Enter in division names below. If less than 5, leave the other spaces blank and no circles will appear. Remember you could use divisions by geographic region for the BCG and by product/service type for the IE (or vice versa).		Your Firm's Division Revenues		Estimated IFE Score		Estimated EFE Score
	Chicken Sandwich Division		$600		3.8		3.9
	Catering Division		$570		2.8		3.2
	Licensing Division		$42		2.2		2.9
	International Division		$180		3.2		3.0
	Digital Division		$350		3.0		3.2
	SPACE Matrix
1	Include up to five factors to assess each SPACE axis: Financial Position (FP), Stability Position (SP), Competitive Position (CP), and Industry Position (IP) and the corresponding rating each factor should receive.
2	You may use the factors provided here, but try to determine key factors related to your company and industry in the same manner you did with the CPM. The calculations are done automatically and the rating scale is provided below.
3	Enter in the estimated FP, SP, CP, and IP numbers for up to two competitors. Or, instead of a competitor, you could show the estimated SPACE values for your firm after your proposed recommendations are implemented, ie a Before and After analysis. Or you could do both, just cut and paste the SPACE into PowerPoint then refill in the new data. It is important you fill in all information or Excel will place a circle(s) at the origin of the SPACE since the default will be (0,0) plot, which is the origin.
	FP and IP
	Positive 1 (worst) to Positive 7 (best)
	CP and SP
	Negative 1 (best) to Negative 7 (worst)
	Enter The Name Of Your Firm
	Chick-Fil-A
			Ratings
	Financial Position (FP)
	Current Ratio		5
	Debt to Equity		7
	Net Income		2
	Revenue		3
	Inventory Turnover		5
	Industry Position (IP)
	Growth Potential		1
	Financial Stability		2
	Ease of Entry into Market		4
	Resource Utilization		5
	Profit Potential		3
			Ratings
	Competitive Position (CP)
	Market Share		-6
	Product Quality		-3
	Customer Loyalty		-4
	Variety of Products Offered		-2
	Control over Suppliers and Distributors		-1
	Stability Position (SP)
	Rate of Inflation		-4
	Technological Changes		-3
	Price Elasticity of Demand		-5
	Competitive Pressure		-2
	Barriers to Entry into Market		-2
	Your firm's X-axis		-0.2
	Your firm's Y-axis		1.2
	Estimated FP
	Estimated IP
	Estimated CP
	Estimated SP
	Competitor 1's X-axis		2.0
	Competitor 1's Y-axis		3.0
	Estimated FP
	Estimated IP
	Estimated CP
	Estimated SP
	Competitor 2's X-axis		-1.0
	Competitor 2's Y-axis		3.0
	Perceptual Map
1	In this Template's Perceptual Map, you may include for up to 10 product categories.
2	Enter in the X axis and Y axis dimensions. For example, if developing a map for frozen foods your X axis could range from "low calorie" to "high calorie," while the Y axis ranges from "low cost" to "high cost."
3	Enter in the products you wish to compare (up to 10); in the example, these products would be different brands of frozen foods available for purchase. After entering in the products, rate each factor on a scale of 1 to 9. In our example, extremely low calorie would receive a score of 1 or 2, and likewise extremely high calorie should receive a score of 8 or 9.
4	To enhance this analysis, you could mentally draw a line  (or two lines) of best fit (through products) and identify areas along the line that do not have (in this example) frozen food products near the line. In this analysis, blank areas of the map are typically the most advantageous for new product creation. Any products that fall well above or below the line, may be over or under serving customers and should be examined closely. Do not blindly follow this rule of thumb however since, for example, a very expensive product may be well off the projected best fit line and yet serve its small customer base quite well. You may with this Template wish to develop several perceptual maps changing your X and Y dimensions. For example, if you are a large food processor, you could examine frozen foods on dimensions other than the ones used here, or you could examine dairy products or any other related products. Simply cut and paste your existing map into Power Point then enter your data for a new map.
	Enter The Name of the Dimensions on the X-axis
	Enter The Name of the Dimensions on the Y-axis
	Enter in up to 10 products		X – axis Rating		Y – axis Rating
	Grand Strategy Matrix
1	The Grand Strategy Matrix allows for entry of your firm and up to 5 divisions
2	Rank the X axis from 1 (Extremely Weak Competitive Position) to 9 (Extremely Strong Competitive Position)
3	Rank the Y axis from 1 (Extremely Slow Market Growth) to 9 (Extremely Rapid Market Growth)		X-axis score		Y-axis score
	SWOT
1	Click on the SWOT Hyperlink below and add your SLOWEST, and WT Strategies.
	QSPM
1.	To perform a QSPM, enter two strategies in the corresponding green boxes below. These two strategies should be derived from your BCG, IE, SPACE, GRAND, and SWOT. In your oral or written project, you will need to provide a recommendations page(s) on your own with the expected cost of each recommendation, ie after performing the QSPM. The recommendations page is followed by an EPS/EBIT Analysis to reveal where best to obtain the needed capital (debt vs equity). You should have multiple recommendations, including perhaps both strategies included in the QSPM, and other strategies for the firm – but no firm can do everything that would benefit the firm due to limited resources.
2.	In developing a QSPM, after entering in your strategies, then rate each strategy based on the strengths, weaknesses, opportunities, and threats (factors). Do not give two strategies the same rating for a particular strength, weakness, opportunity, or threat. (the exception is if you enter 0 to signify a factor "not impacting the choice between strategies" then you MUST enter 0 for both strategies. For example, if Strategy 1 deserves a rating of 4 on a given factor, but that factor has little to do with Strategy 2, just assign a rating of 1 to Strategy 2. (Note QSPM's will have 0's across about one half of the rows). Across each row in performing QSPM analysis, use the rating scale below for AS scores.
	0 = Not applicable		Strategy One		Strategy Two
	1 = Not attractive
	2 = Somewhat attractive
	3 = Reasonably attractive
	4 = Highly attractive
			AS Ratings		AS Ratings
	Strengths
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
			AS Ratings		AS Ratings
	Weaknesses
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
			AS Ratings		AS Ratings
	Opportunities
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
			AS Ratings		AS Ratings
	Threats
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
	You have completed Part 1.

Click The Blue Buttons Below to Navigate Part 1 More Efficiently

Strengths

/xl/drawings/drawing1.xml#'PART%20I'!B13

Perceptual Maps

/xl/drawings/drawing1.xml#'PART%20I'!B255

Weaknesses

/xl/drawings/drawing1.xml#'PART%20I'!B39

Opportunities

/xl/drawings/drawing1.xml#'PART%20I'!B55

Threats

/xl/drawings/drawing1.xml#'PART%20I'!B81

SWOT

/xl/drawings/drawing1.xml#'PART%20I'!B311

CPM

/xl/drawings/drawing1.xml#'PART%20I'!B99

IE Matrix

/xl/drawings/drawing1.xml#'PART%20I'!B155

BCG Matrix

/xl/drawings/drawing1.xml#'PART%20I'!B131

SPACE Matrix

/xl/drawings/drawing1.xml#'PART%20I'!B181

GRAND

/xl/drawings/drawing1.xml#'PART%20I'!B294

QSPM

/xl/drawings/drawing1.xml#'PART%20I'!B317

View IFE Matrix

/xl/drawings/drawing1.xml#'IFE%20'!A1

View IFE Matrix

/xl/drawings/drawing1.xml#'IFE%20'!A1

HOME

/xl/drawings/drawing1.xml#'PART%20I'!A2

View EFE Matrix

/xl/drawings/drawing1.xml#'EFE%20'!A1

View EFE Matrix

/xl/drawings/drawing1.xml#'EFE%20'!A1

View CPM Matrix

/xl/drawings/drawing1.xml#CPM!C2

View CPM Matrix

/xl/drawings/drawing1.xml#CPM!C2

BCG

/xl/drawings/drawing1.xml#BCG!B5

BCG

/xl/drawings/drawing1.xml#BCG!B5

IE

/xl/drawings/drawing1.xml#IE!B2

IE

/xl/drawings/drawing1.xml#IE!B2

SPACE

/xl/drawings/drawing1.xml#SPACE!B2

SPACE

/xl/drawings/drawing1.xml#SPACE!B2

Perceptual Map

/xl/drawings/drawing1.xml#'Perceptual%20Map'!B2

Perceptual Map

/xl/drawings/drawing1.xml#'Perceptual%20Map'!B2

SWOT

/xl/drawings/drawing1.xml#SWOT!A2

QSPM

/xl/drawings/drawing1.xml#QSPM!B2

GRAND

/xl/drawings/drawing1.xml#GRAND!B2

GRAND

/xl/drawings/drawing1.xml#GRAND!B2

QSPM

/xl/drawings/drawing1.xml#QSPM!B2

PART II

Preliminary Financial Data

1

Enter in your preliminary financial data below for your company. This data is used to construct financial statements, financial ratios, and much more.

Income Statement Information

Enter all as Dollar Amounts. Make sure the oldest year is entered into Column 1 throughout this Template. You may NOT Change this sequence as the preset equations will not adjust.

Read the Note to the left CAREFULLY

Reporting Date

Revenue

Cost of Goods Sold

Operating expenses

Interest Expense

Note: If receiving interest credit, enter as NEGATIVE number

Non-recurring Events

Note: If NEGATIVE enter as negative number. Generally this line is for "discontinued operations" and 90% of the time you will enter 0

Tax

Note: If receiving a tax credit, enter as NEGATIVE number

Balance Sheet Information

Current Assets

12/31/99

12/31/99

Cash and equivalents and Short Term Investments

Accounts Receivable

Inventory

Other Current Assets

Long Term Assets

Property, plant & equipment

Goodwill

Intangibles

Other Long-term Assets

Current Liabilities

Accounts Payable

Other Current Liabilities

Long Term Liabilities

Long-term Debt

Other Long-term Liabilities

Equity

Common Stock

Retained Earnings

Treasury Stock

Note: Enter as negative number

Paid in Capital & Other

Company Valuation

1

Enter in the corresponding data below for your firm, and for a rival firm if you desire. The rival can be a firm you wish to acquire or simply just to compare to your case company.

Stockholders' Equity

0

Note: Determined after you complete the preliminary section.

Net Income

0

Note: Determined after you complete the preliminary section.

EPS

ERROR:#DIV/0!

Note: Determined after you complete the preliminary section and enter in # shares outstanding below.

# Shares Outstanding

Note: Using Current # shares outstanding is okay or # of shares outstanding (issued) on the last day of the fiscal year.

Stock Price

Note: Current Stock price is fine, or the closing price on the last day of the fiscal year.

Goodwill & Intangibles

0

Note: Determined after you complete the preliminary section.

Rival Firm's Name

Stockholders' Equity

Net Income

EPS

# Shares Outstanding

Stock Price

Goodwill & Intangibles

EPS/EBIT Analysis

1

Enter in the corresponding data below for your firm.

2

If you notice little to no change in EPS with stock vs debt financing, the total amount of your recommendations is likely too low. Unless of course, you are recommending defensive strategies where you are not acquiring substantial new capital.

Pessimistic

Realistic

Optimistic

EBIT

EPS/EBIT Data

Amounted Needed

Note: This number is the total cost of your recommendations.

Interest Rate

Note: Enter as a decimal.

Tax Rate

Note: Enter as a decimal.

Shares Outstanding

0

Note: Enter in under Company Valuation on this page.

# New Shares Outstanding

ERROR:#DIV/0!

Note: Calculated automatically

Stock Price

$0.00

Note: Enter in under Company Valuation on this page.

Combination Financing Data

Percent Equity Used to Finance

Note: Enter as a decimal.

Percent Debt Used to Finance

Note: Enter as a decimal.

Total Equity and Debt

0.00

Note: Must equal 1.0. Check the two line items above.

Projected Financial Statements

1

Start with the income statement and work your way from top to bottom. Take extreme care to read and understand all notes provided by each line item. See Chapter 8 in the David & David textbook for examples and guidelines in developing projected financial statements.

2

After completing the income statement, begin the balance sheet starting with the "dividends to pay" line near the bottom; finish the equity section of the balance sheet first, then work your way up the statement to the liabilities section, then onto the assets, using the top row (Cash) as the plug figure. A detailed note beside the cash line item explains further.

3

Take care to read all notes to the right of the line items. Consult Chapter 8 of the David & David textbook for excellent explanations and tips for constructing projected statements.

Percentages in the Projected Income Statement will be multiplied by the most recent year. For example, if you enter in 10% for projected revenues in projected year 2, the Template will use the equation (1.10 x projected year 1 revenues) = projected year 2 revenues. For line items in the projected income statement requesting dollar amounts, please read the note below for the balance sheet. The calculations work the same way as described there.

Projected Years (earliest to latest)

Income Statement

Historical Numbers (see notes)

Projected Reporting Date

Historical Percent Notes Below. Enter your data in the EXACT same format as the Notes describe.

Revenues

ERROR:#DIV/0!

Historical Note: Difference the two most recent years of data. Enter percent increases you expect based on your recommendations. Do not blindly use the historical number provided. Enter as percent.

Cost of Goods Sold

ERROR:#DIV/0!

Historical Note: Percent of Sales in the most recent year. Use a similar percent across all three projected years unless you believe COGS to sales percent will change drastically. Enter as percent.

Operating Expenses

ERROR:#DIV/0!

Historical Note: Percent of Sales in the most recent year. Use a similar percent across all three projected years unless you believe Operating Expenses to sales percent will change drastically. Enter as percent.

Interest Expense

$0

Historical Note: Dollar amount of interest paid in the most recent year. Enter in the NEW NET dollar amounts of interest you will forecasted for each year. If your most recent interest payment was $500 and you plan on a $20 net increase in interest for projected year 1, simply enter in $20 for year one. If financing through debt, the number is more likely to increase more than if financing through equity. Enter as dollar amount. If you anticipate less interest expense than the year before, enter as a negative number.

Tax

ERROR:#DIV/0!

Historical Note: Tax Rate in most recent year. You can likely use the same tax rate throughout unless you expect a large increase/decrease in revenues and subsequently EBT. Enter as percent.

Non-Recurring Events

0

Historical Note: Dollar amount of Non-Recurring Events for each year, this number is not cumulative. Safe to forecast this number as $0 in ever year. Enter as dollar amount.

Scroll Down for Balance Sheet

Work from the bottom of the Projected Balance Sheet to the top

Projected Years (earliest to latest)

Balance Sheet (Start at the bottom)

Historical Dollar Amount Paid

The projected Balance Sheet is designed for you to enter in the NET ADDITIONAL DOLLAR VALUES (for PPE, Goodwill, and Intangibles). The Template will add these values to the existing numbers. For Example, if you are adding $1,000 in inventory in projected year 1, (but you estimate your firm used $800 of its existing inventory from the prior year) just enter in $200 ($1,000-$800) in the corresponding box and the Template will use the equation ($200 + most recent historical year Inventory number) = projected year 1 inventory.

Read the message to the right, then start at the bottom with dividends.

Assets

12/31/99

12/31/99

12/31/99

Cash and Equivalents

$0

$0

$0

$0

Historical Note: If your cash number appears too high or low, consult Chapter 8 of the textbook for more information. Also, compare your projected ratios to historical ratios. You may need to make adjustments to your recommendations and/or your projected statements. It is rare for any firm to have acceptal projected statements after the first attempt.

Accounts Receivable

ERROR:#DIV/0!

Historical Note: Percent of revenues in the most recent year. Use a similar percent across all three projected years unless you believe the current assets to revenues percent will change drastically. Enter as percent

Inventory

ERROR:#DIV/0!

Other Current Assets

ERROR:#DIV/0!

Property Plant & Equipment

$0

Historical Note: The values are for the most recent year reported. Enter in the net new (not cumulative) dollar amounts for each item for each forecasted year (Except for the Cash and Equivalents line). If you are purchasing $200 of Property, Plant & Equipment in Projected Year 1, simply enter $200 into the first projected year. If you plan to also reduce existing PP&E by $300, then you would enter in a negative $100 into Projected Year 1 (assuming you still plan to purchase the other $200). Take care with each line time, it is not how fast you get the numbers entered. Reread the hints in red writing a few lines above.

Goodwill

$0

Intangibles

$0

Other Long-Term Assets

ERROR:#DIV/0!

Historical Note: Percent of revenues in the most recent year. Use a similar percent across all three projected years unless you believe the other long-term asets to revenues percent will change drastically. Enter as percent

Liabilities

12/31/99

12/31/99

12/31/99

Accounts Payable

ERROR:#DIV/0!

Historical Note: Percent of revenues in the most recent year. Use a similar percent across all three projected years unless you believe the current liabilities to revenues percent will change drastically. Enter as percent.

Other Current Liabilities

ERROR:#DIV/0!

Long-Term Debt

$0

Historical Note: The values are for the most recent year reported. Enter in the net new (not cumulative) dollar amounts for each item for each forecasted year. For example, if you do not plan to take on any additional long term debt in Projected Year 1, but do plan to pay off $1,000 in debt in Projected Year 1, enter in ($1,000) in Projected Year 1 long term debt column.

Other Long-Term Liabilities

ERROR:#DIV/0!

Historical Note: Percent of revenues in the most recent year. Use a similar percent across all three projected years unless you believe the other long-term liabilities to revenues percent will change drastically. Enter as percent.

Equity

12/31/99

12/31/99

12/31/99

Common Stock

0

Historical Note: The values are for the most recent year reported. Enter in the new (additional, not cumulative) Dollar amounts for each Item for each forecasted year. If you change Treasury Stock, you may need to make an adjustment to Paid in Capital. Enter Treasury Stock as a negative number. Read over Chapter 8 of the David, David and David textbook.

Treasury Stock

0

Paid in Capital & Other

0

Retained Earnings

0

0

0

0

Historical Note: The Retained Earnings value is for the most recent year reported. The new additional (not cumulative) Retained Earnings are calculated automatically.

Total Dividends to Pay

START HERE

Start HERE. Enter the total dollar amount you wish to pay in dividends each forecasted year. If none, enter 0. This line is not cumulative, it does not add the value to any existing value for dividends. For example, if the firm paid $1,000 in dividends and you wish to stop dividend payments, enter $0 in projected year 1 box. If you wish to increase dividends by 10% enter $1,100 into projected year 1 box. Check on your own to see historically what the firm was paying.

Preliminary Financial Data

/xl/drawings/drawing2.xml#'PART%20II'!B2

Income Statement

/xl/drawings/drawing2.xml#'Financial%20Statements'!B5

Balance Sheet

/xl/drawings/drawing2.xml#'Financial%20Statements'!B18

Company Valuation

/xl/drawings/drawing2.xml#'Company%20Valuation'!B3

Rival Firm Valuation

/xl/drawings/drawing2.xml#'Company%20Valuation'!B14

Company Valuation

/xl/drawings/drawing2.xml#'PART%20II'!B71

EPS/EBIT Analysis

/xl/drawings/drawing2.xml#'PART%20II'!B107

Projected Financial Statements

/xl/drawings/drawing2.xml#'PART%20II'!B139

HOME

/xl/drawings/drawing2.xml#'PART%20II'!A2

Balance Sheet

/xl/drawings/drawing2.xml#'Financial%20Statements'!B18

EPS/EBIT Analysis

/xl/drawings/drawing2.xml#EPS_EBIT!C4

IFE

	IFE Matrix
1	If data is missing here, recheck "Part I"
2	Check to make sure your text is not cut off in the matrix. Double click (or drag) between the Cell Numbers.
3	To transfer into Word or Power Point, highlight the matrix, then paste special as "picture"
	Strengths	Weight	Rating	Weighted Score
1	0	0.00	0	0.00
2	0	0.00	0	0.00
3	0	0.00	0	0.00
4	0	0.00	0	0.00
5	0	0.00	0	0.00
6	0	0.00	0	0.00
7	0	0.00	0	0.00
8	0	0.00	0	0.00
9	0	0.00	0	0.00
10	0	0.00	0	0.00
	Weaknesses	Weight	Rating	Weighted Score
1	0	0.00	0	0.00
2	0	0.00	0	0.00
3	0	0.00	0	0.00
4	0	0.00	0	0.00
5	0	0.00	0	0.00
6	0	0.00	0	0.00
7	0	0.00	0	0.00
8	0	0.00	0	0.00
9	0	0.00	0	0.00
10	0	0.00	0	0.00
	Total IFE Score	0.00		0.00

Return to Part I

/xl/drawings/drawing3.xml#'PART%20I'!B26

EFE

	EFE Matrix
1	If data is missing here, recheck "Part I"
2	Check to make sure your text is not cut off in the matrix. Double click (or drag) between the Cell Numbers.
3	To transfer into Word or Power Point, highlight the matrix, then paste special as "picture"
	Opportunities	Weight	Rating	Weighted Score
1	0	0.00	0	0
2	0	0.00	0	0
3	0	0.00	0	0
4	0	0.00	0	0
5	0	0.00	0	0
6	0	0.00	0	0
7	0	0.00	0	0
8	0	0.00	0	0
9	0	0.00	0	0
10	0	0.00	0	0
	Threats	Weight	Rating	Weighted Score
1	0	0.00	0	0.00
2	0	0.00	0	0.00
3	0	0.00	0	0.00
4	0	0.00	0	0.00
5	0	0.00	0	0.00
6	0	0.00	0	0.00
7	0	0.00	0	0.00
8	0	0.00	0	0.00
9	0	0.00	0	0.00
10	0	0.00	0	0.00
	Total EFE Score	0.00		0.00

Return to Part I

/xl/drawings/drawing4.xml#'PART%20I'!B68

Return to Part I

/xl/drawings/drawing4.xml#'PART%20I'!B66

CPM

		CPM Matrix
1		If data is missing here, recheck the "Part I" page.
2		Check to make sure your text is not cut off in the matrix. Double click (or drag) between the Cell Numbers.
3		To transfer into Word or Power Point, highlight the matrix, then paste special as "picture"
				Chick-Fil-A		McDonald's		Popeye's
		Critical Success Factors	Weight	Rating	Score	Rating	Score	Rating	Score
		Customer Service	0.15	3	0.45	4	0.60	3	0.45
		Customer Loyalty	0.15	3	0.45	4	0.60	4	0.60
		Organization Structure	0.05	3	0.15	2	0.10	1	0.05
		Advertising	0.05	4	0.20	2	0.10	2	0.10
		Delivery Time & Cost	0.05	3	0.15	3	0.15	4	0.20
		Global Expansion	0.05	4	0.20	4	0.20	3	0.15
		Sustainability	0.05	3	0.15	4	0.20	4	0.20
		Sales Growth	0.10	3	0.30	4	0.40	4	0.40
		Price Competitiveness	0.10	3	0.30	4	0.40	4	0.40
		Product Quality	0.10	4	0.40	4	0.40	3	0.30
		E-Commerce	0.15	2	0.30	3	0.45	4	0.60
		0	0.00	0	0.00	0	0.00	0	0.00
		Totals	1.00		3.05		3.60		3.45

Return to Part I

/xl/drawings/drawing5.xml#'PART%20I'!D99

BCG

	BCG
	1	If data is missing here, recheck the "Part I" page and read step 3.
	2	Highlight the entire matrix (not just the inside box), and then paste as paste special picture.
	3	If you do not see your circle, either you did not enter in the information or you entered a number for the "Top Firm in the Industry Revenues" smaller than your firm. This number can only be larger or the same (if your firm's division is the largest revenue generator in the industry). It is also possible your bubble is behind another bubble if the information was close to the same, this is unlikely however.
			Please Scroll down for the BCG Matrix and Table
			Relative Market Share Position
			High 1.0				Low 0.0
Industry Sales Growth Rate
	High 0.20
	Low -0.20
	Division				Your Firm's Division Revenues	Top Firm in Industry Division Revenues	Industry Sales Growth Rate	Relative Market Share Position
	northeast				$19,534,240	$19,534,240	0.04	1.00
	southwest				$36,162,560	$36,162,560	0.04	1.00
	west				$19,695,680	$19,695,680	0.04	1.00
	southeast				$79,832,080	$79,832,080	0.04	1.00
	midwest				$19,130,640	$19,130,640	0.04	1.00

northeast 1 0.04 19534240 southwest

1 0.04 36162560 west

1 0.04 19695680 southeast

1 0.04 79832080 midwest

1 0.04 19130640 northeast

1 0.04 19534240

Question Marks

Stars

Cash Cows

Dogs s

Return to Part I

/xl/drawings/drawing6.xml#'PART%20I'!D132

Return to Part I

/xl/drawings/drawing6.xml#'PART%20I'!B144

IE

	IE
1	If data is missing here, recheck the "Part I" page and read step 3.
2	Highlight the entire matrix (not just the inside box), and then paste as paste special picture.
3	If you do not see your circle, either you did not enter in the corresponding EFE or IFE information. It is also possible your bubble is behind another bubble if the EFE and IFE information was close to the same.
						Scroll down for IE Matrix and Table
				THE IFE TOTAL WEIGHTED SCORES
			Strong					Weak
			4.0					1.0
	High
	4.0
THE EFE WEIGHTED SCORES
	Low
	1.0
	Division				Firm's Division Revenues	Estimated IFE Score	Estimated EFE Score
	Chicken Sandwich Division				$600	3.8	3.9
	Catering Division				$570	2.8	3.2
	Licensing Division				$42	2.2	2.9
	International Division				$180	3.2	3.0
	Digital Division				$350	3.0	3.2

Chick-Fil-A 0 0 1 Chicken Sandwich Division

3.8 3.9 600 Catering Division

2.8 3.2 570 Licensing Division

2.2000000000000002 2.9 42 International Division

3.2 3 180 Digital Division

3 3.2 350

Return to Part I

/xl/drawings/drawing8.xml#'PART%20I'!B171

SPACE

	SPACE
1	If data is missing here, recheck the "Part I" page and read step 3.
2	Highlight the entire matrix (not just the inside box), and then paste as paste special picture. Be sure to also include the table below the chart also in your presentation.
3	If you do not see your bubble either you did not enter in the information or, it is also possible your bubble is behind another bubble if the X and Y information were close to the same.
												Chick-Fil-A	0	0
											X Axis	-0.2	2.0	-1.0
											Y Axis	1.2	3.0	3.0
	Internal Analysis:					External Analysis:
	Financial Position (FP)					Stability Position (SP)
	Current Ratio				5	Rate of Inflation				-4
	Debt to Equity				7	Technological Changes				-3
	Net Income				2	Price Elasticity of Demand				-5
	Revenue				3	Competitive Pressure				-2
	Inventory Turnover				5	Barriers to Entry into Market				-2
	Financial Position (FP) Average				4.4	Stability Position (SP) Average				-3.2
	Internal Analysis:					External Analysis:
	Competitive Position (CP)					Industry Position (IP)
	Market Share				-6	Growth Potential				1
	Product Quality				-3	Financial Stability				2
	Customer Loyalty				-4	Ease of Entry into Market				4
	Variety of Products Offered				-2	Resource Utilization				5
	Control over Suppliers and Distributors				-1	Profit Potential				3
	Competitive Position (CP) Average				-3.2	Industry Position (IP) Average				3.0

Return to Part I

/xl/drawings/drawing10.xml#'PART%20I'!B182Chick-Fil-A -0.20000000000000018 1.2000000000000002 1 2 3 1

-1 3 1

FP

SP

CP

IP IPIP

Defensive

Conservative

Aggressive

Competitive

GRAND

	GRAND
1	If data is missing here, recheck the "Part I" page and read Step 3.
2	Highlight the entire matrix (not just the inside box), and then paste as paste special picture.
3	If you do not see your circle, either you did not enter in the corresponding information or it is also possible your bubble is behind another bubble if the axis information was close to the same.

Return to Part I

/xl/drawings/drawing11.xml#'PART%20I'!B299Product development 4 2 1 Market penetration 3 2 1 Customer service 8 8 1 Brand Image 7 4 1 Distribution 9 9 1 Innovation 4 5 1

Quadrant II

Quadrant I

Quadrant III I

Quadrant IV

Rapid Market Growth

Slow Market Growth

Strong Competitive Position

Weak Competitive Position

Perceptual Map

	Perceptual Maps
1	If data is missing here, recheck the "Part I" page and read Step 3.
2	Highlight the entire matrix (not just the inside box), and then paste as paste special picture.
3	If you do not see your circle, either you did not enter in the corresponding information or it is also possible your bubble is behind another bubble if the axis information was close to the same.
					0
		0									0
					0

1 1 1 1 1 1 1 1 1 1

Return to Part I

/xl/drawings/drawing12.xml#'PART%20I'!B256

Financial Statements

1	Complete Part II to Construct the Financial Statements.
	Income Statement			12/31/99	12/31/99		Percent Change
	Revenue (Sales)			$0	$0	NA	NA
	Cost of Goods Sold			0	0	NA	NA
	Gross Profit			0	0	NA	NA
	Operating Expenses			0	0	NA	NA
	EBIT (Operating Income)			0	0	NA	NA
	Interest Expense			0	0	NA	NA
	EBT			0	0	NA	NA
	Tax			0	0	NA	NA
	Non-Recurring Events			0	0	NA	NA
	Net Income			0	0	NA	NA
	Balance Sheet			12/31/99	12/31/99		Percent Change
	Assets
	Cash and Short Term Investments			$0	$0	NA	NA
	Accounts Receivable			0	0	NA	NA
	Inventory			0	0	NA	NA
	Other Current Assets			0	0	NA	NA
	Total Current Assets			0	0	NA	NA
	Property Plant & Equipment			0	0	NA	NA
	Goodwill			0	0	NA	NA
	Intangibles			0	0	NA	NA
	Other Long-Term Assets			0	0	NA	NA
	Total Assets			0	0	NA	NA
	Liabilities
	Accounts Payable			0	0	NA	NA
	Other Current Liabilities			0	0	NA	NA
	Total Current Liabilities			0	0	NA	NA
	Long-Term Debt			0	0	NA	NA
	Other Long-Term Liabilities			0	0	NA	NA
	Total Liabilities			0	0	NA	NA
	Equity
	Common Stock			0	0	NA	NA
	Retained Earnings			0	0	NA	NA
	Treasury Stock			0	0	NA	NA
	Paid in Capital & Other			0	0	NA	NA
	Total Equity			0	0	NA	NA
	Total Liabilities and Equity			0	0	NA	NA

Return to Part II

/xl/drawings/drawing14.xml#'PART%20II'!B2

SWOT

SWOT
	SO Strategies
	1
	2
	3
	4
	ST Strategies
	1
	2
	3
	4
	WO Strategies
	1
	2
	3
	4
	WT Strategies
	1
	2
	3
	4

Return to Part I

/xl/drawings/drawing15.xml#'PART%20I'!B296

QSPM

	QSPM
1	If data is missing here, recheck the "Part I" page.
3	Check to make sure your text is not cut off in the matrix. Double click (or drag) between the Cell Numbers.
			0		0
	Strengths	Weight	AS	TAS	AS	TAS
1	0	0.00	0	0.00	0	0.00
2	0	0.00	0	0.00	0	0.00
3	0	0.00	0	0.00	0	0.00
4	0	0.00	0	0.00	0	0.00
5	0	0.00	0	0.00	0	0.00
6	0	0.00	0	0.00	0	0.00
7	0	0.00	0	0.00	0	0.00
8	0	0.00	0	0.00	0	0.00
9	0	0.00	0	0.00	0	0.00
10	0	0.00	0	0.00	0	0.00
			0		0
	Weaknesses	Weight	AS	TAS	AS	TAS
1	0	0.00	0	0.00	0	0.00
2	0	0.00	0	0.00	0	0.00
3	0	0.00	0	0.00	0	0.00
4	0	0.00	0	0.00	0	0.00
5	0	0.00	0	0.00	0	0.00
6	0	0.00	0	0.00	0	0.00
7	0	0.00	0	0.00	0	0.00
8	0	0.00	0	0.00	0	0.00
9	0	0.00	0	0.00	0	0.00
10	0	0.00	0	0.00	0	0.00
			0		0
	Opportunities	Weight	AS	TAS	AS	TAS
1	0	0.00	0	0.00	0	0.00
2	0	0.00	0	0.00	0	0.00
3	0	0.00	0	0.00	0	0.00
4	0	0.00	0	0.00	0	0.00
5	0	0.00	0	0.00	0	0.00
6	0	0.00	0	0.00	0	0.00
7	0	0.00	0	0.00	0	0.00
8	0	0.00	0	0.00	0	0.00
9	0	0.00	0	0.00	0	0.00
10	0	0.00	0	0.00	0	0.00
			0		0
	Threats	Weight	AS	TAS	AS	TAS
1	0	0.00	0	0.00	0	0.00
2	0	0.00	0	0.00	0	0.00
3	0	0.00	0	0.00	0	0.00
4	0	0.00	0	0.00	0	0.00
5	0	0.00	0	0.00	0	0.00
6	0	0.00	0	0.00	0	0.00
7	0	0.00	0	0.00	0	0.00
8	0	0.00	0	0.00	0	0.00
9	0	0.00	0	0.00	0	0.00
10	0	0.00	0	0.00	0	0.00
	TOTALS			0.00		0.00

Return to Part I

/xl/drawings/drawing16.xml#'PART%20I'!B317

Company Valuation

1	Complete Part II to Construct the Company Valuation
	0
	Stockholders' Equity – (Goodwill + Intangibles)	$0
	Net Income x 5	$0
	(Share Price/EPS) x Net Income	ERROR:#DIV/0!
	Number of Shares Outstanding x Share Price	$0
	Method Average	ERROR:#DIV/0!
	Rival Firm's Name
	Stockholders' Equity – (Goodwill + Intangibles)	$0
	Net Income x 5	$0
	(Share Price/EPS) x Net Income	ERROR:#DIV/0!
	Number of Shares Outstanding x Share Price	$0
	Method Average	ERROR:#DIV/0!

Return to Part II

/xl/drawings/drawing17.xml#'PART%20II'!B71

EPS_EBIT

1	Complete Part II to Construct the EPS/EBIT Charts
			Common Stock Financing				Debt Financing
			Pessimistic	Realistic	Optimistic		Pessimistic	Realistic	Optimistic
		EBIT	$0	$0	$0		$0	$0	$0
		Interest	0	0	0		0	0	0
		EBT	0	0	0		0	0	0
		Taxes	0	0	0		0	0	0
		EAT	0	0	0		0	0	0
		# Shares	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!		0	0	0
		EPS	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!		ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
												Amount Needed				$0
												Interest Rate				0%
		Stock	0%	Debt	0%							Tax Rate				0%
			Pessimistic	Realistic	Optimistic							# Shares Outstanding				0.0
		EBIT	$0	$0	$0							Additional Shares Outstanding Needed				NA
		Interest	0	0	0							Stock Price				$0.00
		EBT	0	0	0
		Taxes	0	0	0
		EAT	0	0	0
		# Shares	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
		EPS	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!

Common Stock Financing 0 0 0 0 0 0 Debt Financing 0 0 0 0 0 0

Return to Part II

/xl/drawings/drawing18.xml#'PART%20II'!B107