They plan to work for 15 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 KHZ Consulting if they decide to invest their savings in its purchase. Koala and Cheese believe they can earn 12 by investing in the stock market so their cost of capital is equal to their opportunity cost of 12. Koala and Cheese 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 112. 00 Annual property tax 140, 000. 00 Income tax rate 20, 000 Cost of capital 160. 00 Average variable cost per hour 1 1 , OHO.
O Annual other cash fixed costs 39 Building tax depreciation per year 12 Weeks each owner works a year 48 Koala and Cheese 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 need for depreciation. Each owner will bill 30 hours per week for 48 weeks. There will be no other employees. Koala and Cheese plan to sell the business for 1. 5 times what they paid for the building and the land at the end of the 15th year (750, 000).
Neither the land nor the building will appreciate in value during the 15 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. Any gain on the sale will be taxed at 39. Your team needs to Scenario A. Use the template in Blackboard 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 (NP) and Internal Rate of Return (AIR) 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. Scenario B. Koala and Cheese are concerned that the 160. 00 price per hour which they hope to charge customers is not sustainable. Find the price per hour (to nearest whole cent) that would be necessary to charge in order to make the NP equal to zero. Scenario C. Change the price per hour back to the original price 160. Find the percentage increase in variable costs which would make the NP equal to zero.
Discuss whether the business should be purchased. Make a recommendation based on your analysis. You should include analysis of the sensitivity that you found in your calculations in parts b and c. You should consider NP, Profitability Index, Cash Payback Period, Simple Rate of Return and Internal Rate of Return. Instructions for group sign-up 1 . Before starting this assignment make sure that you have signed up for a team in Blackboard by using the Excel Project Sign-Up Sheet located in the Excel Project folder. All students MUST work in a team to receive points for the project.
Instructions for project This assignment should be completed n your team which you signed up for on Blackboard. Start with the template that is found in Blackboard folder titled Excel Project. Save this file with your group number in the title. Example Excel Project Group 1 The first page of the template should serve as your input page. The first step in completing the project is to manually fill in the input data from this directions page. After you have inserted your input data, the rest of your spreadsheet should be entirely formula driven.
This means that on page 2 of your spreadsheet, where you are doing your actual analysis, EVERY number would either be referenced from the data input page, or should be calculated by a formula. You should be able to use your knowledge of fixed and variable cost to complete the income statements for each year. Many hints have been added as comments on the spreadsheet. To compute the Net Present Value, use the function wizard. Select Financial Functions and then NP. 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.
To calculate the Simple Rate of Return, average the net income across all years, and divide average net income by the initial investment. You can use a function to average the net incomes of years 1-15. 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 O 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. You MUST use Goal Seek to do your sensitivity analysis in parts b and c. Goal Seek will give you very strong evidence that your spreadsheet is working. Goal seek will use an iterative process to make one cell be a particular value by changing another value. In our cases, we are going to make NP become O by changing first the price charged per consulting hour, and next the rate at which variable costs will increase. You can find Goal Seek on the Data menu in Excel. It is under the What if analysis feature. ) To find the consulting price per hour (to nearest whole cent) that would be necessary to make the NP equal to zero, do the allowing Copy your formulas from the Analysis Part A tab to the Analysis Part B tab. We want to leave our original inputs as they are and show the new price per hour for consulting which will make NP zero. You will notice a yellow colored cell has been added to the Analysis Part B tab. Manually type in the 160. 00 per hour consulting price we started with. Now, you will have to update your formula in the Sales Revenue Row (row 10) to incorporate cell 83 which has our consulting fee per hour.
If you begin to see at any time when you are making these changes, it is k. These just show up when the column is not wide enough. If you simply expand the width of your column these will turn back into numbers. Now that you are linked to cell 83 go ahead and use goal seek by going to the Data tab in the top of the Excel screen and selecting Goal Seek. If you are using the new version of Excel, click on the Data tab, under Data Tools click on the What-elf Analysis and then on Goal Seek. Goal seek will ask you what value you want to change to what amount by changing what cell.
You wish to change the NP value to O by changing the consulting price per hour in cell 3. B) To find the increase in variable cost that would be necessary to make the NP positive, do the following Copy your formulas from the Analysis Part A tab to the Analysis Part C tab. We want to leave our original inputs as they are and show the required percentage increase in variable costs which will make NP zero. You will notice a yellow colored cell has been added to the Analysis Part C tab. Manually type in the 3 per year increase in variable costs we started with.
Now, you will have to update your formula in the variable cost row starting with year 2 to incorporate cell 3 which has our new increase in variable cost per year. Now that you are linked to the revised increase in variable cost cell (83) use goal seek by going to the Tools menu and selecting Goal Seek. If you are using the new version of Excel, click on the Data tab, under Data Tools click on the What-elf Analysis and then on Goal Seek. Goal seek will ask you what value you want to change to what amount by changing what cell. You wish to change the NP value too by changing the revised increase in variable costs in cell 83.
