Using Scenarios and Data Tables to Support Pricing Decisions The Istanbul Water Company (ISKI) has used...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Using Scenarios and Data Tables to Support Pricing Decisions The Istanbul Water Company (ISKI) 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 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. 6 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. For the current rate structure (bree rate 50.00 / gallon, cutoff at 100,000 gallon, and $ 0.07 / gallo thereafter) Enter the data into the worksheet (0.09, 0.07, 100000) in B20.B22. Develop the fomulls that will compute the amount due by a customer, referring to the cells that contain the data. Enter the formula for Baxter in 02. Always use cell references in your formula favold 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 wow 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 strueture that will meet the objectives stated in the case. At this point, you may find it useful to create new column that compute tbe clifferences between the current rate structure and proposed rate structure. Write the best price structure you found at this stage in box A Using Scenarios: A Scenario for us in this example ofits of linee "variables the base rate, the cutofl amount. and the tiened rate, as these are the things under our control. We can define scenaris in Excel pointing cul cells that are allowed to take different values and helps formalize our what - if analysis A resnio is identified by a scenario name that you specify. 1. Under Data memu go to What If analysis, and then choose Scenario Mannger. Choose Adki 2 Call the scenario you are about to define " current " Onder changing cells, give the address of the thired cells that you have used to experiment in the previous exercise (for base rate, tiered rate and citott value) 3. Eoter the default values for these cells 14. Create a new smarles, for instance ' high 'that has a rate of SD. 10/gallon, a cate amount of 50.000 gallons and is tied rate of $ 0.12 / gallon 6. Under Sonario Nanager.cco a scenario and click on Display No that scenarios are permanent unless you delete them. This gives you a more systematic way of doing what if analysis compared to frasfom tria lanchetron You can also onerate summary reports of scontos !reports of scenarios. Using Data Tables : Another systematic way of analyzing the effects of a change in one or two variables in using data tables 1. Develop a one - variable data table that studies the effect of the change in the cutoff amount (sallons) on the change in the amount billed for all customers. We will develop tumw-oriented one varinble data table starting at cell 11. Formulas and in a che variable data table most refer to an input cell. Our input cell will be the cell that both the clitol at B22) 1. Enter a list of five distinct vadiles you want to try for cutoff value acos 14 row. starting at ISO 100,00, 100,000.:) 2. Typ the formula that competes Baxtar's bill all the column to the left of the first value and one I below the row of values (12) When entering the formula is the cell address that houses the original cutoll value (B22) Enter or copy) the formulas for the other companies in the first format 3. Select the range of cells that contains the formules and values you want to substitute. 11 ... N13.2 on the Date menu fiad What I Amlysis and choose Data Table In the Rowlinpus bell toxicoter B22 Click OnLOR Now di computed a table that lists die effects of changes in cut-off value on He is onssamer. Write graph explanation to indicate which pricing structure is mon LTSR box B. Company Baxter Mining Co. Lloyd's Laundry Davis Metal Inc. Finest Foods Corp. Builders Block Co. Adams Toy Mfg. Norris Milling Co. Webster Windows Central Paper Co. A-1 Rubber Prods. Winco Fabricators Landes Mfg. Inc. VARIABLES base rate tiered rate cutoff value Volume Billed Amount 182755 618324 76382 127391 268310 47383 57293 94383 139726 312094 37290 93271 A. Write the best price structure you found by trial and error below. Your Answer: 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: Using Scenarios and Data Tables to Support Pricing Decisions The Istanbul Water Company (ISKI) 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 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. 6 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. For the current rate structure (bree rate 50.00 / gallon, cutoff at 100,000 gallon, and $ 0.07 / gallo thereafter) Enter the data into the worksheet (0.09, 0.07, 100000) in B20.B22. Develop the fomulls that will compute the amount due by a customer, referring to the cells that contain the data. Enter the formula for Baxter in 02. Always use cell references in your formula favold 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 wow 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 strueture that will meet the objectives stated in the case. At this point, you may find it useful to create new column that compute tbe clifferences between the current rate structure and proposed rate structure. Write the best price structure you found at this stage in box A Using Scenarios: A Scenario for us in this example ofits of linee "variables the base rate, the cutofl amount. and the tiened rate, as these are the things under our control. We can define scenaris in Excel pointing cul cells that are allowed to take different values and helps formalize our what - if analysis A resnio is identified by a scenario name that you specify. 1. Under Data memu go to What If analysis, and then choose Scenario Mannger. Choose Adki 2 Call the scenario you are about to define " current " Onder changing cells, give the address of the thired cells that you have used to experiment in the previous exercise (for base rate, tiered rate and citott value) 3. Eoter the default values for these cells 14. Create a new smarles, for instance ' high 'that has a rate of SD. 10/gallon, a cate amount of 50.000 gallons and is tied rate of $ 0.12 / gallon 6. Under Sonario Nanager.cco a scenario and click on Display No that scenarios are permanent unless you delete them. This gives you a more systematic way of doing what if analysis compared to frasfom tria lanchetron You can also onerate summary reports of scontos !reports of scenarios. Using Data Tables : Another systematic way of analyzing the effects of a change in one or two variables in using data tables 1. Develop a one - variable data table that studies the effect of the change in the cutoff amount (sallons) on the change in the amount billed for all customers. We will develop tumw-oriented one varinble data table starting at cell 11. Formulas and in a che variable data table most refer to an input cell. Our input cell will be the cell that both the clitol at B22) 1. Enter a list of five distinct vadiles you want to try for cutoff value acos 14 row. starting at ISO 100,00, 100,000.:) 2. Typ the formula that competes Baxtar's bill all the column to the left of the first value and one I below the row of values (12) When entering the formula is the cell address that houses the original cutoll value (B22) Enter or copy) the formulas for the other companies in the first format 3. Select the range of cells that contains the formules and values you want to substitute. 11 ... N13.2 on the Date menu fiad What I Amlysis and choose Data Table In the Rowlinpus bell toxicoter B22 Click OnLOR Now di computed a table that lists die effects of changes in cut-off value on He is onssamer. Write graph explanation to indicate which pricing structure is mon LTSR box B. Company Baxter Mining Co. Lloyd's Laundry Davis Metal Inc. Finest Foods Corp. Builders Block Co. Adams Toy Mfg. Norris Milling Co. Webster Windows Central Paper Co. A-1 Rubber Prods. Winco Fabricators Landes Mfg. Inc. VARIABLES base rate tiered rate cutoff value Volume Billed Amount 182755 618324 76382 127391 268310 47383 57293 94383 139726 312094 37290 93271 A. Write the best price structure you found by trial and error below. Your Answer: 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:
Expert Answer:
Related Book For
Management Accounting Information for Decision-Making and Strategy Execution
ISBN: 978-0137024971
6th Edition
Authors: Anthony A. Atkinson, Robert S. Kaplan, Ella Mae Matsumura, S. Mark Young
Posted Date:
Students also viewed these accounting questions
-
A consumer organization has reported test data for 50 car models. We will examine the association between the weight of the car (in thousands of pounds) and the fuel efficiency (in miles per gallon)....
-
Using thermodynamic data for water from Tables B.1.1 and B.1.5, estimate the freezing temperature of liquid water at a pressure of 30 MPa.
-
Using thermodynamic data for water from Tables C.8.1 and C.8.3, estimate the freezing temperature of liquid water at a pressure of 5000 lbf/in 2.
-
Why does Sam Goldman go out of his way to talk about the importance of d.lights business plan? In what ways do you think having a meticulously crafted business plan helped d.light in its launch...
-
A small furnace uses 3.00 kW of power. If the cost of operation of the furnace is $3.84 for a 24.0-h period, what is the cost of energy per kWh?
-
In space, points of the form (x, y, 0) lie in: xy-plane (a) the (c) the yz-plane (b) the xz-plane (d) none of these
-
Which two types of intellectual property potentially last indefinitely? For the remaining two types that do not last indefinitely, how long do they last? Which types of intellectual property, if any,...
-
A stream of Liquid n-pentane flows at a rate of 50.4L/min into a heating chamber, where it evaporates into a stream of air 15% in excess of the amount needed to burn the pentane completely. The...
-
11.A simple LR circuit is connected to a battery at t = 0. The time instant at which rate of energy storage in inductor is half of power delivered by battery 2L (1) In 2 (3) In 2 (2) In (4) (4) In 3
-
It is October 16, 2020, and you have just taken over the accounting work of China Moon Products, whose annual accounting period ends October 31. The company?s previous accountant journalized its...
-
The cost of preferred stock equals its required rate of return, which is its _______ divided by its _________. ANSWER Unselected current market price; quarterly dividend Unselected annual dividend;...
-
Bill was born in 1978. He is married to Hazel who was born in 1982. Neither of them is a Scottish taxpayer. In 2017-18, Bill had business profits of 47,550 and received dividends of 5,200. Hazel...
-
Andrew owns a house which he lets to tenants. Rent is payable quarterly in advance on 1 January, 1 April, 1 July and 1 October. The rent was 8,000 per annum until it was increased to 8,800 per annum...
-
Which of the following expenses incurred by an employee would be deductible when computing employment income for tax purposes? (a) Travel between work and home. (b) Travel between work sites. (c)...
-
Greta com mences trading on 1 January 2016 and chooses 30 June as her annual accounting date. Her first accounts are made up f or the 18 mont hs to 30 June 2017 and show an adjusted trading profit of...
-
At what rate of income tax is each of the following types of payment relieved in tax year 2017-18? (a) interest on a qualifying home loan used to buy an annuity (b) interest on a loan used to buy an...
-
The pretax financial income (or loss) figures for Blue Company are as follows. 2021 $360,000 2022 2023 (225,200) 2024 (202,000) 2025 117,600 2026 162,000 122,000 Pretax financial income (or loss) and...
-
Experiment: Tossing four coins Event: Getting three heads Identify the sample space of the probability experiment and determine the number of outcomes in the event. Draw a tree diagram when...
-
Describe the flow of costs from raw materials to cost of goods sold in a manufacturing organization.
-
What are some different managerial uses of cost information?
-
What are some special considerations in the design of cost accounting systems for service organizations?
-
Consider the logistic map discussed in the text. To gauge the density of bifurcations, one uses a measure of distance between fixed points as follows. Define \(d=x^{*}-(1 / 2)\) as the distance...
-
Consider a particle in a Newtonian potential \(V(r)=-k / r+\epsilon / r^{n}\) for some integer \(n\). Using the alternate variable \(u=1 / r\), (a) show that the radial equation of motion can be put...
-
Consider the equation of motion \[\frac{d^{2} u}{d \varphi^{2}}+u-\frac{1}{p}=3 \lambda u^{2}\] where \(p\) and \(\lambda\) are constants. Find the solution using perturbation theory to first order...
Study smarter with the SolutionInn App