Problem Set #1 FI 311 Excel Problem Company ZYX has asked you to help complete s
ID: 2422261 • Letter: P
Question
Problem Set #1
FI 311 Excel Problem
Company ZYX has asked you to help complete some of their finance work for 2015. They tell you the interest for the year was $50,000, and the corporate tax rate was 38%.
Company ZYX's 2015 Income Statement:
2015
2014
Sales
$ 3,450,000
$3,000,000
COGS
986,000
658,934
Gross Profit
2,464,000
2,341,066
SG&A Expenses
239,000
191,200
Depreciation
135,000
108,000
EBIT
2,090,000
2,041,866
Interest Expense
50,000
25,833
EBT
2,040,000
2,016,033
Taxes
775200
766,092
Net Income
1,264,800
1,249,940
Using excel, please fill in the missing parts of the income statement.
Calculate the operating cash flow amount for 2015
What is the cash coverage for 2015?
If you were told that the amount of inventory on the 2015 balance sheet was $275,000, what is the inventory turnover?
Given your findings in part A, what is the days in inventory? (Round to nearest whole number)
2015
2014
Sales
$ 3,450,000
$3,000,000
COGS
986,000
658,934
Gross Profit
2,464,000
2,341,066
SG&A Expenses
239,000
191,200
Depreciation
135,000
108,000
EBIT
2,090,000
2,041,866
Interest Expense
50,000
25,833
EBT
2,040,000
2,016,033
Taxes
775200
766,092
Net Income
1,264,800
1,249,940
Explanation / Answer
1) We observed that EBITDA is missing in the Income statement
EBITDA = EBIT+Depreciation+amortisation
Using excel, we find that
For 2015, EBITDA = 20,90,000+135,000=2,225,000
For 2014, EBITDA=2149866
2) Operating cash flow for 2015:-
Net Income : 1264,800
Add:Interest : 50,000
Add: Depreciation : 135,000
Cash flow from operation : 1,449,800
3) Cash Coverage ratio = (EBIT + Non cash expense e,g Depreciation) / Interest
=(2090,000+135000)/135000 = 16.48 times
4) Closing Inventory of 2015 (given) = 275,000
Inventory Turnover = Cost of goods sold / Inventory at hand = 986,000/275,000=3.58 times
5) Average days in Inventory = 365/Inventory turnover = 365/3.58=101.9 or 102 days
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.