Explore BrainMass
Share

Explore BrainMass

    Fix "broken" Formulas in Financial statements

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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.

    © BrainMass Inc. brainmass.com October 10, 2019, 8:02 am ad1c9bdddf
    https://brainmass.com/business/financial-statements/fix-broken-formulas-financial-statements-605933

    Attachments

    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) ...

    Solution Summary

    This solution helps in creating financial statements. The expert restores all broken links and to fix the balance sheet.

    $2.19