I need help with questions 5-7
Suppose you are planning to expand your Personal Fitness Center. You own the current space you use, but have extra unused space in the building. You are thinking about buying new equipment and hiring an assistant to help with new clients. Here are the assumptions to use: 1. The cost of equipment (weights, treadmill, etc.) is estimated to be $90,000. 2. The bank has offered a loan at 6% for 5 years, although you do have $90,000 in savings. 3. Your friend has offered to rent your spare space for $1000 per month (for the next 5 years). 4. You think you could hire an assistant (including benefits) for $40,000 per year. 5. You plan to spend an additional $5000 in advertising annually to attract new clients. 6. You estimate that you will attract 100 new clients as a result of the expansion (some may come & go over the 5-year period) and keep 100 clients, on average, each year. You currently charge $60 per month for each client. I've provided a Excel spreadsheet template to start. You'll need to calculate the expected cash flow as a result of this analysis for the next 5 years - the payback period you'd like to use. (One reason I've provided this template is to remind you that by organizing your information and then using cells in your formulas, you can do a sensitivity analysis very easily by just changing values in cells.) To deliver: 1. The Excel spreadsheet showing your calculations. Assume you use your savings for this part of the analysis. Based on a 5-year window, what is the NPV of this project? Is this a viable project? Why/Why not? 2. How does your NPV change if you adjust: the discount rate? The number of new clients? The rate you charge? That is, how sensitive is your answer in part 1 to possible variables in the project? 3. Suppose that at the end of 5 years you can sell the equipment for $20,000. How does this salvage value affect your decision? 4. If you could take your $90,000 and put it in an exchange traded fund (ETFs earn the stock market's average and have very low fees) and earn, say 7% annually (on average), would this be smart? 5. Copy your current worksheet and create a new one for this next part. Name this new sheet "Taxes". How does the story change if we include taxes? Let's make one other adjustment: you think you can attract 120 new clients each paying the $60 monthly fee. What happens to the project's viability? (Notice that we haven't included the firm's overall tax rate on the cash flow from the original firm....only what might relate to this expansion.) 6. Copy your current worksheet and create a new one named "Taxes & Loan". Use a tax rate of 10%. Let's say that you think 120 new clients is reasonable assumption. Plus, you decide to finance the equipment's purchase with the loan from the bank at the 6% rate. Let's simplify the analysis by saying that you can pay interest-only for the first 4 years and then repay the entire principal and that year's interest in year 5. What happens to the project's viability with 120 new clients? 100 new clients? 7. Copy the current worksheet and create a new one named "Elasticity". Finally, let's add some elasticity application to this problem. We've held the membership fee at $60 throughout the analysis. Let that change increasing and/or lowering the fee and follow the law of demand. What happens to your total revenue and your NPV? (Remember to think about your price change in terms of percentages. For example, if you increase your fee to $65 and lose clients to only 110, then using this formula E.= change in o % change in pl (change in Caverage = 1120-110) 115 - 1.76 This means the Es=1.09... that is demand for your service is elastic. If you increase your price by 8%, your quantity demanded falls by more (8.7%). This means your total revenue will fall from $7200 per month to $7150 per month. (You'd then include this $7150 in your spreadsheet & calculate the new NPV). This seems reasonable to me... I'd think that demand for personal fitness service is somewhat elastic there are plenty of substitutes. Maybe your service is in a smaller town or you're a very good trainer though So, as you raise or lower your price, provide an elasticity figure and back up your claim about what happens to total revenue. That is, don't say: "I'll raise my price to $100 and keep 90 clients"... unless maybe you're an excellent personal fitness instructor!" (Also, notice that we haven't given your employee a raise this whole time...what happens if you adjust that?!) 1 Cost of equipment 3 Expansion expenses salary & benefits est. overhead advertising 7 Total expense due to expansion 8 9 Expansion revenue 10 new clients 11 100 $60 12 13 annual rev due to expansion 14 15 Expected Cash Flow due to expansion 17 Discount Rate 18 19 NPV Analysis periods 20 23 Present Value 25 NPV= (using the excel formula) 26 NPV= (using PV cash flow-cost) cell B25 should = cell B26 Salvage Value Suppose you are planning to expand your Personal Fitness Center. You own the current space you use, but have extra unused space in the building. You are thinking about buying new equipment and hiring an assistant to help with new clients. Here are the assumptions to use: 1. The cost of equipment (weights, treadmill, etc.) is estimated to be $90,000. 2. The bank has offered a loan at 6% for 5 years, although you do have $90,000 in savings. 3. Your friend has offered to rent your spare space for $1000 per month (for the next 5 years). 4. You think you could hire an assistant (including benefits) for $40,000 per year. 5. You plan to spend an additional $5000 in advertising annually to attract new clients. 6. You estimate that you will attract 100 new clients as a result of the expansion (some may come & go over the 5-year period) and keep 100 clients, on average, each year. You currently charge $60 per month for each client. I've provided a Excel spreadsheet template to start. You'll need to calculate the expected cash flow as a result of this analysis for the next 5 years - the payback period you'd like to use. (One reason I've provided this template is to remind you that by organizing your information and then using cells in your formulas, you can do a sensitivity analysis very easily by just changing values in cells.) To deliver: 1. The Excel spreadsheet showing your calculations. Assume you use your savings for this part of the analysis. Based on a 5-year window, what is the NPV of this project? Is this a viable project? Why/Why not? 2. How does your NPV change if you adjust: the discount rate? The number of new clients? The rate you charge? That is, how sensitive is your answer in part 1 to possible variables in the project? 3. Suppose that at the end of 5 years you can sell the equipment for $20,000. How does this salvage value affect your decision? 4. If you could take your $90,000 and put it in an exchange traded fund (ETFs earn the stock market's average and have very low fees) and earn, say 7% annually (on average), would this be smart? 5. Copy your current worksheet and create a new one for this next part. Name this new sheet "Taxes". How does the story change if we include taxes? Let's make one other adjustment: you think you can attract 120 new clients each paying the $60 monthly fee. What happens to the project's viability? (Notice that we haven't included the firm's overall tax rate on the cash flow from the original firm....only what might relate to this expansion.) 6. Copy your current worksheet and create a new one named "Taxes & Loan". Use a tax rate of 10%. Let's say that you think 120 new clients is reasonable assumption. Plus, you decide to finance the equipment's purchase with the loan from the bank at the 6% rate. Let's simplify the analysis by saying that you can pay interest-only for the first 4 years and then repay the entire principal and that year's interest in year 5. What happens to the project's viability with 120 new clients? 100 new clients? 7. Copy the current worksheet and create a new one named "Elasticity". Finally, let's add some elasticity application to this problem. We've held the membership fee at $60 throughout the analysis. Let that change increasing and/or lowering the fee and follow the law of demand. What happens to your total revenue and your NPV? (Remember to think about your price change in terms of percentages. For example, if you increase your fee to $65 and lose clients to only 110, then using this formula E.= change in o % change in pl (change in Caverage = 1120-110) 115 - 1.76 This means the Es=1.09... that is demand for your service is elastic. If you increase your price by 8%, your quantity demanded falls by more (8.7%). This means your total revenue will fall from $7200 per month to $7150 per month. (You'd then include this $7150 in your spreadsheet & calculate the new NPV). This seems reasonable to me... I'd think that demand for personal fitness service is somewhat elastic there are plenty of substitutes. Maybe your service is in a smaller town or you're a very good trainer though So, as you raise or lower your price, provide an elasticity figure and back up your claim about what happens to total revenue. That is, don't say: "I'll raise my price to $100 and keep 90 clients"... unless maybe you're an excellent personal fitness instructor!" (Also, notice that we haven't given your employee a raise this whole time...what happens if you adjust that?!) 1 Cost of equipment 3 Expansion expenses salary & benefits est. overhead advertising 7 Total expense due to expansion 8 9 Expansion revenue 10 new clients 11 100 $60 12 13 annual rev due to expansion 14 15 Expected Cash Flow due to expansion 17 Discount Rate 18 19 NPV Analysis periods 20 23 Present Value 25 NPV= (using the excel formula) 26 NPV= (using PV cash flow-cost) cell B25 should = cell B26 Salvage Value
