Question: Run scenario analyses on key variables using information from the case. AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell
Run scenario analyses on key variables using information from the case.



AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) NIT v 11 In AA = = V ab Wrap Text General LIX X [6 * WE 28. O. F IN Analyze Data Paste BIU a Av A Merge & Center & $ % ) Insert Inl IM .00 000 Delete Format v Conditional Format Cell Formatting as Table Styles V Sort & Filter Find & Select 032 4x fx A B D E F G H 1 L M N O P Q R s T U V w 1 Chang Dental Clinic 2 3 4 Exhibit 2 BALANCE SHEET As of December 31 5 2005 2004 2003 6 7 8 9 ASSETS 10 Current assets $4,067 46,942 6,366 1,646 59,021 $ - $ 41,248 5,296 29,528 2,677 0.058 0.008 0.069 0.009 0.103 0.009 46,544 32,205 9209 24217 $69,834 $60,625 $36,408 (30,595) (19,171) (8,687) 39,239 41,454 27,721 0.022 c-factor 1 1 1 39,240 41,455 27,722 $98,261 $87,999 $59,927 11 Cash 12 Accounts receivable 13 Inventory 14 Prepaid expenses 15 Total Current Assets 16 17 Long-term Assets 18 Fixed assets 19 Less: Accumulated amortization - fixed assets 20 Net fixed assets 21 22 Goodwill 23 Total Long-term Assets 24 25 TOTAL ASSETS 26 27 LIABILITIES AND OWNER'S EQUITY 28 Liabilities 29 Bank indebtedness 30 Accounts payable 31 Total Liabilities 32 33 OWNER'S EQUITY 34 S. Chang, Capital 35 36 TOTAL LIABILITIES AND OWNER'S EQUITY 30 37 38 39 40 41 42 $ $39 10,724 10,763 $8,201 8,173 16,374 0.013 10,929 10,929 0.018 0.028 0.052 0.060 0.084 w-factor 87,332 77,236 43,553 $98,261 $87,999 $59,927 Exh1 Exh2 Base Case + Ready O El + 100% AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X NIT Calibri (Body) v 11 In AA = = V ab Wrap Text LUL General LIX WE 28. O. F IN Analyze Data Paste B I U a Av A 3 Merge & Center $ % ) Delete Insert .00 000 Format v Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select 032 4 x fx A B c D E F G G H 1 K L M N N o P o R s T U v W 1 Chang Dental Clinic 2 3 4 Exhibit 1 INCOME STATEMENTS For the year ended December 31 5 6 2005 2004 2003 2005 2004 2003 $812,987 $600,308 $287,281 8 35.4% 109.0% 0.205 0.177 0.140 Dental supplies 0.077 0.067 0.092 $387,008 10-year capitalization of leases 0.027 0.018 0.001 7 8 9 9 FEE REVENUE 10 11 OPERATING EXPENSES: 12 Associate fees 13 Salaries and benefits 14 15 Equipment leasing 16 Rent and utilities 17 Laboratory fees 18 General and office 19 Promotion and entertainment 20 Automobile expenses 21 Telephone 22 Professional focs 23 Interest and bank charges 24 Professional development 25 Insurance 26 Licenses, focs and dues 27 Bed debts (recovered) 28 29 30 TOTAL EXPENSES 31 32 NET INCOME 33 34 Cash operating costs 35 36 37 38 166,727 127,703 62,715 52,582 42,441 21,626 17,420 10,775 5,104 11.187 5,910 5,630 2,913 2,778 1,843 (3,647) 14,872 106,486 93,868 40,508 52,582 38,500 10,636 14,193 8,172 3,677 1,251 3.294 7,015 406 1,612 2,685 17,939 10,484 40,098 39,008 26,291 19,456 29,428 186 9,051 794 1,337 4,697 5,755 13.579 2,021 849 1,282 339 8.687 Amortization 0.309 0.263 548,579 413,308 202,858 0.232 v 368,074 F includes draw $264,408 $187,000 $84,423 $191,324 $116,919 $42,701 39 40 41 42 43 44 Exh1 Exh2 Base Case + Ready O El + 100% AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X Calibri (Body) v 11 = = AA LUU In ab Wrap Text V General NT LIX WE 28- 0 F IN Analyze Data Paste BIU a Av A Merge & Center & $ % ) Insert .00 000 v Delete & Format Conditional Format Cell Formatting as Table Styles V Sort & Filter Find & Select 032 4x fx A B D E F F G H 1 K L . M N 0 0 P o R S S T T U V w X Y Z AA AB AC Dollar amounts in $1,000 1 Chang Dental Clinic 2 Valuation 3 4 2013 2005 0 2006 1 1 2007 2 2008 3 2009 4 2010 5 2011 6 2012 7 2014 9 2015 10 5 8 6 7 S(1-1) 669.9 729.2 761.5 784.4 807.9 832.1 857.1 882.8 909.3 936.6 8 VS(1-4) -206.9 -225.2 -235.2 -242.2 -249.5 -257.0 -264.7 -272.6 -280.8 -289.2 9 F(1-1) -265.4 -273.3 -281.5 -290.0 -298.7 307.6 -316.9 -326.4 336.2 -346.3 10 D 3.5 3.6 3.7 3.9 4.0 4.1 4.2 4.3 4.5 4.6 11 AWC 4.4 -2.4 -1.7 -1.7 -1.8 -1.9 -1.9 -2.0 -2.0 -2.1 12 C [replacement] -11.8 -12.1 -12.5 -12.9 -13.2 -13,6 -14.1 -14.5 -14.9 -15.4 13 C[expansion] -1.9 -1.0 -0.7 -0.7 -0.8 -0.8 -0.8 -0.8 -0.9 -0.9 14 Continuing Value 2466.6 15 16 Net result oper CF 0.0 183.1 218.8 233.7 240.7 247.9 255.3 263.0 270.9 279.0 2754.0 17 PV factor 15.00% 1.000 0.870 0.756 0.658 0.572 0.497 0.432 0.376 0.327 0.284 0.247 18 19 PVs 0.0 159.3 165.4 153.6 137.6 123.2 110.4 98.9 88.5 79.3 680.7 20 21 Value 1797.0 22 Debt 0.0 23 Leases 387.0 24 25 Equity 1410.0 26 27 1 0.30 28 V 0.309 29 F 368.1 Creplace 11.4 30 w 0.052 31 C 0.0219 32 GROWTH 0.177 0.089 0.044 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 33 INFLATOR 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 34 35 SALES 813.0 957.0 1041.8 1087.9 1120.5 1154.2 1188.8 1224.4 1261.2 1299.0 1338.0 36 37 Base_Case: Buyer's Assumptions; tradtional analysis 38 39 [1] use 10 years on basis of loan life and equipment life 40 (2) half of previous year's growth rate until normalization 41 (3) historical cost of Associates 42 43 44 Service of Financing Exh1 Exh2 Base Case + Ready O El + 100% AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) In v 11 AA LUL = = V ab Wrap Text General NT LIX X LO & v 28-O WE F IN Analyze Data Paste B I U a Av A Merge & Center & $ % ) Insert .00 000 Delete v Format Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select 032 A x fx A B D E F G H H ! 1 K L . M N O 0 P R S s T U V V w X Y z AA AB AC 224.0 15.4 32.0 160.0 11.5 32.0 128.0 9.6 64.0 5.8 192.0 13.4 32.0 0.5 57.5 0.0 1.9 32.0 3.8 32.0 96.0 7.7 32.0 0.5 62.8 32.0 0.5 0.5 55.8 0.5 32.0 0.5 64.7 0.5 59.2 32.0 0.5 68.6 61.0 66.6 103.1 21.3 102.9 21.3 102.7 21.2 102.6 21.2 102.5 21.1 102.4 21.1 102.5 21.1 102.5 21.2 233.7 240.7 247.9 255.3 263.0 270.9 279.0 287.4 37 Base_Case: Buyer's Assumptions; tradtional analysis 38 39 [1] use 10 years on basis of loan life and equipment life 40 (2) half of previous year's growth rate until normalization 41 [3] historical cost of Associates 42 43 44 Service of Financing 45 Beginning debt 320.0 288.0 256.0 46 Interest payments 19.2 17.3 47 Principal payments 32.0 32.0 48 Other bank charges 0.5 0.5 49 Lease payments 52.6 54.2 50 51 Total service 103.8 103.4 52 Tax shields 21.5 21.4 53 54 Operating cash flow 183.1 218.8 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 Dn Exh1 Exh2 Base Case + Ready O E + 100% AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) NIT v 11 In AA = = V ab Wrap Text General LIX X [6 * WE 28. O. F IN Analyze Data Paste BIU a Av A Merge & Center & $ % ) Insert Inl IM .00 000 Delete Format v Conditional Format Cell Formatting as Table Styles V Sort & Filter Find & Select 032 4x fx A B D E F G H 1 L M N O P Q R s T U V w 1 Chang Dental Clinic 2 3 4 Exhibit 2 BALANCE SHEET As of December 31 5 2005 2004 2003 6 7 8 9 ASSETS 10 Current assets $4,067 46,942 6,366 1,646 59,021 $ - $ 41,248 5,296 29,528 2,677 0.058 0.008 0.069 0.009 0.103 0.009 46,544 32,205 9209 24217 $69,834 $60,625 $36,408 (30,595) (19,171) (8,687) 39,239 41,454 27,721 0.022 c-factor 1 1 1 39,240 41,455 27,722 $98,261 $87,999 $59,927 11 Cash 12 Accounts receivable 13 Inventory 14 Prepaid expenses 15 Total Current Assets 16 17 Long-term Assets 18 Fixed assets 19 Less: Accumulated amortization - fixed assets 20 Net fixed assets 21 22 Goodwill 23 Total Long-term Assets 24 25 TOTAL ASSETS 26 27 LIABILITIES AND OWNER'S EQUITY 28 Liabilities 29 Bank indebtedness 30 Accounts payable 31 Total Liabilities 32 33 OWNER'S EQUITY 34 S. Chang, Capital 35 36 TOTAL LIABILITIES AND OWNER'S EQUITY 30 37 38 39 40 41 42 $ $39 10,724 10,763 $8,201 8,173 16,374 0.013 10,929 10,929 0.018 0.028 0.052 0.060 0.084 w-factor 87,332 77,236 43,553 $98,261 $87,999 $59,927 Exh1 Exh2 Base Case + Ready O El + 100% AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X NIT Calibri (Body) v 11 In AA = = V ab Wrap Text LUL General LIX WE 28. O. F IN Analyze Data Paste B I U a Av A 3 Merge & Center $ % ) Delete Insert .00 000 Format v Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select 032 4 x fx A B c D E F G G H 1 K L M N N o P o R s T U v W 1 Chang Dental Clinic 2 3 4 Exhibit 1 INCOME STATEMENTS For the year ended December 31 5 6 2005 2004 2003 2005 2004 2003 $812,987 $600,308 $287,281 8 35.4% 109.0% 0.205 0.177 0.140 Dental supplies 0.077 0.067 0.092 $387,008 10-year capitalization of leases 0.027 0.018 0.001 7 8 9 9 FEE REVENUE 10 11 OPERATING EXPENSES: 12 Associate fees 13 Salaries and benefits 14 15 Equipment leasing 16 Rent and utilities 17 Laboratory fees 18 General and office 19 Promotion and entertainment 20 Automobile expenses 21 Telephone 22 Professional focs 23 Interest and bank charges 24 Professional development 25 Insurance 26 Licenses, focs and dues 27 Bed debts (recovered) 28 29 30 TOTAL EXPENSES 31 32 NET INCOME 33 34 Cash operating costs 35 36 37 38 166,727 127,703 62,715 52,582 42,441 21,626 17,420 10,775 5,104 11.187 5,910 5,630 2,913 2,778 1,843 (3,647) 14,872 106,486 93,868 40,508 52,582 38,500 10,636 14,193 8,172 3,677 1,251 3.294 7,015 406 1,612 2,685 17,939 10,484 40,098 39,008 26,291 19,456 29,428 186 9,051 794 1,337 4,697 5,755 13.579 2,021 849 1,282 339 8.687 Amortization 0.309 0.263 548,579 413,308 202,858 0.232 v 368,074 F includes draw $264,408 $187,000 $84,423 $191,324 $116,919 $42,701 39 40 41 42 43 44 Exh1 Exh2 Base Case + Ready O El + 100% AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X Calibri (Body) v 11 = = AA LUU In ab Wrap Text V General NT LIX WE 28- 0 F IN Analyze Data Paste BIU a Av A Merge & Center & $ % ) Insert .00 000 v Delete & Format Conditional Format Cell Formatting as Table Styles V Sort & Filter Find & Select 032 4x fx A B D E F F G H 1 K L . M N 0 0 P o R S S T T U V w X Y Z AA AB AC Dollar amounts in $1,000 1 Chang Dental Clinic 2 Valuation 3 4 2013 2005 0 2006 1 1 2007 2 2008 3 2009 4 2010 5 2011 6 2012 7 2014 9 2015 10 5 8 6 7 S(1-1) 669.9 729.2 761.5 784.4 807.9 832.1 857.1 882.8 909.3 936.6 8 VS(1-4) -206.9 -225.2 -235.2 -242.2 -249.5 -257.0 -264.7 -272.6 -280.8 -289.2 9 F(1-1) -265.4 -273.3 -281.5 -290.0 -298.7 307.6 -316.9 -326.4 336.2 -346.3 10 D 3.5 3.6 3.7 3.9 4.0 4.1 4.2 4.3 4.5 4.6 11 AWC 4.4 -2.4 -1.7 -1.7 -1.8 -1.9 -1.9 -2.0 -2.0 -2.1 12 C [replacement] -11.8 -12.1 -12.5 -12.9 -13.2 -13,6 -14.1 -14.5 -14.9 -15.4 13 C[expansion] -1.9 -1.0 -0.7 -0.7 -0.8 -0.8 -0.8 -0.8 -0.9 -0.9 14 Continuing Value 2466.6 15 16 Net result oper CF 0.0 183.1 218.8 233.7 240.7 247.9 255.3 263.0 270.9 279.0 2754.0 17 PV factor 15.00% 1.000 0.870 0.756 0.658 0.572 0.497 0.432 0.376 0.327 0.284 0.247 18 19 PVs 0.0 159.3 165.4 153.6 137.6 123.2 110.4 98.9 88.5 79.3 680.7 20 21 Value 1797.0 22 Debt 0.0 23 Leases 387.0 24 25 Equity 1410.0 26 27 1 0.30 28 V 0.309 29 F 368.1 Creplace 11.4 30 w 0.052 31 C 0.0219 32 GROWTH 0.177 0.089 0.044 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 33 INFLATOR 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 0.030 34 35 SALES 813.0 957.0 1041.8 1087.9 1120.5 1154.2 1188.8 1224.4 1261.2 1299.0 1338.0 36 37 Base_Case: Buyer's Assumptions; tradtional analysis 38 39 [1] use 10 years on basis of loan life and equipment life 40 (2) half of previous year's growth rate until normalization 41 (3) historical cost of Associates 42 43 44 Service of Financing Exh1 Exh2 Base Case + Ready O El + 100% AutoSave OFF Ey... M7_Project_1_Class Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) In v 11 AA LUL = = V ab Wrap Text General NT LIX X LO & v 28-O WE F IN Analyze Data Paste B I U a Av A Merge & Center & $ % ) Insert .00 000 Delete v Format Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select 032 A x fx A B D E F G H H ! 1 K L . M N O 0 P R S s T U V V w X Y z AA AB AC 224.0 15.4 32.0 160.0 11.5 32.0 128.0 9.6 64.0 5.8 192.0 13.4 32.0 0.5 57.5 0.0 1.9 32.0 3.8 32.0 96.0 7.7 32.0 0.5 62.8 32.0 0.5 0.5 55.8 0.5 32.0 0.5 64.7 0.5 59.2 32.0 0.5 68.6 61.0 66.6 103.1 21.3 102.9 21.3 102.7 21.2 102.6 21.2 102.5 21.1 102.4 21.1 102.5 21.1 102.5 21.2 233.7 240.7 247.9 255.3 263.0 270.9 279.0 287.4 37 Base_Case: Buyer's Assumptions; tradtional analysis 38 39 [1] use 10 years on basis of loan life and equipment life 40 (2) half of previous year's growth rate until normalization 41 [3] historical cost of Associates 42 43 44 Service of Financing 45 Beginning debt 320.0 288.0 256.0 46 Interest payments 19.2 17.3 47 Principal payments 32.0 32.0 48 Other bank charges 0.5 0.5 49 Lease payments 52.6 54.2 50 51 Total service 103.8 103.4 52 Tax shields 21.5 21.4 53 54 Operating cash flow 183.1 218.8 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 Dn Exh1 Exh2 Base Case + Ready O E + 100%
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
