Accounting Excel Project , Please see Acct-Excel Project Direction.doc

Accounting Excel Group Project Directions - Kayla and Zhejia have always wanted to start their own consulting firm. Kayla and Zhejia have the opportunity to purchase an existing consulting firm for $500,000. The purchase price of $500,000 would be allocated $400,000 for the existing business's building and $100,000 for the land on which the building sits. 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 \"KZ Consulting\" if they decide to invest their savings in its purchase. Kayla and Zhejia believe they can earn 12% by investing in the stock market so their cost of capital is equal to their opportunity cost of 12%. Kayla and Zhejia 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: Average charge to customer:* Average variable cost per hour** Annual property tax Annual other cash fixed costs Income tax rate Building tax depreciation per year Cost of capital Weeks each owner works a year: 30 per owner $160.00 $112.00 $11,000.00 $140,000.00 39% $20,000 12% 48 * Kayla and Zhejia 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. Kayla and Zhejia 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: a. 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 (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. b. Scenario B. Kayla and Zhejia 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 NPV equal to zero. c. Scenario C. Change the price per hour back to the original price $160. Find the percentage increase in variable costs which would make the NPV equal to zero. d. 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 NPV, 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: 1. This assignment should be completed in your team which you signed up for on Blackboard. 2. 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 3. 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 should 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. 4. 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-15. DO NOT PUT IN YOUR INITIAL CASH OUTFLOW (YEAR 0 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. 5. To compute the profitability index, divide NPV by the initial investment which consists of the amounts paid for the land, building and working capital. 6. 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. 7. 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 $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. 8. 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 NPV become 0 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. a) To find the consulting price per hour (to nearest whole cent) that would be necessary to make the NPV equal to zero, do the following: a. Copy your formulas from the \"Analysis Part A\" tab to the \"Analysis Part B\" tab. b. We want to leave our original inputs as they are and show the new price per hour for consulting which will make NPV 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 B3 which has our consulting fee per hour. ***If you begin to see \"#####\" at any time when you are making these changes, it is ok. 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 B3 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-If Analysis and then on Goal Seek. d. Goal seek will ask you what value you want to change to what amount by changing what cell. You wish to change the NPV value to 0 by changing the consulting price per hour in cell B3. c. b) To find the increase in variable cost that would be necessary to make the NPV positive, do the following: a. Copy your formulas from the \"Analysis Part A\" tab to the \"Analysis Part C\" tab. b. We want to leave our original inputs as they are and show the required percentage increase in variable costs which will make NPV 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 B3 which has our new increase in variable cost per year. c. Now that you are linked to the revised increase in variable cost cell (B3) 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-If Analysis and then on Goal Seek. d. Goal seek will ask you what value you want to change to what amount by changing what cell. You wish to change the NPV value to 0 by changing the revised increase in variable costs in cell B3. SAVE YOUR SPREADSHEET BEFORE TRYING ANY OF THE CHANGES SUGGESTED ON THE RUBRIC. 9. You must finish part D. 10. Before you submit, you need to be sure you get the check figures below. Your team will be evaluated based on the rubric on the next page. Submit your assignment for your team through Blackboard by attaching and submitting your completed template via your group assignment page. SUBMIT ONLY ONE PER TEAM. Do not use email to submit your project. Evaluation Rubric that will be used to assess your project Have correct answers that match the following check figures: NPV for Scenario A analysis is $60,554.51 Possible 4 Scenario A Analysis Profitability Index .11 2 IRR for Scenario A analysis is 13.13% 4 Simple rate of return Scenario A is 22.05% 3 Cash Payback Period is 9.51 years 2 Scenario A Analysis Yr 15 Cash Flow is $829,083 2 Scenario B Yr 5 Net Income $20,941 (after using Goal Seek) 2 Scenario C Yr 10 Cash Flow $117,497 (after using Goal Seek) 2 All cells are formula driven (you lose 2 points each time there is a number entered in the analysis worksheets) Your worksheet passes our tests of your formulas. Be sure that you only make one change at a time (change back the prior one) On your input tab we will: Change the price per hour to $200; NPV becomes $718,953.70 10 points Change depreciation to $5000 per year; Yr 4 Net Income = $24,614 2 Change the number of weeks each owner works to 45 and cash-flow for year 11 becomes $274,122 2 Change the tax rate to 15%; NPV becomes $225,993.24 2 Change the investment in working capital to $140,000 & NPV becomes ($21,175.87). 2 2 Your one page write-up includes: an analysis of the three cases 4 makes a recommendation based on the analysis 2 uses good grammar, sentence structure, and paragraphing skills 3 Bonus points for exemplary analysis or presentation Up to 2 Total Score 50 Comments Your Score 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 A Data Input for Business Purchase Decision: Building Cost Land Cost Average consulting hours per week: Weeks worked per year Average price charged per hour Sales price annual appreciation per year Average variable cost per hour Increase in variable cost per year Annual property tax Annual other cash fixed costs Income tax rate Cost of capital Investment in Working Capital Sales Price For Business Depreciation Per Year Group Members B A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 B C D E F G H I J K L 0 1 2 3 4 5 6 7 8 9 10 M N O P Q Business Purchase Scenario Year Land Cost 11 12 13 14 15 $0.00 $0.00 $0.00 $0.00 $0.00 Building Cost Sales Gain on Sale of Business Total Revenue Variable Costs Contribution Margin Property tax Cash Fixed costs Depreciation Income before tax Tax Net Income Depreciation Sales Amount Less Taxable Gain Working Capital Cash Flow Book Value of Land Book Value of Building Payback Outstanding Net present value Profitability Index Internal rate of return Simple rate of return Payback Period $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 B C D E F G H I J K L 0 1 2 3 4 5 6 7 8 9 10 M N O P Q Change in Consulting Fee Scenario Consulting Fee Per Hour Year Land Cost 11 12 13 14 15 $0.00 $0.00 $0.00 $0.00 $0.00 Building Cost Sales Gain on Sale of Business Total Revenue Variable Costs Contribution Margin Property tax Cash Fixed costs Depreciation Income before tax Tax Net Income Depreciation Sales Amount Less Taxable Gain Working Capital Cash Flow Book Value of Land Book Value of Building Payback Outstanding Net present value Profitability Index Internal rate of return Simple rate of return Payback Period $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 B C D E F G H I J K L 0 1 2 3 4 5 6 7 8 9 10 M N O P Q Business Purchase Scenario Percentage Change in VC Year Land Cost 11 12 13 14 15 $0.00 $0.00 $0.00 $0.00 $0.00 Building Cost Sales Gain on Sale of Business Total Revenue Variable Costs Contribution Margin Property tax Cash Fixed costs Depreciation Income before tax Tax Net Income Depreciation Sales Amount Less Taxable Gain Working Capital Cash Flow Book Value of Land Book Value of Building Payback Outstanding Net present value Profitability Index Internal rate of return Simple rate of return Payback Period $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Click "alt" and "enter" at the same time to start a new line