FIN 330 Excel Technology Inc Financial Statement Spreadsheet

User Generated

zbun44

Business Finance

FIN 330

Northern Arizona University

FIN

Description

Build a one sheet model applying the five step process to calculate FCF, the uses of FCF, and other performance measures for Technology Inc.. Your spreadsheet should contain the following sections:

1) FCF: consisting of calculations for NOPAT, NOWC, TOC, NIOC, and FCF.

2) Uses of FCF

3) Performance evaluation: ROIC, OP, CR, MVA, and EVA.

The model sheet must display each component of the formula. Columns and rows should be clearly labeled showing the equation for each formula. Each factor of the equation should be presented separately and used for the result. For example the OP Ratio = NOPAT / Sales. When presenting, you will create a cell which shows NOPAT, one that shows Sales, and one for the result. All original numbers used should reference a cell from the financial statements tab.

Unformatted Attachment Preview

A B с D E F G H 1 J 5 Previous Closing Date 12/31/2018 6 7 8 Technology Inc. Balance Sheet as of December 31, 2019 and 2018 (in millions of dollars) Technology Inc. Income Statement for the Years Ending December 31, 2019 and 2018 (in millions of dollars) 9 10 1-1 12 12/31/2019 12/31/2018 ASSETS 13 14 Assets Current Assets: 15 16 17 18 Cash and equivalents Short-term investments Accounts Receivable Inventory Total 19 $ 47.25 $ 3.80 $ 283.50 $ 141.75 $ 476.30 $ 45.00 $ 3.60 $ 270.00 $ 135.00 $ 453.60 2019 2018 $ 945.00 $ 900.00 $ 812.70 $ 774.00 $ 132.30 $ 126.00 $ 33.10 $ 31.50 $ 99.20 $ 94.50 $ 10.40 $ 8.90 $ 88.80 $ 85.60 $ 22.20 $ 21.40 $ 66.60 $ 64.20 $ 43.30 $ 41.23 $ 23.30 $ 22.97 Net Sales COGS EBITDA Depreciation and amortization EBIT (operating income) Less: Interest EBT Taxes (25%) NET INCOME Less: Common Stock Dividends Addition to Retained Earnings 20 21 22 23 Fixed Assets: Net plant and equipment 24 $ 330.75 $ 315.00 25 26 Total assets $ 807.05 $ 768.60 27 28 12/31/2019 12/31/2018 LIABILITIES & EQUITY Per share data Common shares (millions) Common stock price Earnings per share (EPS) Dividends per share (DPS) Book value per shares (BVPS) 29 15 $ 66.00 $ 4.44 $ 2.89 $ 37.19 15 $ 65.00 $ 4.28 $ 2.75 $ 35.64 30 31 32 Liabilities and Equity Current liabilities: Accounts payable Accruals Notes Payable Total 33 $ 94.50 $ 47.25 $ 17.40 $ 159.15 $ 90.00 $ 45.00 $ 9.00 $ 144.00 34 Additional data Tax Rate WACC Days per year 35 25% 10.0% 365 25% 10.0% 365 36 37 38 Long-term bonds Total Liabilites Common stock (15M shares) Retained Earnings $ 90.00 $ 249.15 $ 444.60 $ 113.30 $ 90.00 $ 234.00 $ 444.60 $ 90.00 39 40 41 Total Common Equity Total Liabilities and Equity $ 557.90 $ 807.05 $ 534.60 $ 768.60 42 43 44 45 Technology Inc. Statement of Cash Flows for the Year Ending December 31, 2019 46 47 48 Cash Provided (Used) 49 50 51 $ 66.60 52 53 54 $ 33.10 55 Operating Activities Net Income before preferred dividends Adjustments Noncash adjustments Depreciation and amortization Due to changes in working capital Increase in accounts receivable Increase in inventories Increase in accounts payable Increase in accruals Net cash provided (used) by operating activities 56 57 58 $ (13.50) $ (6.75) $ 4.50 $ 2.25 $ 86.20 59 60 61 62 63 Investing Activities Cash used to acquire fixed assets Purchase of short-term investments Net cash provided (used) by investing activities 64 $ (48.85) $ (0.20) $ (49.05) 65 66 67 68 69 Financing Activities Increase in notes payable Change in bonds outstanding Payment of common dividends Net cash flow from financing activities $ 8.40 $ $ (43.30) $ (34.90) 70 71 72 73 74 Summary Net change in cash Cash at beginning of year Cash at end of year 75 76 $ 2.25 $ 45.00 $ 47.25 $ 2.25 check 77 78 79 80
Purchase answer to see full attachment
Explanation & Answer:
1 Excel Spreadsheets
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Financial Period
EBIT (operating income)
NOPAT
Notes payable
Long-term bonds
Total Equity

2019

2018

Formulas

Fniancial Period
E...


Anonymous
Great! Studypool always delivers quality work.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags