Since its founding in 1984 in Hillsboro, OR, Mama Mushroom has been a pioneer and dominant leader in Take ‘N’ Bake pizza, with a 25 year history built on quality, customer service and compelling value...

1 answer below »
financial modeling work , excel based


Since its founding in 1984 in Hillsboro, OR, Mama Mushroom has been a pioneer and dominant leader in Take ‘N’ Bake pizza, with a 25 year history built on quality, customer service and compelling value Part I Instructions: Operating Model An Operating Model gives you an idea of how the Company will perform in the future if it were to remain independent. You will first need to generate a set of projections for the standalone Company. Use the template in the “Worksheets” excel file on tab “Operating Model” and tab “Depreciation,” the financial statements from Exhibit 1 and the assumptions from Exhibit 2 when building your model. Assume the Company uses 100% of excess cash flow to pay down senior debt and interest expense on the senior debt (L + 5.5%) is equal to the interest rate times the average of the opening and closing debt balance. Assume the LIBOR rate does not change over the life of the loan. For the depreciation schedule on the “Depreciation” tab, make sure to link 2010 ending net PP&E to the “BS” tab and assume straight-line depreciation for all years when calculating the depreciation expense from CapEx. Total depreciation expense should include existing depreciation (this is already given to you in the model and is listed in the assumptions in Exhibit 2) and depreciation from projected CapEx. There are a few comments inserted in several cells which are marked by a red triangle in the upper right hand corner. These comments will help to further explain the assumptions so be sure to read each one by clicking on the cell. The projections calculated in your Operating Model will be used when building out the LBO. Leveraged Buyout Analysis A Leveraged Buyout Analysis looks at how much a financial sponsor (i.e. private equity firm) could pay for the Company using a mix of equity and debt. Please complete a valuation of the Company using the Leveraged Buyout methodology. You will need to solve for the range of TEV a financial buyer could pay for the Company. Use assumptions in Exhibit 2 and the LBO template in the “Worksheets” excel file on tab “LBO Model” when building your model. Assume a financial sponsor will target a 20.0% - 25.0% return and will use total leverage of 5.5x EBITDA. The first 3.5x of debt is assumed to be senior bank debt and cost L + 55o bps. The incremental debt up to 5.5x will carry a fixed rate of 12%. Refer to the “Cost of Debt” table for the pricing and read the comments in each cell to better understand the incremental cost of debt. Lenders will require at least 35% of the total purchase price in equity. You will want to link the Income Statement from the Operating Model to the LBO Model, but ONLY up to and including Depreciation (DO NOT link Amortization). You will also need to use Purchase Accounting to determine the Pro Forma 2010 column of the Balance Sheet. Assume Finite-Life Intangibles represent 20.0% of Excess Purchase Price and amortize over 15 years (assume amortization of Finite-Life Intangibles is tax deductible – this is a simplifying assumption to eliminate the need to incorporate deferred taxes in this model). Finally, use the tables shaded in grey to sensitize the impact of the leverage multiple and exit multiple on the maximum TEV. The public comparables table (Exhibit 3) will help determine an exit multiple. When sensitizing purchase multiple versus leverage, please make sure cell F11 is hard-coded. Also, when sensitizing purchase multiple versus the exit multiple, please make sure cell D134 is hard-coded. There are several cells with comments located throughout the LBO model tab which will help guide you through the assumptions and methodology of completing this worksheet. These cells are marked by a red triangle located in the upper right-hand corner of the cell. Be sure to read each one. This analysis will help you evaluate the effect debt has on valuation and its implications. WACC The Weighted Average Cost of Capital is the average cost of the different sources of capital (equity and debt) a Company uses to finance its operations. The Company’s WACC is the overall required return for the firm to satisfy all providers of capital and is the rate used to discount its future cash flows. You will need to determine the Weighted Average Cost of Capital using the template in the “Worksheets” excel file on tab “WACC.” In order to determine the appropriate Beta for Mama Mushroom, you will need to relever each company’s Beta using the median percent of Debt and each company’s Tax Rate. This will be used when calculating the Company’s Cost of Equity. Assume the Cost of Debt is 8.0%, which is provided in the template. a. Hint. You want to start with the operating model, for which you will need to fill out the depreciation tab to complete) and get it to balance before doing the WACC and LBO model tabs. 1
case-study-part-i-worksheets-2019-nydbcdol.xls case-study-intructions-2019-3gou2zie.doc
Answered Same DayApr 03, 2021

Answer To: Since its founding in 1984 in Hillsboro, OR, Mama Mushroom has been a pioneer and dominant leader in...

Lovenesh answered on Apr 03 2021
138 Votes
IS
        ($ in millions)
        Mama Mushroom Income Statement
        Fiscal Year End December 31                39814.0        40179.0
        Total Systemwide Sales                $1,169.8        $1,260.2
        Franchise Royalties                $55.6        $60.6
        Franchise Fees                5.8        4.8
        Company Store Sales                56.4        42.2
        Total Revenues                $117.8        $107.6
        Company Store Expenses                54.4        37.0
        Gross Profit                $63.4        $70.6
        % Margin
        Operating Expenses                33.2        35.8
        EBITDA                $30.2        $34.8
        % Margin                25.6%        32.3%
        Depreciation & Amortization                1.5        1.3
        Operating Income (EBIT)                $28.7        $33.5
        % Margin                24.4%        31.1%
        Interest Expense                2.0        3.8
        Income before Income Taxes                11.5        12.3
        Provision for Income Taxes                4.0        4.3
        Net Income                $7.5        $8.0
BS
        ($ in millions)
        Mama Mushroom Balance Sheet
        Fiscal Year End December 31                    40179.0
        Cash                    $0.9
        Accounts Receivable                    3.4
        Other current assets                    8.2
        Total Current Assets                    $12.5
        P&E, net                    $6.1
        Goodwill                    23.6
        Intangible Assets                    30.0
        Other Assets                    2.6
        Total Assets                    $74.8
        Accounts Payable                    $3.6
        Accrued Liabilities                    11.0
        Other Current Liabilities                    3.9
        Total Current Liabilities                    $18.5
        Long Term Debt                    $38.0
        Other Liabilities                    3.9
        Total Long Term Liabilities                    $41.9
        Total Equity                    $14.4
        Total Liabilities & Stockholder's Equity                    $74.8
        Check                    $0.0
Assumptions
    MAMA MUSHROOMMODEL ASSUMPTIONS
        Operating Model
            Amortization for all years                                0.0
            Amortization of Financing Fees for all years                                0.0
            LIBOR                                0.75%
            Spread                                5.5%
            Tax Rate                                35.0%
            Other current assets for all years                                0.0
            Assumptions
                
a650389: Please note that some assumptions will differ from their historical calculations.        40179.0    40545.0    40911.0    41277.0    41643.0    42009.0
            Franchise Royalties Growth            9.0%    10.0%    12.5%    15.0%    17.5%    17.5%
            Franchise Fees Sales Growth            (17.2%)    4.0%    5.0%    6.0%    6.0%    6.0%
            Company Store Sales Growth            (25.2%)    3.0%    3.0%    3.0%    3.0%    3.0%
            COGS (% Sales)            34.4%    34.4%    34.4%    34.4%    34.4%    34.4%
            SG&A (% Sales)            33.3%    34.3%    34.3%    34.3%    34.3%    34.3%
            Accounts Receivable Days (Sales)            11.4    11.5    11.5    11.5    11.5    11.5
            Other Current Assets (% Sales)            7.6%    7.6%    7.6%    7.6%    7.6%    7.6%
            Accounts Payable Days (COGS)            35.0    35.0    35.0    35.0    35.0    35.0
            Accrued Liabilities (% Sales)            10.2%    10.2%    10.2%    10.2%    10.2%    10.2%
            Other Current Liabilities, Other Liabilities and Other Assets equal to number as the year before
            Assume minimum operating cash balance equal to 2010 cash balance
            Days        360
            Capital Expenditures                40545.0    40911.0    41277.0    41643.0    42009.0
            Growth (Company stores)                $0.0    $0.0    $0.5    $0.8    $0.9
            Maintenance                1.5    1.6    1.5    1.3    1.3
            Point of Sale Systems                2.2    1.2    0.0    0.0    0.0
            Total                $3.7    $2.8    $2.0    $2.1    $2.2
        Depreciation Schedule
            Asset Sales or Write-Offs                                0.0
            Useful Life                                12.0
                            40545.0    40911.0    41277.0    41643.0    42009.0
            Existing Depreciation                $1.3    $1.4    $1.6    $1.8    $2.1
        WACC
            Risk Free Rate                                 3.84%
            Market Risk Premium                                6.47%
            Small Size Company Premium                                5.81%
            Current Company Specific Cost of Debt                                8.00%
        LBO Model
            Total Debt Financing EBITDA Multiple                                5.5x
            Maximum Senior Leverage                                3.5x
            % Excess PP Finite-Life Intangibles                                20.0%
            Finite-Life Intangibles Amortization Period (years)                                15.0
            Advisory Fees (as a % of EV)                                1.0%
            Other (Fees in $ millions)                                1.5
            Financing Fees (as a % of Long Term Debt)                                3.0%
            Amortization Period for Financing Fees (years)                                5.0
            Assume Tax Rate, LIBOR, Spread are same as above
            Assume the same metrics for the balance sheet and income statement as in operating model
            Assume Cash on Balance Sheet is used for LBO
Operating Model
    MAMA MUSHROOM OPERATING MODEL                                        ERROR:#REF!        ERROR:#REF!
    INCOME STATEMENT
                            39814.0    40180.0    40546.0    40912.0    41278.0    41644.0    42010.0
    Franchise Royalties                        $55.6    $60.6    $66.7    $75.0    $86.2    $101.3    $119.1
    Franchise Fees                        5.8    4.8    $5.0    $5.2    $5.6    $5.9    $6.2
    Company Store                        56.4    42.2    $43.5    $44.8    $46.1    $47.5    $48.9
    Revenue                        $117.8    $107.6    $115.1    $125.0    $137.9    $154.7    $174.2
    % Growth                        NA    (8.7%)    7.0%    8.6%    10.3%    12.2%    12.6%
    COGS                        54.4    37.0    39.6    43.0    47.4    53.2    59.9
    % Sales                        46.2%    34.4%    34.4%    34.4%    34.4%    34.4%    34.4%
    Gross Profit                        63.4    70.6    75.5    82.0    90.5    101.5    114.3
    % Margin                        53.8%    65.6%    65.6%    65.6%    65.6%    65.6%    65.6%
    SG&A                        33.2    35.8    39.5    42.8    47.3    53.0    59.7
    % Margin                        28.2%    33.3%    34.3%    34.3%    34.3%    34.3%    34.3%
    EBITDA                        30.2    34.8    36.1    39.2    43.2    48.5    54.6
    % Margin                        25.6%    32.3%    31.3%    31.3%    31.3%    31.3%    31.3%
    Depreciation                        1.5
a650389: Please assume that D&A on the income statement is just depreciation in 2007 and 2008.    1.3    2.4    2.2    2.1    2.2    2.3
    Amortization                        0.0    0.0    0.0    0.0    0.0    0.0    0.0
    EBIT                        28.7    33.5    33.7    37.0    41.1    46.3    52.3
                LIBOR    Spread    Rate                                    REF# Buster
    Interest Expense            0.75%    5.50%    6.3%    2.0    3.8    2.4    1.1    0.0    0.0    0.0        1
    Amortization of Debt Financing Fees                    2.10    0.0    0.0    0.0    0.0    0.0    0.0    0.0
    Pre-Tax Income                        26.7    29.7    31.3    35.9    41.1    46.3    52.3
    Cash Taxes                        4.0    4.3    11.0    12.6    14.4    16.2    18.3
    Tax Rate                        15.1%
a650389: Please note that some assumptions will differ from their historical calculations.    14.5%
a650389: Please note that some assumptions will differ from their historical calculations.    35.0%    35.0%    35.0%    35.0%    35.0%
    Net Income                        $22.7    $25.4    $20.4    $23.3    $26.7    $30.1    $34.0
                                11.8%    (19.7%)    14.5%    14.5%    12.7%    12.9%
    STATEMENT OF CASH FLOWS
                                    40546.0    40912.0    41278.0    41644.0    42010.0
    Net Income                                $20.4    $23.3    $26.7    $30.1    $34.0
    Amortization                                0.0    0.0    0.0    0.0    0.0
    Depreciation                                2.4    2.2    2.1    2.2    2.3
    Operating Activities
    Accounts Receivable            -    CA                0.3    0.3    0.4    0.5    0.6
    Inventory            -    CA                - 0    - 0    - 0    - 0    - 0
    Other Current Assets            -    CA                0.5    0.8    1.0    1.3    1.5
    Accounts Payable            +    CA                0.2    0.3    0.4    0.6    0.7
    Accrued Liabilities            +    CA                0.7    1.0    1.3    1.7    2.0
    Other Current Liabilities            +    CA                - 0    - 0    - 0    - 0    - 0
    Other Liabilities                                - 0    - 0    - 0    - 0    - 0
    Cash from operating activity                                $22.9    $25.8    $29.2    $32.7    $36.8
    Investing Activities
    Capital Expenditures                                ($3.7)    ($2.8)    ($2.0)    ($2.1)    ($2.2)
    Cash used in Investing Activities                                ($3.7)    ($2.8)    ($2.0)    ($2.1)    ($2.2)
    Cash Available for Debt Repayment                                $19.2    $23.0    $27.2    $30.6    $34.6
    Financing Activities
    Payments on Long-Term Debt                                (20.1)    (17.9)    - 0    - 0    - 0
    Cash used in Financing Activities                                $20.1    $17.9    $0.0    $0.0    $0.0
    Increase(Decrease) in Cash                                ($0.9)    $5.1    $27.2    $30.6    $34.6
    Beginning Cash Balance                                0.9    0.0    5.1    32.2    62.9
    Ending Cash Balance                                $0.0    $5.1    $32.2    $62.9    $97.5
    DEBT BALANCE AND INTEREST CALCULATIONS
                                40180.0    40546.0    40912.0    41278.0    41644.0    42010.0
    Opening Debt Balance                                $38.0    $17.9    $0.0    $0.0    $0.0
    Less: Excess Cash Flow                                20.1
Ernest: Ernest:
less?    
a650389: Please note that some assumptions will differ from their historical calculations.    
a650389: Please note that some assumptions will differ from their historical calculations.    
a650389: Please assume that D&A on the income statement is just depreciation in 2007 and 2008.            17.9    0.0    0.0    0.0
    Closing Balance                            $38.0    $17.9    $0.0    $0.0    $0.0    $0.0
    Total Debt                                $17.9    $0.0    $0.0    $0.0    $0.0
    Total Debt / EBITDA                                0.5x     0.00x     0.0x     0.0x     0.0x
    EBITDA / Interest                                15.2x     35.0x
    (EBITDA - Capex) / Interest                                16.7x     37.5x
    BALANCE SHEET
                                40180.0    40546.0    40912.0    41278.0    41644.0    42010.0
    ASSETS
    Cash                            $0.9    $0.0    $5.1    $32.2    $62.9    $97.5
    Accounts Receivable                            3.4    3.7    4.0    4.4    4.9    5.6
    Inventory                            0.0    0.0    0.0    0.0    0.0    0.0
    Other Current Assets                            8.2    8.7    9.5    10.5    11.8    13.2
    Total Current Assets                            $12.5    $12.4    $18.6    $47.1    $79.6    $116.3
    Property, Plant and Equipment, Net                            $6.1    $7.4    $8.1    $8.0    $7.9    $7.8
    Goodwill                            23.6    23.6    23.6    23.6    23.6    23.6
    Intangible...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?