Fix "broken" Formulas in Financial statements
Not what you're looking for?
See the attachments.
Some formulas and links are "broken" in the Excel sheet.
Broken links are marked with a red font. In particular, my interest calculation and tax schedules look a bit messed up.
Tasks are:
1. To restore all broken links and to fix the balance sheet. (One hint - cash calculation is circular).
2. To reproduce the (new, separate) Cash Flow statement starting "from Net Income" (as opposed to "from EBITDA", as it currently is).
3. To replace the "summary" deferred tax item by two separate/dedicated deferred tax items: one on the asset side and the other on the liability side of the B/S.
NB:
Basically there should be a link between the income statement, cash flow statement and balance sheet on the "Financials - ABC Industries" sheet.
And this should also link to the tax and interest schedules on the "Supp Schedule - ABC Industries"
By using a correct formula for the "cells" in red, it should be able to balance the sheet.
So for instance if you look at the balance sheet, you can see that its not balanced.
Purchase this Solution
Solution Summary
This solution helps in creating financial statements. The expert restores all broken links and to fix the balance sheet.
Solution Preview
See the attached files.
Income Statement Summary
Projected Fiscal Year Ending December 31,
(USD in millions, except otherwise stated) 2014 2015 2016 2017 2018 2019
Revenues 150.0 162.0 173.3 183.7 192.9 200.6
-- growth rate, % --- 8.0% 7.0% 6.0% 5.0% 4.0%
COGS 90.0 94.0 97.1 101.1 102.3 104.3
-- gross margin, % 40.0% 42.0% 44.0% 45.0% 47.0% 48.0%
Salaries 17.0 19.4 20.8 20.2 19.3 18.1
Marketing 4.0 7.3 7.4 7.3 6.2 6.0
Other Operating Costs 2.0 4.9 3.5 2.8 2.9 2.8
EBITDA 37.0 36.5 44.6 52.4 62.3 69.4
-- EBITDA margin, % 24.7% 22.5% 25.8% 28.5% 32.3% 34.6%
Depreciation & Amortization 25.0 35.0 33.8 32.8 32.3 34.1
EBIT 12.0 1.5 10.8 19.5 30.0 35.3
-- EBIT margin, % 8.0% 0.9% 6.3% 10.6% 15.6% 17.6%
Interest Income 0.5 (0.1) ...
Education
- MBA, Indian Institute of Finance
- Bsc, Madras University
Recent Feedback
- "I've posted a similar question for another course. It's post 657940, and it's a practice problem that I'd like to use for the final exam. Your help will be greatly appreciated. "
- "thank you!"
- "Thank you again Jayant. You are super fast. "
- "Thank you Jayant. You are appreciated. "
- "Again, thank you Jayant. You are wonderful. "
Purchase this Solution
Free BrainMass Quizzes
Social Media: Pinterest
This quiz introduces basic concepts of Pinterest social media
Balance Sheet
The Fundamental Classified Balance Sheet. What to know to make it easy.
Writing Business Plans
This quiz will test your understanding of how to write good business plans, the usual components of a good plan, purposes, terms, and writing style tips.
SWOT
This quiz will test your understanding of the SWOT analysis, including terms, concepts, uses, advantages, and process.
Lean your Process
This quiz will help you understand the basic concepts of Lean.