Below Excel File 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 gallon 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 3 Repeat this procedure with a rate structure that you propose By trial and error, try to find a rate structure that will meet the objectives stated in the case At this point, you may find it useful to create a new column that computes the differences between the current rate structure and proposed rate structure Write the best price structure you found at this stage in box A 1 Using Scenarios A Scenario for us in this example consists of three variables the base rate, the cutoff amount, and the tiered rate, as these are the things under our control We can define scenarios in Excel by pointing out cells that are allowed to take different values and helps formalize our what if analysis A scenario is identified by a scenario name that you specify 1 Under Data menu, go to What If analysis, and then choose Scenario Manager Choose Add 2 Call the scenario you are about to define current Under changing cells, give the addresses of the three cells that you have used to experiment in the previous exercise (for base rate, tiered rate and cutoff value) 3 Enter the default values for these cells 4 Create a new scenario, for instance high' that has a base rate of $0 10 gallon, a cutoff amount of 50,000 gallons and a tiered rate of $0 12 gallon 5 Under Scenario Manager, choose a scenario and click on Display Note that scenarios are permanent unless you delete them This gives you a more systematic way of doing what if analysis compared to free form trial and error You can also generate summary reports of scenarios Using Data Tables Another systematic way of analyzing the effects of a change in one or two variables is using data tables 1 Develop a one variable data table that studies the effect of the change in the cutoff amount (gallons) on the change in the amount billed for all customers We will develop a row oriented one variable data table, starting at cell I1 Formulas used in a one variable data table must refer to an input cell Our input cell will be the cell that houses the cutoff amount (B22) 1 Enter a list of five distinct values you want to try for cutoff value across one row, starting at J1 (e g 80,000 100,000, 120,000 ) 2 Type the formula that computes Baxter's bill in the column to the left of the first value and one cell below the row of values (12) When entering the formula, use the cell address that houses the original cutoff value (B22) Enter (or copy) the formulas for the other companies below the first formula 3 Select the range of cells that contains the formulas and values you want to substitute, 11 N13 4 On the Data menu, find What If Analysis and choose Data Table 5 In the Row input cell box, enter B22 6 Click on OK Now, you have automatically computed a table that lists the effects of changes in cut off value on the bills of customers Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI in box B Company Mining Co Laundry Metal Inc Foods Corp Block Co Toy Mfg Milling Co Windows Paper Co Prods Fabricators Mfg Inc Volume Billed Amount 182755 618324 76382 127391 268310 47383 57293 94383 139726 312094 37290 93271 VARIABLES base rate tiered rate cutoff value A Write the best price structure you found by trial and error below B Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI Which analysis helps you to come up with this price structure Your Answer Your Answer 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 gallon 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 3 Repeat this procedure with a rate structure that you propose By trial and error, try to find a rate structure that will meet the objectives stated in the case At this point, you may find it useful to create a new column that computes the differences between the current rate structure and proposed rate structure Write the best price structure you found at this stage in box A 1 Using Scenarios A Scenario for us in this example consists of three variables the base rate, the cutoff amount, and the tiered rate, as these are the things under our control We can define scenarios in Excel by pointing out cells that are allowed to take different values and helps formalize our what if analysis A scenario is identified by a scenario name that you specify 1 Under Data menu, go to What If analysis, and then choose Scenario Manager Choose Add 2 Call the scenario you are about to define current Under changing cells, give the addresses of the three cells that you have used to experiment in the previous exercise (for base rate, tiered rate and cutoff value) 3 Enter the default values for these cells 4 Create a new scenario, for instance high' that has a base rate of $0 10 gallon, a cutoff amount of 50,000 gallons and a tiered rate of $0 12 gallon 5 Under Scenario Manager, choose a scenario and click on Display Note that scenarios are permanent unless you delete them This gives you a more systematic way of doing what if analysis compared to free form trial and error You can also generate summary reports of scenarios Using Data Tables Another systematic way of analyzing the effects of a change in one or two variables is using data tables 1 Develop a one variable data table that studies the effect of the change in the cutoff amount (gallons) on the change in the amount billed for all customers We will develop a row oriented one variable data table, starting at cell I1 Formulas used in a one variable data table must refer to an input cell Our input cell will be the cell that houses the cutoff amount (B22) 1 Enter a list of five distinct values you want to try for cutoff value across one row, starting at J1 (e g 80,000 100,000, 120,000 ) 2 Type the formula that computes Baxter's bill in the column to the left of the first value and one cell below the row of values (12) When entering the formula, use the cell address that houses the original cutoff value (B22) Enter (or copy) the formulas for the other companies below the first formula 3 Select the range of cells that contains the formulas and values you want to substitute, 11 N13 4 On the Data menu, find What If Analysis and choose Data Table 5 In the Row input cell box, enter B22 6 Click on OK Now, you have automatically computed a table that lists the effects of changes in cut off value on the bills of customers Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI in box B Company Mining Co Laundry Metal Inc Foods Corp Block Co Toy Mfg Milling Co Windows Paper Co Prods Fabricators Mfg Inc Volume Billed Amount 182755 618324 76382 127391 268310 47383 57293 94383 139726 312094 37290 93271 VARIABLES base rate tiered rate cutoff value A Write the best price structure you found by trial and error below B Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI Which analysis helps you to come up with this price structure Your Answer Your

Question

Below Excel File  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 gallon 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  3  Repeat this procedure with a rate structure that you propose  By trial and error, try to find a rate structure that will meet the objectives stated in the case  At this point, you may find it useful to create a new column that computes the differences between the current rate structure and proposed rate structure  Write the best price structure you found at this stage in box A  1 Using Scenarios  A Scenario for us in this example consists of three  variables   the base rate, the cutoff amount, and the tiered rate, as these are the things under our control  We can define scenarios in Excel by pointing out cells that are allowed to take different values and helps formalize our what if analysis  A scenario is identified by a scenario name that you specify  1  Under Data menu, go to What If analysis, and then choose Scenario Manager  Choose  Add   2  Call the scenario you are about to define  current   Under changing cells, give the addresses of the three cells that you have used to experiment in the previous exercise (for base rate, tiered rate and cutoff value)  3  Enter the default values for these cells  4  Create a new scenario, for instance  high' that has a base rate of $0 10 gallon, a cutoff amount of 50,000 gallons and a tiered rate of $0 12 gallon  5  Under Scenario Manager, choose a scenario and click on Display  Note that scenarios are permanent unless you delete them  This gives you a more systematic way of doing what if analysis compared to free form trial and error  You can also generate summary reports of scenarios  Using Data Tables  Another systematic way of analyzing the effects of a change in one or two variables is using data tables  1  Develop a one variable data table that studies the effect of the change in the cutoff amount (gallons) on the change in the amount billed for all customers  We will develop a row oriented one variable data table, starting at cell I1  Formulas used in a one variable data table must refer to an input cell  Our input cell will be the cell that houses the cutoff amount (B22)  1  Enter a list of five distinct values you want to try for cutoff value across one row, starting at J1 (e g 80,000  100,000, 120,000   )  2  Type the formula that computes Baxter's bill in the column to the left of the first value and one cell below the row of values (12)  When entering the formula, use the cell address that houses the original cutoff value (B22)  Enter (or copy) the formulas for the other companies below the first formula  3  Select the range of cells that contains the formulas and values you want to substitute, 11   N13  4  On the Data menu, find What If Analysis and choose Data Table  5  In the Row input cell box, enter B22  6  Click on OK Now, you have automatically computed a table that lists the effects of changes in cut off value on the bills of customers  Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI in box B  Company Mining Co  Laundry Metal Inc  Foods Corp  Block Co  Toy Mfg  Milling Co  Windows Paper Co  Prods  Fabricators Mfg Inc  Volume Billed Amount 182755 618324 76382 127391 268310 47383 57293 94383 139726 312094 37290 93271 VARIABLES base rate tiered rate cutoff value A  Write the best price structure you found by trial and error below  B  Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI  Which analysis helps you to come up with this price structure  Your Answer  Your Answer  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 gallon 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  3  Repeat this procedure with a rate structure that you propose  By trial and error, try to find a rate structure that will meet the objectives stated in the case  At this point, you may find it useful to create a new column that computes the differences between the current rate structure and proposed rate structure  Write the best price structure you found at this stage in box A  1 Using Scenarios  A Scenario for us in this example consists of three  variables   the base rate, the cutoff amount, and the tiered rate, as these are the things under our control  We can define scenarios in Excel by pointing out cells that are allowed to take different values and helps formalize our what if analysis  A scenario is identified by a scenario name that you specify  1  Under Data menu, go to What If analysis, and then choose Scenario Manager  Choose  Add   2  Call the scenario you are about to define  current   Under changing cells, give the addresses of the three cells that you have used to experiment in the previous exercise (for base rate, tiered rate and cutoff value)  3  Enter the default values for these cells  4  Create a new scenario, for instance  high' that has a base rate of $0 10 gallon, a cutoff amount of 50,000 gallons and a tiered rate of $0 12 gallon  5  Under Scenario Manager, choose a scenario and click on Display  Note that scenarios are permanent unless you delete them  This gives you a more systematic way of doing what if analysis compared to free form trial and error  You can also generate summary reports of scenarios  Using Data Tables  Another systematic way of analyzing the effects of a change in one or two variables is using data tables  1  Develop a one variable data table that studies the effect of the change in the cutoff amount (gallons) on the change in the amount billed for all customers  We will develop a row oriented one variable data table, starting at cell I1  Formulas used in a one variable data table must refer to an input cell  Our input cell will be the cell that houses the cutoff amount (B22)  1  Enter a list of five distinct values you want to try for cutoff value across one row, starting at J1 (e g 80,000  100,000, 120,000   )  2  Type the formula that computes Baxter's bill in the column to the left of the first value and one cell below the row of values (12)  When entering the formula, use the cell address that houses the original cutoff value (B22)  Enter (or copy) the formulas for the other companies below the first formula  3  Select the range of cells that contains the formulas and values you want to substitute, 11   N13  4  On the Data menu, find What If Analysis and choose Data Table  5  In the Row input cell box, enter B22  6  Click on OK Now, you have automatically computed a table that lists the effects of changes in cut off value on the bills of customers  Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI in box B  Company Mining Co  Laundry Metal Inc  Foods Corp  Block Co  Toy Mfg  Milling Co  Windows Paper Co  Prods  Fabricators Mfg Inc  Volume Billed Amount 182755 618324 76382 127391 268310 47383 57293 94383 139726 312094 37290 93271 VARIABLES base rate tiered rate cutoff value A  Write the best price structure you found by trial and error below  B  Write a one paragraph explanation to indicate which pricing structure is more appropriate for ISKI  Which analysis helps you to come up with this price structure  Your Answer  Your

Accepted Answer

The Answer is in the image, click to view ...

Question

Below Excel File: While Answering write down formula you used to solve question. Thanks Using Scenarios and Data Tables to Support Pricing Decisions Water Company

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Bookkeeping And Auditing A Career Guide

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question