This is a case study to help you understand the financial statements and learn how to work with financial data in excel. You will use the following scenario: Jenny Cochran, a graduate of

This is a case study to help you understand the financial statements and learn how to work with financial data in excel. You will use the following scenario:

Jenny Cochran, a graduate of The University of Tennessee with 4 years of experience as an equities analyst, was recently brought in as assistant to the chairman of the board of Computron Industries, a manufacturer of computer components.

During the previous year, Computron had doubled its plant capacity, opened new sales offices outside its home territory, and launched an expensive advertising campaign. Cochran was assigned to evaluate the impact of the changes. She began by gathering financial statements and other data.

Instructions

Download and complete this Excel worksheet Download Download and complete this Excel worksheetfor the case study and submit as an .xlsx or .xls file.

Please use Excel functions to show your answers so that your instructor can see your calculation details for full credit. 

Mini Case

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.

Related Posts

All Rights Reserved Terms and Conditions
College pals.com Privacy Policy 2010-2018

11/20/18
Chapter 2 Mini Case
Situation
Jenny Cochran, a graduate of The University of Tennessee with 4 years of experience as an equities analyst, was recently brought in as assistant to the chairman of the board of Computron Industries, a manufacturer of computer components. During the previous year, Computron had doubled its plant capacity, opened new sales offices outside its home territory, and launched an expensive advertising campaign. Cochran was assigned to evaluate the impact of the changes. She began by gathering financial statements and other data.
Computron's Balance Sheets (Millions of Dollars)
2018 2019
Assets
Cash and equivalents $ 60 $ 50
Short-term investments 100 10
Accounts receivable 400 520
Inventories 620 820
Total current assets $ 1,180 $ 1,400
Gross fixed assets $ 3,900 $ 4,820
Less: Accumulated depreciation 1,000 1,320
Net fixed assets $ 2,900 $ 3,500
Total assets $ 4,080 $ 4,900
Liabilities and equity
Accounts payable $ 300 $ 400
Notes payable 50 250
Accruals 200 240
Total current liabilities $ 550 $ 890
Long-term bonds 800 1,100
Total liabilities $ 1,350 $ 1,990
Common stock 1,000 1,000
Retained earnings 1,730 1,910
Total equity $ 2,730 $ 2,910
Total liabilities and equity $ 4,080 $ 4,900
Computron's Income Statement (Millions of Dollars)
2018 2019
Net sales $ 5,500 $ 6,000
Cost of goods sold (Excluding depr. & amort.) 4,300 4,800
Depreciation and amortizationa 290 320
Other operating expenses 350 420
Total operating costs $ 4,940 $ 5,540
Earnings before interest and taxes (EBIT) $ 560 $ 460
Less interest 68 108
Pre-tax earnings $ 492 $ 352
Taxes (25%) 123 88
Net Income $ 369 $ 264
Notes:
a Computron has no amortization charges.
Other Data 2018 2019
Stock price $50.00 $30.00
Shares outstanding (millions) 100 100
Common dividends (millions) $90 $84
Tax rate 25% 25%
Weighted average cost of capital (WACC) 10.00% 10.00%
Computron's Statement of Cash Flows (Millions of Dollars)
Bart Kreps: The statement of cash flows provides information about cash inflows and outflows during an accounting period.
2019
Operating Activities
Net Income before preferred dividends $ 264
Noncash adjustments
Depreciation and amortization 320
Due to changes in working capital
Change in accounts receivable (120)
Change in inventories (200)
Change in accounts payable 100
Change in accruals 40
Net cash provided by operating activities $ 404
Investing activities
Cash used to acquire fixed assets $ (920)
Bart Kreps: Make sure to add back annual Depreciation to Net PP&E.
Bart Kreps: The statement of cash flows provides information about cash inflows and outflows during an accounting period. Change in short-term investments 90
Net cash provided by investing activities $ (830)
Financing Activities
Change in notes payable $ 200
Change in long-term debt 300
Payment of cash dividends (84)
Net cash provided by financing activities $ 416
Net change in cash and equivalents $ (10)
Cash and securities at beginning of the year 60
Cash and securities at end of the year $ 50
a. (1.) What effect did the expansion have on sales and net income? (2 pts)
a. (2.) What effect did the expansion have on the asset side of the balance sheet? (2 pts)
b. What do you conclude from the statement of cash flows? (2 pts)
c. What is free cash flow? Why is it important? What are the five uses of FCF? (2 pts)
d. What is Computron’s net operating profit after taxes (NOPAT)? What are operating current assets? What are operating current liabilities? How much net operating working capital and total net operating capital does Computron have? (7 pts)
Net Operating Profit After Taxes
NOPAT is the amount of profit Computron would generate if it had no debt and held no financial assets.
2019 NOPAT = EBIT x ( 1 – T )
= x
=
2018 NOPAT = EBIT x ( 1 – T )
= x
=
Net Operating Working Capital
Those current assets used in operations are called operating current assets, and the current liabilities that result from operations are called operating current liabilities. Net operating working capital is equal to operating current assets minus operating current liabilities.
2019 NOWC = Operating current assets − Operating current liabilities
= −
=
2018 NOWC = Operating current assets − Operating current liabilities
= −
=
Total Net Operating Capital (TNOC)
TNOC = NOWC + net operating long-term assets
2019 TNOC = NOWC + Fixed assets
= +
=
2018 TNOC = NOWC + Fixed assets
= +
=
e. What is Computron’s free cash flow (FCF)? What are Computron’s “net uses” of its FCF? (4pts)
Free Cash Flow
Computron's Free Cash Flow calculation is the cash flow actually availabe for distribution to investors after the company has made all necessary investments in fixed assets and working capital to sustain ongoing operations.
2019 FCF = NOPAT − Net Investment in Operating Capital
= −
=
Uses of FCF 2019
After-tax interest payment =
Reduction (increase) in debt =
Payment of dividends =
Repurchase (Issue) stock =
Purchase (Sale) of short-term investments =
Total uses of FCF =
f. Calculate Computron’s return on invested capital (ROIC). Computron has a 10% cost of capital (WACC). What caused the decline in the ROIC? Was it due to operating profitability or capital utilization? Do you think Computron’s growth added value? (6pts)
Return on Invested Capital
The Return on Invested Capital tells us the amount of NOPAT per dollar of operating capital.
2019 ROIC = NOPAT ÷ Operating Capital
=
=
2018 ROIC = NOPAT ÷ Operating Capital
=
=
Operating Profitability
The operating profitability (OP) ratio shows how many dollars of operating profit are generated by each dollar of sales.
2019 OP = NOPAT ÷ Sales
=
=
2018 OP = NOPAT ÷ Sales
=
=
Capital Utilization
The capital utilization (CR) ratio shows how many dollars of operating assets are needed to generated a dollar of sales.
2019 CR = Total Op. Cap. ÷ Sales
=
=
2018 CR = Total Op. Cap. ÷ Sales
=
=
Operating profitability declined and the capital utlization worsened, each contributing to the big decrease in ROIC.
g. What is Computron's EVA? The cost of capital was 10% in both years. (4pts)
Economic Value Added
Economic Value Added represents Computron's residual income that remains after the cost of all capital, including equity capital, has been deducted.
2019 EVA = NOPAT − Operating Capital x WACC
= − x