I need help with an excel project for accounting

I need help with an excel project for accounting

Computer Project Instruction and Rubric for the grading
ACC 3010 Fall 2022
Professor Seong Yeon Cho
Step 1: Download the excel “Computer Project File” from Moodle.
Step 2: Search for the 10-K report data for the fiscal year 2019, 2020, and 2021
Step 3: Fill in the B85:D116 cells with the corresponding data and put company name and Ticker
assigned in the cell B82 and D82, respectively (40 points).
Step 4: Check cell D59 if there is any error (Checking A=L+SE) (no error gets 40 points).
Step 5: If there is any error, correct the corresponding amount in the data area to make total assets,
total liabilities and Shareholders’ Equity are balanced.
Step 6: Finish the Ratio Analysis Tab and Credit Analysis Tab (20 points)
Step 7: Submit the finished file to Moodle
Warning! Do not touch the other area of the excel spread sheet except for the data input area in the
Financial Statement tab and Ratio analysis, Credit Analysis Tab
Financial Statements
($000s)
Company Name and Ticker
Common Shares Outstanding
0
Fiscal Year End (YYYY-MM-DD)
Instruction: Use the 10-K report data for
the area B85:D116. Do not touch the
other area
Jump to Raw Data Input
0
0 (in 000s at most recent fiscal year end)
Actual
FY2019
Actual
FY2020
Actual
FY2021
Income Statement
Sales (Net)
Cost of Goods Sold
Gross Profit
R&D Expense
SG&A Expense
EBITDA
Depreciation & Amortization
EBIT
Net Interest Expense
Non-Operating Income (Loss)
EBT
Income Taxes
Other Income (Loss)
Net Income Before Ext. Items
Ext. Items & Disc. Ops.
Minority Interest in Earnings
Preferred Dividends
Net Income (available to common)
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Operating Cash and Market. Sec.
Receivables
Inventories
Other Current Assets
Total Current Assets
PP&E (Net)
Investments
Intangibles
Other Assets
Total Assets
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Current Debt
Accounts Payable
Income Taxes Payable
Other Current Liabilities
Total Current Liabilities
Long-Term Debt
Other Liabilities
Deferred Taxes
Total Liabilities
Minority Interest
Preferred Stock
Paid in Common Capital (Net)
Retained Earnings
Total Common Equity
Total Liabilities and Equity
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Statement of Retained Earnings
0
0
Beg. Retained Earnings
+Net Income
-Common Dividends
+/-Clean Surplus Plug (Ignore)
=End. Retained Earnings
0
0
0
0
0
0
0
0
0
0
Balance Sheet
0
Values for Dupont Analysis and Valuation
Net Operating Income
Net Financial Expense
Net Income
Effective Tax Rate
Net Operating Assets
Net Financial Obligations
Total Common Equity
Raw Data Inputs
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0
#DIV/0!
#DIV/0!
#DIV/0!
0
#DIV/0!
0
0
0
0
#DIV/0!
0
0
0
0
0
0
cut-and-paste raw data block from DataMaker file here
Company Ticker Below
Company Name and Ticker
Common Shares Outstanding
Fiscal Year End Form FYxxxx
Sales (Net)
Cost of Goods Sold
R&D Expense
SG&A Expense
Depreciation & Amortization
Interest Expense
Non-Operating Income (Loss)
Income Taxes
Minority Interest in Earnings
Extraordinary Items & Acc Change
Earnings From Discotinued Op.
Preferred Dividends
Operating Cash and Market. Sec.
Receivables
Inventories
Other Current Assets
PP&E (Net)
Investments
Intangibles
Other Assets
Current Debt
Accounts Payable
Income Taxes Payable
Other Current Liabilities
Long-Term Debt
Other Liabilities
Deferred Taxes
Minority Interest
Preferred Stock
Paid in Common Capital (Net)
Retained Earnings
Common Dividends
FY2019
FY2020
FY2021
Cash Flow Analysis
($000)
Company Name
Do not tocu this tab
0
Fiscal Year End Date
Pro Forma Statement of Cash Flows
Actual
FY2020
Actual
FY2021
Operating:
Net Income
+Depreciation & Amortization
+Increase in Deferred Taxes
+Increase in Other Liabilities
+Minority Interest in Earnings
+Preferred Dividends
=Funds From Operations
-Increase in Receivables
-Increase in Inventory
-Increase in Other Current Assets
+Increase in Accounts Payable
+Increase in Taxes Payable
+Increase in Other Curr. Liabilities
=Cash From Operations
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Investing:
-Capital Expenditures
-Increase in Investments
-Purchases of Intangibles
-Increase in Other Assets
=Cash From Investing
0
0
0
0
0
0
0
0
0
0
Financing:
+Increase in Debt
-Dividends Paid to Minority Interest
-Dividends Paid on Preferred
+Increase in Pref. Stock
-Dividends Paid on Common
+/-Net Issuance of Common Stock
+/-Clean Surplus Plug (Ignore)
=Cash From Financing
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Net Change in Cash
+ Beginning Cash Balance
= Ending Cash Balance
0
0
0
0
0
0
Net Income
– Increase in Common Equity
+/-Clean Surplus Plug (Ignore)
=Free Cash Flow to Common Equity
0
0
0
0
0
0
0
0
Computation based on SCF:
+Cash From Operations
-Increase in Cash
+Cash From Investing
+Increase in Debt
-Dividends Paid to Minority Interest
-Dividends Paid on Preferred
+Increase in Preferred Stock
+/-Clean Surplus Plug (Ignore)
=Free Cash Flow to Common Equity
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Financing Flows:
+Dividends Paid
-Net Issuance of Common Stock
= Free Cash Flow to Common Equity
0
0
0
0
0
0
Free Cash Flow to Common Equity
Free Cash Flow to all Investors
Net Operating Income
– Increase in Net Operating Assets
+/-Clean Surplus Plug (Ignore)
=Free Cash Flow to Investors
#DIV/0!
#DIV/0!
0
0
#DIV/0!
0
0
#DIV/0!
Computation based on SCF:
Cash From Operations
-Increase in Operating Cash
+Cash from Investing
+Interest Expense
-Tax Shield on Interest
+/-Clean Surplus Plug (Ignore)
=Free Cash Flow to Investors
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Financing Flows:
+Dividends on Common Stock
+Interest Expense
-Tax Shield on Interest
+Dividends on Preferred Stock
+Dividends Paid to Minority Interest
-Net Issuance of Common Stock
-Net Issuance of Debt
-Net Issuance of Preferred Stock
=Free Cash Flow to Investors
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Traditional Computation of FCF:
EBIT
-Taxes on EBIT
+Increase in Deferred Taxes
= NOPLAT
+Depreciation & Amortization
+Non-Operating Income (Loss)
+Other Income (Loss)
+Ext. Items & Disc. Ops.
=Gross Cash Flow
-Increase in Working Capital
-Capital Expenditures
-Increase in Investments
-Purchases of Intangibles
-Increase in Other Assets
+Increase in Other Liabilities
+/-Clean Surplus Plug (Ignore)
=Free Cash Flow to Investors
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
Analysis of Earnings Quality
(Red Shading = Quality Flag)
Current Op. Accruals/NOA
+ Non-Current Op. Accruals/NOA
= Operating Accruals/NOA
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Sales Growth
– NOA Turnover Growth
– Interaction
= Operating Accruals/NOA
Fiscal Year
0.000
#DIV/0!
#DIV/0!
#DIV/0!
2015
0.000
#DIV/0!
#DIV/0!
#DIV/0!
2016
Ratio Analysis
Company Name
Fiscal Year End Date
Instrunction
Fill the Yellow Marked Area
0
Actual
FY2019
Annual Growth Rates
Sales
Assets
Common Equity
Earnings
Free Cash Flow to Investors
Sustainable Growth Rate
Profitability
Return on Equity
N/A
Return on Equity (b4 non-recurring) N/A
Return on Net Operating Assets
N/A
Basic Dupont Model
Net Profit Margin
x Total Asset Turnover
x Total Leverage
= Return on Equity
N/A
N/A
N/A
Advanced Dupont Model
Net Operating Margin
x Net Operating Asset Turnover
= Return on Net Operating Assets
Net Borrowing Cost (NBC)
Spread (RNOA – NBC)
Financial Leverage (LEV)
ROE = RNOA + LEV*Spread
N/A
N/A
N/A
N/A
N/A
N/A
Margin Analysis
Gross Margin
EBITDA Margin
EBIT Margin
Net Operating Margin (b4 non-rec.)
Net Operating Margin
Turnover Analysis
Avge Days to Cash Usage
Avge Inventory Holding Period
Avge Days to Collect Receivables
Avge Days to Pay Payables
Net Operating Asset Turnover
Net Working Capital Turnover
PP&E Turnover
Analysis of Leverage
– Long-Term Capital Structure
Debt to Equity Ratio
FFO to Total Debt
CFO to Total Debt
Analysis of Leverage
– Short-Term Liquidity
Current Ratio
Quick Ratio
EBIT Interest Coverage
EBITDA Interest Coverage
N/A
N/A
N/A
N/A
N/A
N/A
N/A
Actual
FY2020
Actual
FY2021
Credit Analysis
Company Name
Fill the information for
the yellow area
0
Fiscal Year End Date
Actual
FY2019
Analysis of Leverage
– Long-Term Capital Structure
Debt to Equity Ratio
FFO to Total Debt
CFO to Total Debt
Analysis of Leverage
– Short-Term Liquidity
Current Ratio
Quick Ratio
EBIT Interest Coverage
EBITDA Interest Coverage
Actual
FY2020
Actual
FY2021