financial modeling work , excel basedSince its founding in 1984 in Hillsboro, OR, Mama Mushroom has...

Question

financial modeling work , excel basedSince 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

Lovenesh · Accepted Answer

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 Assets							30.0	30.0	30.0	30.0	30.

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

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

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment

Sun	Mon	Tue	Wed	Thu	Fri	Sat
30	31	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	1	2	3