Below Excel File:gg

While Answering write down formula you used to solve question. Thanks

  
 
 
 

Using Scenarios and Data Tables to Support Pricing Decisions Water Company has used a two-tier price structure for its industrial customers which was designed to encourage water usage. Industrial customers have paid 9 cents a gallon for the first 100,000 gallons used each month, and 7 cents per gallon for each gallon beyond the first 100,000. Because of a water shortage in the area, and increasing consumer pressure, ISKI is planning to change its rate structure to one that encourages water conservation. The plan is to retain the two-tier pricing structure, but reverse the tiers so that a higher rate is paid for gallons used beyond some cutoff level. For example, the rates might simply be reversed so that customers would be charged 7 cents a galon for the first 100,000 gallons and 9 cents a gallon for all additional gallons. Another possible rate structure might be 6 cents a gallon for the first 50,000 gallons and 9.5 cents for all additional gallons. The new rate structure should produce approximately the same revenue as the previous structure. It is important for ISKI to know how the new rate structure affects individual industrial customers, since opposition can be expected from any customers whose water costs are substantially increased. ISKI has asked us to create a spreadsheet model which will allow the analysis of the impact of alternative rates and cutoff levels. Although the price changes are designed to reduce water use ISKI does not have a good estimate of how much reduction will occur. Therefore, for the purpose of this spreadsheet analysis, we will assume that the number of gallons used by each customer is unaffected by the changes in the rate structure. Starting Guidelines: 1. Open the Excel worksheet on your computer 2. For the current rate structure (base rate $0.09/gallon, cutoff at 100,000 gallon, and $0.07/gallon thereafter). Enter the data into the worksheet (0.09, 0.07, 100000) in B20...B22. Develop the formula(s) that will compute the amount due by a customer, referring to the cells that contain the data. Enter the formula for Baxter in C2. Always use cell references in your formula (avoid typing in values). Use absolute referencing whenever required. Once you are sure your formula is correct, copy it downwards to cover all customers. Prepare a row that will compute the total for all customers.