80.2K

Verified Solution

Link Copied!

need help filling up the excel sheet

Notes: 1. Use the Excel spreadsheet provided for calculations. The spreadsheet also has some explanations about the assumptions. First seven sheets are the Exhibits of the case. You can use the sheet titled "Projections" to calculate project's incremental after-tax cash flows. You can use the sheet titled "Net Working Capital to calculate net working capital (which in turn will be used in incremental after-tax cash flow calculations). You may add more sheets to the excel file as necessary Exhibit 1: HPL's Historical Financial Statements 2003 2006 2007 161 $503.4 4052 5680.7 5582 513.4 43223 111.4 46 378 Operating Results Revenue Less: Cost of Goods Sold Gross Profit Less: Selling, General & Administrative EBITDA Less: Depreciation EBIT Less: Interest Expense EBT Less: Taxes Net Income 53.6 55 48.1 19.2 $28.9 22.0 $32.8 60.0 24.0 $36.0 $38.5 Margins Revenue Growth NA Gross Margin 19.5% Selling, General & Administrative/Revenue 7.5% EBITDA Margin 12.0% EBIT Margin 10.6% Net Income Margin 5.7% Effective Tax Rate 39.9% 8.0% 20.5% 8.29 12.3% 11.1% 6,0% 40.1% 8.0% 15.5% 7.8% 77% 8.3% 19,3% 8.1% 11.2% 10.3% 5.7% 40.0% 7.0% 18.0% 7.2% 10.8% 67 3.4% 39.9% 39.9% 2004 2007 $5.1 Assets Cash & Cash Equivalents Accounts Receivable Inventory Total Current Assets 2003 $43 62.1 57.7 124.1 78.8 87.1 61.9 61.2 144.8 133.2 156.8 203.1 202.3 Property, Plant & Equipment Other Non-Current Assets Total Assets 2014 123 $337-8 $359.7 $371.6 S8805 Libilities & Ouers Equity Accounts Payable & Accrued Liabilities $422 $45.0 $51.6 $53.4 $58.1 Long-Term Debt 916 Owners' Equity Total Liabilities & Owners' Equity 204.0 $337.8 Net Working Capital Accounts Receivable Plus Inventory Le Accounts Payable & Accrued Expenses Net Working Capital (Exhibit 1 cont'd next page) Exhibit 1. cont'd 2004 2005 2006 2003 $28.9 2007 $32 Cash From Operations: Net Income Plus: Depreciation Less: Increase in Accounts Receivable Less: Increase in Inventory Plus: Increase in Accounts Payable Total Cash From Operations $33.5 537 Cash From Ingesting: Capital Expenditures Plus: Increases in Other Non-Current Assets Total Cash Used in Investing $7.3 0.5 $7.8 $7.7 (0.2 $7.5 $8.2 1.7 $6.5 Cash From Financing Repayment of Debt Plus Dividend Payments Cash Used in Financing $8.0 14.4 $22.4 $8.8 16.4 $25.2 $11.0 23.0 5440 Total Cash Generated $2.2 $0.8 ($0.3) $3.0 ($2.8) Exhibit 2: Private Label Share of U.S. Consumer Packaged-Goods Spending Unit share Dollar share 25% 21.0% 21.2% 21.3% 20% 15.7% 15.8% 16.1% 15% 10% 0% 2005 2006 2007 Source: IRI Exhibit 3: U.S. Sales in HPL's Target Markets (in US$ millions) Skin care Oral hygiene Personal hygiene Hand and body care $24,000 $21,000 $2,229 $2,071 51.909 $1.990 $18,000 54,328 $15.000 $12,000 $9,000 $6,000 58 100 53,000 2003 2005 2007 Source: Datamonitor Note: These figures include sales of both branded and private label products. BIBLE CASESILHARVARD BUSINESS SCHOC Hansson Private Label, Ine. Evaluating an Investment in Expansion 21 Exhibit 4: HPL's Sales into its Retail Channels Figures represent wholesale prices. Sales (US$ millions) for Year Ending December 31 Channel 2003 2004 2005 2006 2007 Mass merchants 220 Grocery Club 149 159 167 Drug Dollar All others Total 505 543 587 637 681 268 $$ 24 26 *The category All others comprises convenience store sales and sales to miscellaneous distributors. Exhibit 5: Excerpt of Financial Assumptions in Capital Request Form) 80.000 50% Romur Projection Total Capacity (2007) Capacity Utilization Unit Volume Selling PricePerUnit - Growing at Revenue 2009 +0000 60.0 48,000 17 M G 2010 0.000 65.0% 2,000 18 9,851 2011 0.000 0.0% 56,000 19 103,124 2012 BODO 75. 0 60.000 19 112,700 2013 ODO 0 0 64,000 192 122,618 2014 2015 N CIONOLOCO .0% 50% 68,000 M4,000 193 199 132,65 135,545 2016 80.000 50% 8,000 200 138.256 2017 80.00 50% 68,000 200 141021 2.0% 1611 Raw Materials Per Leit Growing at 19. 10% Manufacturing Overhead Growing at 3.0% Maintenance Expert Growing at 30% 3,708 319 3934 2.459 Avenge Away Loaded Comt Total Solaried Labor Cost 3.% 1714 1774 35% Hewler Co Average Rally Loaded Hourly Cost Hours Per Year Cost Per Hourly Eployee Number of ourly Workers Total Hourly Labor Costco . 2142 2.000 22.17 2.000 4,349 40,000 18.000 19,570.9 21,8140 24,190.2 Total Labor Coat 202333 23,414 2256 Selling, General Administrative/Revenue 7 Days Sales Outanding Day Sale inventory Days PayableOutanding (1) Note that accounts receivable less accounts payable will be retumed at the end of the project. Also note that all working capital ratios are based on a 360 day year. Based on historical average 21 Hansson Private Label, Ines Evaluating an Investment in Expansion Exhibit 6: Comparable Company Analysis EBITDA 255.3 Market Company Value Cathleen Sinclair 2989 General Health & Beauty 3193 Women's Care Company 498.6 Skin Care Enterprises 1,204. 2 Net Enterprise Debt Value 1,329.6 1,628.5 63.1 3824 55.3 553.9 371. 9 ,576. 1 Equity Beta Revenue 222 13468 1.95 4461 3973 1 .35 1.2476 EBIT 1634 51.9 493 125.4 Net Income 21.5 23.8 770 653 Book Value (1.1128) 204 2 437 3162 52.7 1958 EBIT Earnings Revenue EBITDA PE P/B 1215 EBITDA 190 13.9% 13.3% 12.5% 0.9% Cathleen Sinclair General Health & Beauty Women's Care Company Skin Care Enterprises Average 13.4x 53% 19.4% 52% 6.2x 10.5 EBIT 10.Ch 7.4% 11.2x 12. 11.6% 12.4% 10.1% 11.5% 1.4 18. 11. I 1.2x 10.3 rivate Label Ini Ivalon AVA Exhibit 7: Cost of Capital Analysis Asset Debt Value 81.6% 16.5% 10.0% 23.6% 32.9% Company Cathleen Sinclair General Health & Beauty Women's Care Company Skin Care Enterprises Average Debt Equity 444.9% 19.8% 11.1% 30.9% 49.1% Equity Beta 2.22 1.95 1.14 1.35 1.67 Debt Beta 0.25 0.00 0.00 0.00 0.06 1 Debu Debt Value Equity 0.010 . 0 5.0% 5.3% 10.0 11.19 15.0% 17.6% 20.0% 25,0% 25.0% 33.3% Asset Beta .18 1.18 1.18 1.18 1.18 1.18 Equity Beta 1.18 122 1.26 1.31 1.36 142 Cost of Equity 9.67% 9.86% 10.07% 10.30% 10.56% 10.86% Cost of Debt WACC 7.75% 9.67% 7.75% 9.60% 7.75% 9.53% 775% 9,45% 7.75% 938% 7.75% 9.31% Assumptions: 10-Year Treasury Market Risk Premium 3.75% 5.00% Tax Rate 40.0% Est. Hansson EBITDA Multiple Est. Hansson Enterprise Value 7.0x 514.5 49.8 Existing Net Debt Plus New Expansion Debt Total Estimated Debt 578 107.6 Existing D/V Estimated New D/V 9.7% 20.9% Assumed Debt Beta Estimated Cost of Debt 0.00 7.75% Notes: 1. Use the Excel spreadsheet provided for calculations. The spreadsheet also has some explanations about the assumptions. First seven sheets are the Exhibits of the case. You can use the sheet titled "Projections" to calculate project's incremental after-tax cash flows. You can use the sheet titled "Net Working Capital to calculate net working capital (which in turn will be used in incremental after-tax cash flow calculations). You may add more sheets to the excel file as necessary Exhibit 1: HPL's Historical Financial Statements 2003 2006 2007 161 $503.4 4052 5680.7 5582 513.4 43223 111.4 46 378 Operating Results Revenue Less: Cost of Goods Sold Gross Profit Less: Selling, General & Administrative EBITDA Less: Depreciation EBIT Less: Interest Expense EBT Less: Taxes Net Income 53.6 55 48.1 19.2 $28.9 22.0 $32.8 60.0 24.0 $36.0 $38.5 Margins Revenue Growth NA Gross Margin 19.5% Selling, General & Administrative/Revenue 7.5% EBITDA Margin 12.0% EBIT Margin 10.6% Net Income Margin 5.7% Effective Tax Rate 39.9% 8.0% 20.5% 8.29 12.3% 11.1% 6,0% 40.1% 8.0% 15.5% 7.8% 77% 8.3% 19,3% 8.1% 11.2% 10.3% 5.7% 40.0% 7.0% 18.0% 7.2% 10.8% 67 3.4% 39.9% 39.9% 2004 2007 $5.1 Assets Cash & Cash Equivalents Accounts Receivable Inventory Total Current Assets 2003 $43 62.1 57.7 124.1 78.8 87.1 61.9 61.2 144.8 133.2 156.8 203.1 202.3 Property, Plant & Equipment Other Non-Current Assets Total Assets 2014 123 $337-8 $359.7 $371.6 S8805 Libilities & Ouers Equity Accounts Payable & Accrued Liabilities $422 $45.0 $51.6 $53.4 $58.1 Long-Term Debt 916 Owners' Equity Total Liabilities & Owners' Equity 204.0 $337.8 Net Working Capital Accounts Receivable Plus Inventory Le Accounts Payable & Accrued Expenses Net Working Capital (Exhibit 1 cont'd next page) Exhibit 1. cont'd 2004 2005 2006 2003 $28.9 2007 $32 Cash From Operations: Net Income Plus: Depreciation Less: Increase in Accounts Receivable Less: Increase in Inventory Plus: Increase in Accounts Payable Total Cash From Operations $33.5 537 Cash From Ingesting: Capital Expenditures Plus: Increases in Other Non-Current Assets Total Cash Used in Investing $7.3 0.5 $7.8 $7.7 (0.2 $7.5 $8.2 1.7 $6.5 Cash From Financing Repayment of Debt Plus Dividend Payments Cash Used in Financing $8.0 14.4 $22.4 $8.8 16.4 $25.2 $11.0 23.0 5440 Total Cash Generated $2.2 $0.8 ($0.3) $3.0 ($2.8) Exhibit 2: Private Label Share of U.S. Consumer Packaged-Goods Spending Unit share Dollar share 25% 21.0% 21.2% 21.3% 20% 15.7% 15.8% 16.1% 15% 10% 0% 2005 2006 2007 Source: IRI Exhibit 3: U.S. Sales in HPL's Target Markets (in US$ millions) Skin care Oral hygiene Personal hygiene Hand and body care $24,000 $21,000 $2,229 $2,071 51.909 $1.990 $18,000 54,328 $15.000 $12,000 $9,000 $6,000 58 100 53,000 2003 2005 2007 Source: Datamonitor Note: These figures include sales of both branded and private label products. BIBLE CASESILHARVARD BUSINESS SCHOC Hansson Private Label, Ine. Evaluating an Investment in Expansion 21 Exhibit 4: HPL's Sales into its Retail Channels Figures represent wholesale prices. Sales (US$ millions) for Year Ending December 31 Channel 2003 2004 2005 2006 2007 Mass merchants 220 Grocery Club 149 159 167 Drug Dollar All others Total 505 543 587 637 681 268 $$ 24 26 *The category All others comprises convenience store sales and sales to miscellaneous distributors. Exhibit 5: Excerpt of Financial Assumptions in Capital Request Form) 80.000 50% Romur Projection Total Capacity (2007) Capacity Utilization Unit Volume Selling PricePerUnit - Growing at Revenue 2009 +0000 60.0 48,000 17 M G 2010 0.000 65.0% 2,000 18 9,851 2011 0.000 0.0% 56,000 19 103,124 2012 BODO 75. 0 60.000 19 112,700 2013 ODO 0 0 64,000 192 122,618 2014 2015 N CIONOLOCO .0% 50% 68,000 M4,000 193 199 132,65 135,545 2016 80.000 50% 8,000 200 138.256 2017 80.00 50% 68,000 200 141021 2.0% 1611 Raw Materials Per Leit Growing at 19. 10% Manufacturing Overhead Growing at 3.0% Maintenance Expert Growing at 30% 3,708 319 3934 2.459 Avenge Away Loaded Comt Total Solaried Labor Cost 3.% 1714 1774 35% Hewler Co Average Rally Loaded Hourly Cost Hours Per Year Cost Per Hourly Eployee Number of ourly Workers Total Hourly Labor Costco . 2142 2.000 22.17 2.000 4,349 40,000 18.000 19,570.9 21,8140 24,190.2 Total Labor Coat 202333 23,414 2256 Selling, General Administrative/Revenue 7 Days Sales Outanding Day Sale inventory Days PayableOutanding (1) Note that accounts receivable less accounts payable will be retumed at the end of the project. Also note that all working capital ratios are based on a 360 day year. Based on historical average 21 Hansson Private Label, Ines Evaluating an Investment in Expansion Exhibit 6: Comparable Company Analysis EBITDA 255.3 Market Company Value Cathleen Sinclair 2989 General Health & Beauty 3193 Women's Care Company 498.6 Skin Care Enterprises 1,204. 2 Net Enterprise Debt Value 1,329.6 1,628.5 63.1 3824 55.3 553.9 371. 9 ,576. 1 Equity Beta Revenue 222 13468 1.95 4461 3973 1 .35 1.2476 EBIT 1634 51.9 493 125.4 Net Income 21.5 23.8 770 653 Book Value (1.1128) 204 2 437 3162 52.7 1958 EBIT Earnings Revenue EBITDA PE P/B 1215 EBITDA 190 13.9% 13.3% 12.5% 0.9% Cathleen Sinclair General Health & Beauty Women's Care Company Skin Care Enterprises Average 13.4x 53% 19.4% 52% 6.2x 10.5 EBIT 10.Ch 7.4% 11.2x 12. 11.6% 12.4% 10.1% 11.5% 1.4 18. 11. I 1.2x 10.3 rivate Label Ini Ivalon AVA Exhibit 7: Cost of Capital Analysis Asset Debt Value 81.6% 16.5% 10.0% 23.6% 32.9% Company Cathleen Sinclair General Health & Beauty Women's Care Company Skin Care Enterprises Average Debt Equity 444.9% 19.8% 11.1% 30.9% 49.1% Equity Beta 2.22 1.95 1.14 1.35 1.67 Debt Beta 0.25 0.00 0.00 0.00 0.06 1 Debu Debt Value Equity 0.010 . 0 5.0% 5.3% 10.0 11.19 15.0% 17.6% 20.0% 25,0% 25.0% 33.3% Asset Beta .18 1.18 1.18 1.18 1.18 1.18 Equity Beta 1.18 122 1.26 1.31 1.36 142 Cost of Equity 9.67% 9.86% 10.07% 10.30% 10.56% 10.86% Cost of Debt WACC 7.75% 9.67% 7.75% 9.60% 7.75% 9.53% 775% 9,45% 7.75% 938% 7.75% 9.31% Assumptions: 10-Year Treasury Market Risk Premium 3.75% 5.00% Tax Rate 40.0% Est. Hansson EBITDA Multiple Est. Hansson Enterprise Value 7.0x 514.5 49.8 Existing Net Debt Plus New Expansion Debt Total Estimated Debt 578 107.6 Existing D/V Estimated New D/V 9.7% 20.9% Assumed Debt Beta Estimated Cost of Debt 0.00 7.75%

Answer & Explanation Solved by verified expert

See Answer