Excel work

profileKikotynr
IndividualproformastudentversionFirstnameLastname11.xlsm
Home>Business & Finance homework help>Excel work

Instructions

FIN 4873 Individual Project Instructions In this project, you will have a chance to produce pro forma financial statements for a public company. Based on historical data, you will make projections of income statements and balance sheets for three years out of sample. You will need submit your spreadsheet file. Where to get the data? Go to https://www.sec.gov/edgar/searchedgar/legacy/companysearch.html. Search for Lyondell Basell (ticker LYB) and then limit your documents search to “10-k” which is the report the company files with SEC that includes financial statements you need. Basic requirements on your spreadsheets Compile data from 2016-2020 from Edgar to fill out the historical data in the “financial statement” tab. Professionally format all entries. Specifically I’ll be looking for number formats, displaying percent to the 0.1% place, and borders to separate calculated totals. Use the max (min) revenue growth during the 2016-2020 years for a high (low) revenue projection for 2021. Then create an average revenue growth estimate for 2021. Document the necessary calculations to support your revenue estimates in the “revenue estimate” tab provided. Extend the average revenue growth case from 2022 to 2025. Create a projected income statement and balance sheet for 2021 for each of the three cases. Then extend the average case to 2025. Provide a brief description of the assumptions for each line item similar to what we did in class. In some cases I’ve given you directions on what to assume for the line item. Calculate the payout ratio that balances the balance sheet. Use equations whenever and wherever possible – especially for totals. When to submit the spreadsheets? Each individual should submit a project with 3 separate tabs (rubric, revenue estimate, financial statements) in blackboard.

rubric

rubric
2016-2020 data 285 57 items per year
formatted data 152 Percents to 0.1%, dollar format, underline totals
equation/references 82 41 calc & intermediate calc - each worth 2 pts
revenue estimate 45 High, low, average case - 10 pt / 10 pt / 25 pt
assumption justification 37 1 point for each justificaiton not in red
399 Pro Forma 1 point per cell
0 1000
0

Sheet1

User Save Time
elr682 10/19/21 23:38
elr682 10/27/21 14:53
elr682 10/27/21 15:51
10/28/21 13:45

revenue estimate

High Revenue Max growth
Dec. 31, 2016 Dec. 31, 2017 Dec. 31, 2018 Dec. 31, 2019 Dec. 31, 2020 2021 Comments
Sales and other operating revenues:
Trade
Related parties
Sales revenue net
Low revenue Min growth
Dec. 31, 2016 Dec. 31, 2017 Dec. 31, 2018 Dec. 31, 2019 Dec. 31, 2020 2021 Comments
Sales and other operating revenues:
Trade
Related parties
Sales revenue net
Average revenue Avg growth
Dec. 31, 2016 Dec. 31, 2017 Dec. 31, 2018 Dec. 31, 2019 Dec. 31, 2020 2021 Comments 2022 2023 2024 2025
Sales and other operating revenues:
Trade
Related parties
Sales revenue net

Financial Statements

Consolidated Statements of Income - USD ($) $ in Millions Comments Low Sales High Sales Average Sales
Dec. 31, 2016 Dec. 31, 2017 Dec. 31, 2018 Dec. 31, 2019 Dec. 31, 2020 2021 2021 2021 2022 2023 2024 2025
Sales and other operating revenues:
Trade
Related parties
Sales revenue net
Operating costs and expenses:
Cost of sales
Impairment of long-lived assets Assume this is a one time expense
Selling, general and administrative expenses
Research and development expenses
Costs and expenses
Operating income
Interest expense
Interest income
Other income, net
Income from continuing operations before equity investments and income taxes
Income from equity investments
Income from continuing operations before income taxes
(Benefit from) provision for income taxes exclude tax credit in tax rate calc
Income from continuing operations
Loss from discontinued operations, net of tax
Net income
Consolidated Balance Sheets - USD ($) $ in Millions Dec. 31, 2016 Dec. 31, 2017 Dec. 31, 2018 Dec. 31, 2019 Dec. 31, 2020
Current assets: [Abstract]
Cash and cash equivalents Assume Percent of Sales
Restricted cash
Short-term investments
Accounts receivable: [Abstract]
Trade, net Assume 2020 percent of sales (not average)
Related parties Assume 2020 percent of sales (not average)
Inventories Assume 2020 percent of sales (not average)
Prepaid expenses and other current assets
Total current assets
Operating lease assets Assume 2019 - 2020 growth rate
Property, plant and equipment, net Fix at 2020 level
Investment in PO joint ventures Cease in 2018
Equity investments Fix at 2020 level
Other investments and long-term receivables Cease in 2018
Goodwill Fix at 2020 level
Intangible assets, net
Other assets
Total assets
Current liabilities: [Abstract]
Current maturities of long-term debt Fix at 2020 level
Short-term debt Fix at 2020 level
Accounts payable: [Abstract]
Trade
Related parties
Accrued liabilities
Total current liabilities
Long-term debt Fix at 2020 level
Operating lease liabilities Assume 2019 - 2020 growth rate
Other liabilities
Deferred income taxes
Redeemable noncontrolling interests Fix at 2020 level
Long term liabilities
Shareholders' equity:
Ordinary shares, EUR0.04 par value, 1,275 million shares authorized Fix at 2020 level
Additional paid-in capital Fix at 2020 level
Retained earnings Calculate the payout ratio that balances balance sheet
Accumulated other comprehensive loss Fix at 2020 level
Treasury stock, at cost Fix at 2020 level
Total Company share of shareholders' equity
Non-controlling interests Fix at 2020 level
Total equity
Total liabilities, redeemable noncontrolling interests and equity
Shares outstanding Fix at 2020 level
Treasury shares held Fix at 2020 level
Total Assets - Total liabilities
Dividend this year
Payout ratio