ACCT 285 Excel Case \# 3 Basic Instructions: 1) It is expected that you work individually on this assignment. Working with another student is a violation of the academic integrity pledge. 2) Please use the Excel template provided in the Excel Case \#3 assignment link in Canvas. You must complete this assignment using Microsoft Excel and submit an Excel file for grading. 3) It is required that you use links and formulas to fill in the shaded cells within the assignment template. Failure to formula drive your cells will result in a zero grade. 4) Each tab in your template will match the headings throughout the instructions document. 5) Check your answers against the check figures provided as you work. 6) Once you have completed your template, please upload your Excel file to the Excel Case \#3 assignment link in Canvas for grading. You must upload your completed template before the due date and time in order to receive credit. 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 business's 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. * 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. HELP 1. To compute the Net Present Value, use the function wizard. Select Financial Functions and then NPV. Excel will ask you for a rate, the cost of capital. Reference this from your input page. Excel will also ask you for a range of values. This should consist of your cash flows for years 1-10. DO NOT PUT IN YOUR INITIAL CASH OUTFLOW (YEAR O CASH FLOW). You will need to separately add in the initial cash outflow outside of the NPV formula in your formula bar at the top of the page. MAKE SURE THAT THE INITIAL CASH FLOW IS NEGATIVE. 2. To compute the profitability index, divide NPV by the initial investment which consists of the amounts paid for the land, building and working capital. 3. To compute the Internal Rate of Return, again use the function wizard. Select Financial Functions and then IRR. It will ask you for a range of cash flows. THIS TIME BE SURE TO INCLUDE THE YEAR 0 CASH FLOW AND MAKE SURE THAT IT IS NEGATIVE. 4. To calculate the Simple Rate of Return, average the net income across all years, and divide the average net income by the initial investment. You can use a function to average the net incomes of years 1-10. Select Statistical Functions and then AVERAGE. Once you have found your average you will need to go back up to the formula bar and divide by the initial investment. Do not include the working capital investment here. just the land and building. If you get $0 as your answer, right click and select format cells. Click on numbers tab, percentage, and 2 decimal places. Once you do this, you should see a percentage in the cell. \begin{tabular}{|l|l|r|} \multicolumn{2}{|c}{} & \multicolumn{2}{c|}{ B } \\ \hline \multirow{2}{*}{1} & Data Input for Business Purchase Decision: \\ \hline 2 & Building Cost & $400,000 \\ \hline 3 & Land Cost & $100,000 \\ \hline 4 & Average consulting hours per week: & 60 \\ \hline 5 & Weeks worked per year & 48 \\ \hline 6 & Average price charged per hour & $160 \\ \hline 7 & Sales price annual appreciation per year & 6% \\ \hline 8 & Average variable cost per hour & $112 \\ \hline 9 & Increase in variable cost per year & 3% \\ \hline 10 & Annual other cash fixed costs & $140,000 \\ \hline 11 & Cost of capital & 12% \\ \hline 12 & Investment in Working Capital & $40,000 \\ \hline 13 & Sales Price For Business & $750,000 \\ \hline 14 & Depreciation Per Year & $20,000 \\ \hline \end{tabular} C 15 16 17 18 INPUTSAnalysis+ ACCT 285 Excel Case \# 3 Basic Instructions: 1) It is expected that you work individually on this assignment. Working with another student is a violation of the academic integrity pledge. 2) Please use the Excel template provided in the Excel Case \#3 assignment link in Canvas. You must complete this assignment using Microsoft Excel and submit an Excel file for grading. 3) It is required that you use links and formulas to fill in the shaded cells within the assignment template. Failure to formula drive your cells will result in a zero grade. 4) Each tab in your template will match the headings throughout the instructions document. 5) Check your answers against the check figures provided as you work. 6) Once you have completed your template, please upload your Excel file to the Excel Case \#3 assignment link in Canvas for grading. You must upload your completed template before the due date and time in order to receive credit. 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 business's 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. * 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. HELP 1. To compute the Net Present Value, use the function wizard. Select Financial Functions and then NPV. Excel will ask you for a rate, the cost of capital. Reference this from your input page. Excel will also ask you for a range of values. This should consist of your cash flows for years 1-10. DO NOT PUT IN YOUR INITIAL CASH OUTFLOW (YEAR O CASH FLOW). You will need to separately add in the initial cash outflow outside of the NPV formula in your formula bar at the top of the page. MAKE SURE THAT THE INITIAL CASH FLOW IS NEGATIVE. 2. To compute the profitability index, divide NPV by the initial investment which consists of the amounts paid for the land, building and working capital. 3. To compute the Internal Rate of Return, again use the function wizard. Select Financial Functions and then IRR. It will ask you for a range of cash flows. THIS TIME BE SURE TO INCLUDE THE YEAR 0 CASH FLOW AND MAKE SURE THAT IT IS NEGATIVE. 4. To calculate the Simple Rate of Return, average the net income across all years, and divide the average net income by the initial investment. You can use a function to average the net incomes of years 1-10. Select Statistical Functions and then AVERAGE. Once you have found your average you will need to go back up to the formula bar and divide by the initial investment. Do not include the working capital investment here. just the land and building. If you get $0 as your answer, right click and select format cells. Click on numbers tab, percentage, and 2 decimal places. Once you do this, you should see a percentage in the cell. \begin{tabular}{|l|l|r|} \multicolumn{2}{|c}{} & \multicolumn{2}{c|}{ B } \\ \hline \multirow{2}{*}{1} & Data Input for Business Purchase Decision: \\ \hline 2 & Building Cost & $400,000 \\ \hline 3 & Land Cost & $100,000 \\ \hline 4 & Average consulting hours per week: & 60 \\ \hline 5 & Weeks worked per year & 48 \\ \hline 6 & Average price charged per hour & $160 \\ \hline 7 & Sales price annual appreciation per year & 6% \\ \hline 8 & Average variable cost per hour & $112 \\ \hline 9 & Increase in variable cost per year & 3% \\ \hline 10 & Annual other cash fixed costs & $140,000 \\ \hline 11 & Cost of capital & 12% \\ \hline 12 & Investment in Working Capital & $40,000 \\ \hline 13 & Sales Price For Business & $750,000 \\ \hline 14 & Depreciation Per Year & $20,000 \\ \hline \end{tabular} C 15 16 17 18 INPUTSAnalysis+