America is awash in natural gas, but in parts of the country theres hardly a drop to burn. 1

Janus Utilities Incorporated (JU Inc.) supplies natural gas to commercial customers. One geographic service area (Area 12) is located close to a prolific gas field. Another geographic service area (Area 23) is isolated from this source. Historically, JU Inc. has set two different prices for each service area:

MMBtu = one million British Thermal Units

Previously, JU Inc.s price for Area 12 was $5.28 per MMBtu; its cost was $3.79 per MMBtu.

Previously, JU Inc.s price for Area 23 was $6.09 per MMBtu; its cost was $4.59 per MMBtu.

JU Inc. bills each month.

In Area 12, the cost per MMBtu now dropped substantially down to $2.64 and is forecasted to remain at this cost due to prolific supply. In Area 23, the cost per MMBtu is forecasted to remain the same.

JU Inc. is proposing to set the same price for both geographic areas (ie. 12 & 23) to promote consistent demand across regions. JU Inc.s proposal includes setting a baseline consumption for its commercial customers of 300 MMBtu per square foot per month. Usage that does not exceed the baseline calculation will be charged $4.75 per MMBtu for the month; any usage exceeding the baseline

calculation will be charged $4.25 per MMBtu for the month.

You must prepare an analysis of how each customers monthly charges will change under the pricing proposal. Ideally, profitability will remain the same.

Create a spreadsheet that shows the monthly charges under the previous monthly price and cost, the proposed monthly price and updated cost, the dollar amount of change with the proposed pricing, and the percent of change with the proposed pricing. Use the data in the spreadsheet.

Be sure to follow the spreadsheet guidance provided in the last two lectures.

One hint: the baseline calculation = (Square Foot Commercial Space) * (Baseline per sq. ft. per month)

Please complete the table using an if formula where possible with reference to values (ie. not embedding static numbers). Create a graph as well to compare the percent change for each customer. It will provide JU Inc. a good visual of how much the proposal will impact each customer. Use customers as the X axis (horizontal) and percentages as the Y axis (vertical).

Lastly, Interpret the results. Does this make sense for JU Inc.? Why or why not?

Customer Name Geographi c Area Square Foot Commercial Space MMBtu used per Month Previous Monthly Previous Monthly Total Price Total Cost Baseline calculation Proposed Monthly Updated Monthly Changed in Total Price Total Cost Amount Billed % Changed in Amount Billed 12 Baxter Mfg. Company Lloyd's Laundry Davis Metal Inc. Finest Foods Corp Builders Block Co. unds Adams Toy Mfg. Norris Milling Co. Webster Windows Central Paper Co. A-1 Rubber Prods. Winco Fabricators Landes Mfg.Inc. TOTAL 23 23 12 23 12 * 12 23 12 12 23 23 5000 2000 10000 5000 5500 17500 20000 7500 12500 15500 400000 34000 2,233,333 156,333 893,333 670,000 790,000 3,126,667 0 3,573,333 1,005,000 2,791,667 2,077,000 26.800.000 3,126,669 current cost updated cost Geographic Area current price 12 23 MMBtu Baseline proposed price per sq. ft. per