MACRS Table

Your uncle the dentist has a problem for you. He wants to know if it is a good investment for him to buy a 3D mouth scanner and crown maker. The machine automatically determines the best shape, dimensions, and fit and then makes a new crown for a tooth, in the office, in under an hour. This will save several days of waiting and the crown should fit much better than the old method. The machine costs $90,000. Shipping and installation will be $5,000. He will need to have an inventory for all the basic materials to make the crown so inventory will increase by $18,000 of which half can go on accounts payable. Because crowns are so expensive, he will need to increase his accounts receivable by $15,000 for those that need to make payments. He will also need to hire two technicians. With salary and benefits, these people will cost $200,000 annually (total). He knows he is currently doing about 6 crowns per week. A typical crown costs $1,000 so he estimates he can increase revenues by $6,000 per week (52 weeks in a year). Material cost for a crown is $100/crown so $600 per week. Additional costs are minimal (He already has the lights on), so maybe $50 per month in extra electricity. Your analysis should be by years and not weeks. The machine depreciates on a 7-year MACRS schedule. Your uncle says if he buys one, he would likely only keep it for five years and then upgrade to a new machine at that time because the technology is progressing so quickly. That is, he is going to salvage the machine in five years. He expects he might be able to sell it at that time for $10,000. Your uncle will borrow half of the upfront cost from the bank at 6%. The rest he would invest from his own money. He does all of his financing this way and requires 30% return on the investment he makes into his business. His tax rate is 34%. Realize that you need to calculate his WACC. Please put this calculation to the right of the decision criteria you will calculate below. Calculate NPV, IRR, MIRR and Payback for your uncle's machine. Put these in the top left corner of the worksheet. Finally, below the free cash flow calculations, do a one-way table with NPV as the variable of interest. Vary sales by -15%, -10%, 0%, -5%, +5%, +10% and +15%. (NOTE: Excel does not work with percentages in a table. You must allow sales and any variable costs related to sales to change to create a valid table, that is, you need to have those values be able to change via one cell.) You should learn something from that table. Next to it, write a brief summary of your analysis that your dentist uncle will understand. Year 1 2 3 4 5 6 6 7 8 3-year 33.33% 44.45% 14.81% 7.41% Recovery Period 5-year 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% 7-year 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% Your uncle the dentist has a problem for you. He wants to know if it is a good investment for him to buy a 3D mouth scanner and crown maker. The machine automatically determines the best shape, dimensions, and fit and then makes a new crown for a tooth, in the office, in under an hour. This will save several days of waiting and the crown should fit much better than the old method. The machine costs $90,000. Shipping and installation will be $5,000. He will need to have an inventory for all the basic materials to make the crown so inventory will increase by $18,000 of which half can go on accounts payable. Because crowns are so expensive, he will need to increase his accounts receivable by $15,000 for those that need to make payments. He will also need to hire two technicians. With salary and benefits, these people will cost $200,000 annually (total). He knows he is currently doing about 6 crowns per week. A typical crown costs $1,000 so he estimates he can increase revenues by $6,000 per week (52 weeks in a year). Material cost for a crown is $100/crown so $600 per week. Additional costs are minimal (He already has the lights on), so maybe $50 per month in extra electricity. Your analysis should be by years and not weeks. The machine depreciates on a 7-year MACRS schedule. Your uncle says if he buys one, he would likely only keep it for five years and then upgrade to a new machine at that time because the technology is progressing so quickly. That is, he is going to salvage the machine in five years. He expects he might be able to sell it at that time for $10,000. Your uncle will borrow half of the upfront cost from the bank at 6%. The rest he would invest from his own money. He does all of his financing this way and requires 30% return on the investment he makes into his business. His tax rate is 34%. Realize that you need to calculate his WACC. Please put this calculation to the right of the decision criteria you will calculate below. Calculate NPV, IRR, MIRR and Payback for your uncle's machine. Put these in the top left corner of the worksheet. Finally, below the free cash flow calculations, do a one-way table with NPV as the variable of interest. Vary sales by -15%, -10%, 0%, -5%, +5%, +10% and +15%. (NOTE: Excel does not work with percentages in a table. You must allow sales and any variable costs related to sales to change to create a valid table, that is, you need to have those values be able to change via one cell.) You should learn something from that table. Next to it, write a brief summary of your analysis that your dentist uncle will understand. Year 1 2 3 4 5 6 6 7 8 3-year 33.33% 44.45% 14.81% 7.41% Recovery Period 5-year 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% 7-year 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46%