Below Excel File:gg While Answering write down formula you used to solve question. Thanks Using Scenarios and
Fantastic news! We've Found the answer you've been seeking!
Question:
Below Excel File:gg
While Answering write down formula you used to solve question. Thanks
Transcribed Image Text:
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. 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.
Expert Answer:
Answer rating: 100% (QA)
Solution From the given case we have identified three critical scenarios for ISKI Current Base and Tier Rate Structure with Cutoff Value Reverse Base ... View the full answer
Related Book For
Smith and Roberson Business Law
ISBN: 978-0538473637
15th Edition
Authors: Richard A. Mann, Barry S. Roberts
Posted Date:
Students also viewed these accounting questions
-
Since the 1950s, Qualitex Company has used a special shade of green-gold color on the pads that it makes and sells to dry cleaning firms for use on dry cleaning presses. In 1989 Jacobson Products (a...
-
. ABC Company has used a two-stage, reciprocal method cost allocation system for many years. In the first stage, overhead costs from its two service departments (repair and administration) are...
-
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.
-
Explain why the economic order quantity is the intersection of the ordering-cost curve and the carrying-cost curve.
-
A square wood column with side dimensions b (see figure) is constructed of a structural grade of southern pine for which Fc = 10.5 MPa and E = 12 GPa. An axial force P = 200 kN acts on the column....
-
Presented below are two independent cases related to held-for-collection and selling debt investments. For each case, determine the amount of impairment loss, if any, and the journal entry to record...
-
Below is a frequency distribution table for a hypothetical variable: a. How many of the scores for this variable have the value 2? b. What percentage of the scores has the value of 7 ? Value f% 7 5...
-
Bower Corporation reported the following information: common stock, $1 par; 50,000 shares authorized; 35,000 shares issued and outstanding. Required: 1. What is the appropriate journal entry to...
-
Your company, TS&S Inc., has decided to enter the seafood industry, and is considering buying FreshFish, Inc., a procurer and distributor of fresh seafood, located in Cabo San Lucas, Mexico.FreshFish...
-
In Figure 6.5, what rate of output (a) Maximizes total revenue? (b) Maximizes profit per unit? (c) Maximizes total profit? (Choose the higher level of output.) $18 Marginal cost 16 p = MC Profits...
-
Break down what this code means in sections. import java.io. ; import java.util. ; public class ReadFile{ public static void main(String[] args) throws NoSuchElementException { try { File myObj =...
-
15 years ago, Tom Inc issued zero-coupon bonds with a maturity date of 30 years and a face value of $1,000. Presently, the bond exhibits a semi-annually compounded yield to maturity of 11.58 percent....
-
Marc Corporation recently released zero-coupon bonds with a face value of $1,000. The bond has a duration of 22 years and a yield to maturity of 7.78 percent, with annual compounding. What is the...
-
Credit cards can be powerful financial tools when used responsibly. They offer convenience, flexibility, and various benefits such as rewards programs, cashback offers, and fraud protection. However,...
-
John Wood, Inc. has recently issued zero-coupon bonds at a par value of $1,000. The aforementioned bond possesses an 8-year term to maturity, alongside a semi-annual compounded yield to maturity of...
-
In late 2 0 X 1 , Smart Co . recorded $ 1 0 , 0 0 0 salary of lab technicians who spent 6 0 % of their time in Smart's own R&D projects and 4 0 % of their time in R&D projects that Smart performed...
-
A review of the ledger of Sheffield Corporation at its year end, July 31, 2024, produces the following unadjusted data for the preparation of annual adjusting entries: 1. Prepaid Insurance, July 31,...
-
What is the shape of the exponential distribution?
-
Civil Code 1719, subdivision (a) provides in part that any person who draws a check that is dishonored due to insufficient funds shall be liable to the payee for the amount owing upon the check and...
-
A privately owned business in a developing country determines that current computer technology could solve many of the problems faced by its countrys private and public sectors. This business,...
-
John Swan rented a safe deposit box at the Tenth Citizens Bank of Emanon, State of X. On December 17, 2008, Swan went to the bank with stock certificates to place in the safe deposit box. After he...
-
Find the conditional distribution of happiness by marital status for the data in Table 9. Then draw a bar graph that represents the conditional distribution of happiness by marital status. Approach...
-
One growing concern regarding the U.S. economy is the inequality in the distribution of income. The data in Table 1 represent the distribution of household income for various levels of income in...
-
One growing concern regarding the U.S. economy is the inequality in the distribution of income. An economist wants to know if the distribution of income is changing, so she randomly selects 1500...
Study smarter with the SolutionInn App