Invested Capital Excel Template

Visit: www.educba.com
Email: info@educba.com
Let us take the example of a company with shareholder’s equity worth $3,000,000, term loan of $1,000,000,
short-term bonds valued at $2,000,000 and $500,000 of lease obligations. Calculate the invested
capital of the company if cash invested in non-operating activities is $300,000.

Particulars Amount
Total Short-Term Debt $2,000,000
Total Long-Term Debt $1,000,000
Total Lease Obligations $500,000
Total Equity $3,000,000
Non-Operating Cash -$300,000

Invested Capital is calculated using the formula given below

Invested Capital = Total Short-Term Debt + Total Long-Term Debt + Total Lease
Obligations + Total Equity + Non-Operating Cash

Invested Capital $6,200,000

of $1,000,000,
Let us take the example of Apple Inc. for the illustration of invested capital calculation using the financing approach.
According to the latest annual report, the following financial information is available for FY18:

Particulars Amount (in billions)

Commercial Paper $11.96 Total current assets 131,339
Current Portion of Long-Term Debt $8.78 Total non-current asse 234,386
Long-Term Debt $93.74 Total current liabilities -116,866
Total Shareholders’ Equity $107.15 248,859
Cash Generated by Investing Activities $16.07
Cash Generated by Financing Activities -$87.88

Total Short-Term Debt is calculated using the formula given below

Total Short-Term Debt = Commercial Paper + Current Portion of Long-Term Debt

Total Short-Term Debt $20.74

Non-Operating Cash is calculated using the formula given below

Non-Operating Cash = Cash Generated by Investing Activities + Cash
Generated by Financing Activities

Non-Operating Cash -$71.81

Invested Capital is calculated using the formula given below

Invested Capital = Total Short-Term Debt + Total Long-Term Debt + Total Lease
Obligations + Total Equity + Non-Operating Cash

Invested Capital $149.82

financing approach.

Apple Inc.
CONSOLIDATED BALANCE SHEETS
(In millions, except number of shares which September
are reflected
29,in thousands and par value)September 30,
ASSETS:
2018 2017
Current assets:
Cash and cash equivalents $ 25,913 $ 20,289
Marketable securities 40,388 53,892
Accounts receivable, net 23,186 17,874
Inventories 3,956 4,855
Vendor non-trade receivables 25,809 17,799
Other current assets 12,087 13,936
Total current assets 131,339 128,645

Non-current assets:
Marketable securities 170,799
Property, plant and equipment, net 41,304
Other non-current assets 22,283
Total non-current assets 234,386
Total assets $ 365,725 $

LIABILITIES AND SHAREHOLDERS’ EQUITY:

Current liabilities:
Accounts payable $ 55,888 $ 44,242
Other current liabilities 32,687 30,551
Deferred revenue 7,543 7,548
Commercial paper 11,964 11,977
Term debt 8,784 6,496
Total current liabilities 116,866 100,814

Non-current liabilities:
Deferred revenue 2,797 2,836
Term debt 93,735 97,207
Other non-current liabilities 45,180 40,415
Total non-current liabilities 141,712 140,458
Total liabilities 258,578 241,272

Commitments and contingencies

Shareholders’ equity:
Common stock and additional paid-in capital, $0.00001 par value: 12,600,000 shares authorized;
4,754,986 and 5,126,201 shares issued and 40,201 35,867
outstanding, respectively
Retained earnings 70,400 98,330
Accumulated other comprehensive income/(loss) (3,454) (150)
Total shareholders’ equity 107,147 134,047
 Total liabilities and shareholders’ equity $ 365,725 $ 375,319

Apple Inc.
CONSOLIDATED STATEMENTS OF CASH FLOWS

Years ended
September 29, September 30,
2018 2017
beginning of the year $ 20,484
$ 20,289
Cash and cash equivalents,
Operating activities:
Net income 59,531 48,351
Adjustments to reconcile net income to
cash generated by operating activities:
Depreciation and amortization 10,903 10,157
Share-based compensation expense 5,340 4,840

Deferred income tax expense/(benefit) (32,590) 5,966

Other (444) (166)

Changes in operating assets and liabilities:

Accounts receivable, net (5,322) (2,093)

Inventories 828 (2,723)
Vendor non-trade receivables (8,010) (4,254)
Other current and non-current assets (423) (5,318)

Accounts payable 9,175 8,966

Deferred revenue (44) (626)
Other current and non-current liabilities 38,490 1,125

Cash generated by operating 77,434 64,225

activities
Investing activities:
Purchases of marketable securities (71,356) (159,486) (142,428)
Proceeds from maturities of marketable 55,881 31,775
securities
Proceeds from sales of marketable 47,838 94,564
securities
Payments for acquisition of property, plant (13,313) (12,451) (12,734)
and equipment
Payments made in connection with (721) (329)
business acquisitions, net
Purchases of non-marketable securities (1,871) (521)

Proceeds from non-marketable securities 353 126

Other (745) (124)

Cash generated by/(used in) 16,066 (46,446) (45,977)
investing activities
Financing activities:
Proceeds from issuance of common stock 669 555

Payments for taxes related to net share (2,527) (1,874)

settlement of equity awards
Payments for dividends and dividend (13,712) (12,769) (12,150)
equivalents
Repurchases of common stock (72,738) (32,900) (29,722)
Proceeds from issuance of term debt, net 6,969 28,662
 Repayments of term debt (6,500) (3,500)
Change in commercial paper, net (37) 3,852
Cash used in financing activities (87,876) (17,974) (20,890)
Increase/(Decrease) in cash and cash 5,624 (195)
equivalents
Cash and cash equivalents, end of the year $ 25,913 $ 20,289 $ 20,484

Supplemental cash flow disclosure:

Cash paid for income taxes, net $ 10,417 $ 11,591 $ 10,444
Cash paid for interest $ 3,022 $ 2,092 $
r value)September 30,
2017

$ 20,289
53,892
17,874
4,855
17,799
13,936
128,645

194,714
33,783
18,177
246,674
375,319

30,551
7,548
11,977
6,496
100,814

2,836
97,207
40,415
140,458
241,272

35,867
98,330
(150)
134,047
 September 24,
2016
$ 21,120

45,687

10,505
4,210

4,938

486

527
217
(51)
1,055

2,117
(1,554)
(1,906)

66,231

(142,428)
21,258

90,536

(12,734)

(297)

(1,388)

(924)
(45,977)

495

(1,570)

(12,150)

(29,722)
24,954
 (2,500)
(397)
(20,890)
(636)

$ 20,484

$ 10,444
$ 1,316
Let us take the example of Walmart Inc. for the illustration of invested capital calculation using an operating approach
According to the latest annual report, the following financial information
is available for FY18. Calculate the invested capital of Walmart Inc. for the year 2018.

Particulars Amount (in billions) Commercial Paper 0

Total Current Assets $59.66 Current Portion of L $ 5,257
Total Current Liabilities $78.52 Long-Term Debt 6,780
Property and Equipment, Net $107.68 Total Shareholders’ E 77,869
Property Under Capital Lease $7.14 Cash Generated by Inv (9,060)
Goodwill (Intangible Assets) $18.24 Cash Generated by Fin (19,875)
6,780
Net Working Capital is calculated using the formula given below 67751
Net Working Capital = Total Current Assets - Total Current Liabilities

Net Working Capital -$18.86

Net Fixed Assets is calculated using the formula given below

Net Fixed Assets = Property and Equipment, Net + Property under Capital Lease

Net Fixed Assets $114.82

Invested Capital is calculated using the formula given below

Invested Capital = Net Working Capital + Net Fixed Assets + Net Intangible Assets

Invested Capital $114.20

calculation using an operating approach.
Minus
As of January 31, Equal
(Amounts in millions) 2018 2017 Plus
ASSETS Plus
Current assets: Plus
Cash and cash equivalents $ 6,756 $ 6,867
Receivables, net 5,614 5,835

Inventories 43,783 43,046

Prepaid expenses and other 3,511 1,941

Total current assets 59,664 57,689

Property and equipment:
Property and equipment 185,154 179,492

Less accumulated depreciation (77,479) (71,782)

Property and equipment, net 107,675 107,710

Property under capital lease and

financing obligations:
Property under capital lease and 12,703
11,637

financing obligations
Less accumulated amortization (5,560)
(5,169)

Property under capital lease 7,143 6,468

and financing obligations, net

Goodwill 18,242 17,037

Other assets and deferred charges 11,798 9,921

Total assets $ 204,522 $ 198,825

LIABILITIES AND EQUITY

Current liabilities:
Short-term borrowings $ 5,257 $ 1,099
Accounts payable 46,092 41,433

Accrued liabilities 22,122 20,654

Accrued income taxes 645

921

Long-term debt due within one year 3,738 2,256

Capital lease and financing 667

565

obligations due within one year

Total current liabilities 78,521 66,928

Long-term debt 30,045 36,015

Long-term capital lease and financing 6,780 6,003

obligations
Deferred income taxes and other 8,354 9,344

Commitments and contingencies

Equity:
Common stock 295
305

Capital in excess of par value 2,648 2,371

Retained earnings 85,107 89,354

Accumulated other comprehensive (10,181) (14,232)

loss
Total Walmart shareholders' 77,869 77,798
equity
 Noncontrolling interest 2,953 2,737

Total equity 80,822 80,535

Total liabilities and equity $ 204,522 $ 198,825

Fiscal Years Ended January 31,

(Amounts in millions) 2018 2017 2016
Cash flows from operating activities:

Consolidated net income $ 10,523 $ 14,293 $ 15,080

Adjustments to reconcile
consolidated net income to net cash
provided by operating activities:

Depreciation and amortization 10,529 10,080 9,454

Deferred income taxes (304) 761 (672)

Loss on extinguishment of debt 3,136 — —

Other operating activities 1,210 206 1,410

Changes in certain assets and liabilities, net
of effects of acquisitions:

Receivables, net (1,074) (402) (19)

Inventories (140) 1,021 (703)
Accounts payable 4,086 3,942 2,008
Accrued liabilities 928 1,280 1,466
Accrued income taxes (557) 492 (472)

Net cash provided by operating activities 28,337 31,673 27,552

Cash flows from investing activities:

Payments for property and (10,051) (10,619) (11,477)
equipment
Proceeds from the disposal of 378 456 635
property and equipment
Proceeds from the disposal of 1,046 662 246
certain operations
Purchase of available for sale — (1,901) —
securities
Business acquisitions, net of cash (375) (2,463) —
acquired
Other investing activities (58) (122) (79)
Net cash used in investing activities (9,060) (13,987) (10,675)

Cash flows from financing activities:

Net change in short-term 4,148 (1,673) 1,235
borrowings
Proceeds from issuance of long- 7,476 137 39
term debt
Repayments of long-term debt (13,061) (2,055) (4,432)
Premiums paid to extinguish debt (3,059) — —

Dividends paid (6,124) (6,216) (6,294)

Purchase of Company stock (8,296) (8,298) (4,112)
 Dividends paid to noncontrolling (690) (479) (719)
interest
Purchase of noncontrolling interest (8) (90) (1,326)

Other financing activities (261) (398) (676)

Net cash used in financing activities (19,875) (19,072) (16,285)

Effect of exchange rates on cash and cash

487 (452) (1,022)
equivalents
Net increase (decrease) in cash and cash
(111) (1,838) (430)
equivalents
Cash and cash equivalents at beginning of 6,867 8,705 9,135
year
Cash and cash equivalents at end of year $ 6,756 $ 6,867 $ 8,705

Supplemental disclosure of cash flow

information:
Income taxes paid 6,179 4,507 8,111
Interest paid 2,450 2,351 2,540
Current assets 59,664
Non-interest-bearing current liabilities  68,859 Add
Net working capital -9,195 Equal
Net property, plant, and equipment 107,675 Plus
Goodwill 18,242 Plus
Other operating assets (Capital Lease) 7,143 Plus
133,060 Plus
Equal
Invested capital 123,865

34,689
Fiscal Years Ended January 31,
Short-term debt $ 9,662
Long-term debt 36,825
Total debt $ 46,487
Deferred taxes 8,354
Other long-term liabilities
Preferred stock 2953
Shareholders’ equity 77,869
Total equity 80822
Invested capital 89,176
Explanation
Using the financing approach, the formula for invested capital can be derived by using the follow

Step 1: Firstly, determine the total short-term debt of the subject company, which will include the short-term borrowings, rev

Step 2: Next, determine the total long-term debt of the company, which will include term loan, promissory notes, senior notes

Step 3: Next, determine the total lease obligations which are the aggregate of the present value of the future lease payments.

Step 4: Next, determine the total equity of the company which is the summation of common stock, reserve & surplus, addition

Step 5: Next, determine the non-operating cash & investment which is the aggregate of cash generated from financing and inv

Step 6: Finally, the formula for invested capital can be derived by adding total short-term debt (step 1), total long-term debt (s

Invested Capital = Total Short-Term Debt + Total Long-Term Debt + Total Lease Obligations + Total Equity + Non-Operating C

Using the operating approach, the formula for invested capital can be derived by using the follow

Step 1: Firstly, determine the company’s networking capital requirement, which is the summation of inventory holding and ac

Step 2: Next, determine the net fixed assets of the company, which is gross fixed assets minus accumulated depreciation.

Step 3: Next, determine the net tangible assets, which are gross tangible assets minus accumulated amortization.

Step 4: Finally, the formula for invested capital can be derived by adding net working capital, net fixed assets, and net intangib

Invested Capital = Net Working Capital + Net Fixed Assets + Net Intangible Assets

Relevance and Use of Invested Capital Formula

It is important to understand the concept of invested capital because usually companies use it as a source of funds to either p

Excess cash. There are two schools of thought on excess cash. The first says that a company is the steward
of capital and hence should earn an appropriate return on all of the capital on its balance sheet. This group
argues that it’s proper to calculate ROIC including all cash and marketable securities. HOLT, for example,
takes this viewpoint. Academic research shows there is a basis for this argument. Specifically, the market
values $1.00 in cash at roughly $0.40-$0.90 for companies that are deemed to have poor corporate
governance

The second school believes that while earning the cost of capital is critical, investors should treat the ROIC
calculation and capital allocation issues separately. The goal of an ROIC calculation is to understand how
efficiently a company uses its operating capital. The capital allocation assessment should focus on the likely
ways a company will deploy its capital and what the prospective returns may look like

So how do you strip out excess cash? The idea is to include only the amount of cash a company needs to run
its business. Some considerations include the cash a company needs until it reaches free cash flow breakeven,
the cash to fund all capital needs for two to three years, and the cash a company needs to run its
business day to day. Of course, the proper number varies based on the nature of the business, its cash
conversion cycle, earnings volatility, and where the business is in its life cycle.
Once a company reaches a steady state, a rule of thumb suggests you should include two percent of sales as
cash. For less predictable companies with greater growth prospects, a ratio of cash to sales of five percent
may be appropriate. Research shows that more established companies with strong credit ratings and access
to capital tend to hold less cash as a percentage of assets, and younger firms with brighter growth
opportunities and riskier cash flows hold more cash.

The treatment of excess cash and marketable securities also highlights why calculating invested capital solely
from the right hand side of the balance sheet is potentially misleading. Without reviewing how much excess
cash a company has (an asset), you won’t know whether or not you should adjust equity to reflect that excess
cash. If you remove excess cash from the asset side of the balance sheet, then you need to make an identical
adjustment to shareholders’ equity. That way both sides of the ledger remain balanced.
ved by using the following steps:

he short-term borrowings, revolving facilities and the current portion of long-term debt.

promissory notes, senior notes, etc.

of the future lease payments.

ck, reserve & surplus, additional paid-in capital, etc.

nerated from financing and investing activities.

step 1), total long-term debt (step 2), total lease obligations (step 3) and total equity (step 4) minus cash & investments not needed for ope

otal Equity + Non-Operating Cash & Investments

ved by using the following steps:

on of inventory holding and accounts receivable minus trade payable.

ccumulated depreciation.

ted amortization.

t fixed assets, and net intangible assets as shown below.

s a source of funds to either purchase fixed assets or to cover day-to-day  operating expenses. Inherently, companies prefer this source of

n the likely

needs to run
ow breakeven,

nt of sales as

s and access

capital solely
much excess
ct that excess
e an identical
vestments not needed for operations (step 5) as shown below.

mpanies prefer this source of funding before opting to take out a loan from the bank. On the other hand, an investor uses invested capita
investor uses invested capital primarily to calculate the  return on invested capital (ROIC) to monitor the investment profitability.
vestment profitability.