It is required that you use links and formulas to fill in the shaded cells and

Zhejia and Hank have always wanted to start their own consulting firm. They have the opportunity to purchase an existing consulting firm for $500,000. The purchase price of $500,000 would be allocated as follows: $400,000 for the existing businesss building $100,000 for the land on which the building sits. Zhejia and Hank plan to work for 10 years and then retire after selling their business to new owners. Start-up costs would include $40,000 in working capital which is to be used for advertising, salaries and supplies. They plan on naming their business ZH Consulting if they decide to invest their savings in its purchase. Zhejia and Hank believe they can earn 12% by investing in the stock market so their cost of capital is equal to their opportunity cost of 12%. They believe a Simple Rate of Return on a project like this should be at least 30% because of the risk. They have made the following estimates: Average consulting hours per week: 30 per owner Average charge to customer* $160.00 Average variable cost per hour** $112.00 Annual other cash fixed costs $140,000.00

Building tax depreciation per year $20,000 Cost of capital 12% Weeks each owner works per year: 48 * Zhejia & Hank expect the price they charge per hour to increase by 6% each year. ** Variable costs are expected to increase by 3% per year. All payments for costs are made in the year incurred. Depreciation is $20,000 per year so no calculation is needed for depreciation. Each owner will bill 30 hours per week for 48 weeks. There will be no other employees. Zhejia & Hank plan to sell the business for 1.5 times what they paid for the building and the land at the end of the 10th year ($750,000). Neither the land nor the building will appreciate in value during the 10 year period. The gain on the sale of the business will equal the sales price minus the book value of the land and the building. Required: Analysis Use the template provided to complete a capital budgeting analysis of the decision to purchase the existing consulting business. You should show cash flow year by year, and compute the Net Present Value (NPV) and Internal Rate of Return (IRR) using the function wizard in Excel. Also, compute the Simple Rate of Return, Profitability Index and Cash Payback Period. You should use formulas for everything except the cash payback period. Cash payback period will have to be input manually.

\begin{tabular}{|l|r|} \hline \multicolumn{2}{|l|}{ Data Input for Business Purchase Decision: } \\ \hline Building Cost & $400,000 \\ \hline Land Cost & $100,000 \\ \hline Average consulting hours per week: & 60 \\ \hline Weeks worked per year & 48 \\ \hline Average price charged per hour & $160 \\ \hline Sales price annual appreciation per year & 6% \\ \hline Average variable cost per hour & $112 \\ \hline Increase in variable cost per year & 3% \\ \hline Annual other cash fixed costs & $140,000 \\ \hline Cost of capital & 12% \\ \hline Investment in Working Capital & $40,000 \\ \hline Sales Price For Business & $750,000 \\ \hline Depreciation Per Year & $20,000 \\ \hline \end{tabular} Business Purchase Scenario Building Cost \begin{tabular}{|l|r|} \hline \multicolumn{2}{|l|}{ Data Input for Business Purchase Decision: } \\ \hline Building Cost & $400,000 \\ \hline Land Cost & $100,000 \\ \hline Average consulting hours per week: & 60 \\ \hline Weeks worked per year & 48 \\ \hline Average price charged per hour & $160 \\ \hline Sales price annual appreciation per year & 6% \\ \hline Average variable cost per hour & $112 \\ \hline Increase in variable cost per year & 3% \\ \hline Annual other cash fixed costs & $140,000 \\ \hline Cost of capital & 12% \\ \hline Investment in Working Capital & $40,000 \\ \hline Sales Price For Business & $750,000 \\ \hline Depreciation Per Year & $20,000 \\ \hline \end{tabular} Business Purchase Scenario Building Cost