Reference no: EM132008859

SPD Tax Service is a regional tax preparation firm that competes with such national chains as H&R Block. The company is considering expanding and needs a financial model to analyze the decision to open a new store. Key factors affecting this decision include the demographics of the proposed location, price points that can be achieved in the target market, and the availability of funds for marketing and advertising. Capital expenditures will be ignored because unused equipment from other locations can often be shifted to a new store for the first year until they can be replaced periodically through the fixed cost budget. SPD’s target markets being considered are communities with populations between 30,000 and 50,000, assumed to be uniformly distributed. Market demand for tax preparation service is directly related to the number of households in the territory; approximately 15% of households are anticipated to use a tax preparation service. Assuming an average of 2.5 people per household, this can be expressed as 0.15*population/2.5. SPD estimates that its first year demand will have a mean of 5% of the total market demand, and for every dollar of advertising, the mean increases by 2%. The first year demand is assumed to be normal with a standard deviation of 20% of the mean demand. An advertising budget of $5,000 has been approved but is limited to 10% of annual revenues. Demand grows fairly aggressively in the second and third year and is assumed to have a triangular distribution with a minimum value of 20%, most likely value of 35%, and maximum value of 40%. After year 3, demand growth is between 5% and 15%, with a most likely value of 7%. The average charge for each tax return is $175, and increases at a rate that is normally distributed with a mean of 4% with a standard deviation of 1.0% each year. Variable costs average $15 per customer, and increase annually at a rate that is normally distributed with a mean of 3% with a standard deviation of 1.5%. Fixed costs are estimated to be approximately $35,000 for the first year, and grow annually at a rate between 1.5% and 3%. Develop a Monte Carlo simulation model to find the distribution of the net present value of the profitability of a new store over a 5-year period using a discount rate of 3%. Please use 500 simulated trials and attach the excel spreadsheet.