Project 1

Today we’re going to be going into an analysis of a problem with a real world context that can be solved using technology, specifically by using spreadsheets. The first portion of this analysis will cover an in depth solution guide for the problem, including justifications for answers, and insights learned along the way. The second portion of this project (link is provided at the bottom of the page) will then cover the problem from the perspective of a teacher, showing the standards that compose this problem and how this activity can be used to promote student learning. So with all of that out of way, lets dive into the problem itself!

The Problem: The problem we’ll be focusing on is a continuation of another problem from the same packet. Information from the earlier problem is vital in order to answer questions posed by the problem we’re focusing on, so both problem statements will be included for reference.

Problem 1 : “You walk into the new Herizon Wireless store, which just opened at the mall. They offer two different plans for data (the voice and text plans are separate):
- The Pay-Go Plan costs $30 per GB but has no monthly charge.
- The Select Plan has a monthly fee of $100 but you only pay $15 per GB.
- A) Which types of customers should choose which plan?
- B) Suppose they introduce a new Unlimited Plan, which costs $240 per month for as much data as you wish to use. How do your answers change?”
Problem 2: “Herizon Wireless is very concerned. Its biggest competitor, Q-Mobile, just introduced a new plan: Their monthly fee is $125, but that includes the first 5 GB of data! Beyond 5 GB, each GB costs $20.
- A) How does Q-Mobile’s pricing compare to Herizon’s? Does Herizon need to be concerned?
- B) How might Herizon adjust its existing three plans so that they are more competitive with Q-Mobile, but still as profitable as possible?

Since problem 2 builds off of the data found in problem 1, let us begin by modeling what the different plans will look like in our spreadsheet. We’ll start by modeling the Pay-Go Plan.

The Pay-Go Plan states that it costs $30 per GB used but has no monthly charge. What this means is that after a customer who subscribes to this plan uses an entire GB of data, they are charged $30. There being no monthly charge means that there is no constant fee being added to the customer’s bill that does not change, regardless of how many GB they used during that month.

To turn that description into a formula we can use in our spreadsheet, we have to pull information from the description and attribute a value or variable to it. For example, in the description of the Pay-Go Plan, it states that it costs $30 per GB used. This can be represented mathematically by the expression 30x, where x is the number of GB used that month. For the second part of the description, it talks about there not being a constant monthly charge being added to your bill. This can be represented as a constant that you add to the 30x we found previously. In this case, since the problem states that there is no monthly charge, the constant would be zero. Putting the two figures together, we can model the Pay-Go Plan algebraically by having the expression 30x+0.

To apply this expression to our spreadsheet we have to first be able to make a column expressing how many GB’s are being used. This way, we can input it into our payment plans in order to model them appropriately. To do this, let’s go step by step for how to set up this column.

First, highlight and then delete column A, eliminating the grey cells and making it visually consistent with the rest of the spreadsheet.
Double-click or double-tap the first cell in your first column, A2. Then enter in the initial value that you want for your sequence into A2. For this case in modeling the number of GB’s used, enter in zero.
Next, double-click the cell right below A2, cell A3. Then look at the top right of the text bar that appears, where there is an equals sign. Double-click this equals sign.
Then enter in “A2+1” and tap the green check mark to save your changes to the cell.
Finally, find the cell actions button and click it. You should see your chosen cell, A2, highlighted. Drag the bottom line of your highlighted cell down to cell A22. This should give you a sequence that starts at 0 and ends with 20.

What you just did was create a recursive sequence of integers that models the amount of GB being used in problem 1. What recursion means is that to compute the value of a cell, say A3, we have to go back to the previous cell and retrieve it’s value. So to compute the value of A3, the spreadsheet app had to go to cell A2, see the 0 we put there as our initial value, and then add 1 to it in order to compute the intended value of A3, one. In the above picture, the recursive sequence you just created is referenced as the column titled, “GB of Data Used.”

Now that we have a column for the number of GBs used, we can use it to compute how much our Pay-Go Plan will cost, per GB used. But before that, we have to program in the expression we found earlier into our spreadsheet, similar to how we just made a sequence for our “GB of Data Used” column.

In our problem, this “GB of Data Used” column will serve as our our x values for all of the payment plans that we’re going to model. This is because in each payment plan, there is a constant monthly fee and a fee per GB used. The constant monthly fee will be a constant figure that is added on to each of our plans. For example, in the Pay-Go Plan, our constant monthly fee is 0. But while the constant fee is of course constant, the fee that comes for each GB we use changes depending on how many GBs we buy for that month. Since it’s a changing value for how many GBs we use, we can represent it with x, our variable. Therefore, whenever our expressions have an x in them, we’re going to substitute it with a value from our “GB of Data Used” column.

This leads us into how we’re going to set up our sequence to model the Pay-Go Plan. Similar to how we set up the “GB of Data Used” column, we’re going to do it step by step.

Double-click on the first cell in column B.
Type in “30 * A2 + 0” to represent the expression we found earlier. Then click the green check mark to save your changes to the cell.
Then find the button that says “Cell Actions” and click it. Finally, similar to the first column, find the auto-fill option and drag cell B2 all the way down to B22. Your column B should then match the second column of figure 1, labeled “Herizon Wireless: Pay-Go.”

Something you should notice by comparing the formulas for your first column and your second column is that your second column is not recursive. In the formula for your first column, we referenced previous cells to compute the value for other cells. However, in our second column formula, we did not refer to any previous cells in the same column. Instead, we used cells from our first column in order to compute our values for our second column. This kind of formula is called a closed formula because it does not have to refer to it’s own cells in order to compute a value.

Now that we’ve modeled the first phone plan, let’s continue and model the other two plans detailed in problem 1!

The Select Plan states that it has a monthly fee of $100 but that you only have to pay $15 per GB used. Similar to how we found the expression representing the Pay-Go Plan, we can dissect the description in order to craft our formula for the Select Plan. To start, lets look at the monthly fee described in the plan. While in the Pay-Go Plan there was no constant monthly charge being added to our bill, in the Select Plan there is a constant monthly charge of $100. Therefore we can go ahead and add a $100 constant to our expression, so that we just have “$100” so far. Next, we can look at the fee per GB used. Similar to the last plan, every time a GB is used, a fee is added onto your bill. Since the charge to our bill is dependent on how many GBs we use, we can add a variable “x” to our expression to represent how many GB we use in a given month. Then, since it’s a $15 fee per GB we use, we can multiply each GB we use, x, by our $15 fee so that every time we add another GB to our bill, we are charged another $15. So now we have a completed expression , “100+15x”, that models the Select Plan. After substituting the x in the expression for the “GB of Data Used” values that we found earlier, we can create a cell in our spreadsheet and fill down in order to see the sequence our formula makes. See below in figure 3 for an example of what your model of the Select Plan should look like

Finally, from the first problem we have the Unlimited Plan. In the problem, it states that the Unlimited Plan is a new plan that has a $240 monthly fee per month with no extra fee for each GB used.

Something that should immediately stick out to you with this plan is that the only fee you have at the end of the month is the $240 monthly fee. This means that regardless of whether you use 2GB or 200GB, no extra charge will added to your bill. This is fortunate for us in that it makes creating an expression to model the Unlimited Plan extremely simple. Just like in our last two plans, we can represent the monthly fee as a flat constant that is being added to our bill, regardless of how many GB we end up using. In this case, our monthly fee comes out to be $240. And since we have no fee per GB we use, this $240 is the only thing we need to include in our expression. So, just like the other columns, type in our expression of “240” into the first cell of our new column and auto fill all the cells down to D22. What our sequence comes out to be should be no surprise in that they’re all the same number, 240. This is because our bill is never increasing since we are not being charged for each GB used, meaning that besides the original $240 fee, we pay nothing extra. To see an example of what this should look like in our spreadsheet, refer below to figure 4.

Finally, with all three of the original phone plans successfully modeled in our spreadsheet, we can begin answering questions from our intended problem. Before diving into the details of our new problem, make sure that your spreadsheet looks like the above figure.

The first portion of problem 2 states that, “Herizon Wireless is very concerned. Its biggest competitor, Q-Mobile, just introduced a new plan: Their monthly fee is $125, but that includes the first 5 GB of data! Beyond 5 GB, each GB costs $20.” To simplify things, we’ll be referring to this new plan as Q-Mobile.

To model this new plan, let’s analyze each part of the description and form an appropriate formula. Like other plans before it, the Q-Mobile plan consists of a constant monthly fee and a fee per GB used. However, unlike the other plans, the Q-Mobile plan has a deal where the first 5GB of data is free, meaning that you pay no fee for using that GB of data. To model this condition, we’ll have to use the insights we learned from both the Select Plan and the Unlimited Plan. In the Select Plan we learned how to use the plan’s monthly fee and fee per GB used in order to form an expression that we can model. In the Unlimited Plan, we did something similar in that we made a formula that included the monthly fee but not with a fee per GB used. So, in order to model the Q-Mobile plan, we’ll be using our previous two expressions and combining them in one column in order to model the new plan.

Let’s first tackle the problem of how to model the free 5 GB that the problem describes in the new phone plan.

Based on our previous work for building expressions for these phone plans, we can use two different formulas to properly model the Q-Mobile plan.
The first expression is to model the plan before the 5 free GB are used and the second expression is to model what happens after the 5 initial GB are used.
To find the first expression, let’s analyze what we know about the Q-Mobile plan’s pricing.
We know that a) The monthly fee for the plan is $125 and b) That the price per GB after the initial five, is $20 per GB.
So, if we ignore the free first 5 GB, our expression would look like this, “125 + 20x”. With the constant 125 coming from our flat $125 monthly fee and the 20x coming from our fee per GB used of $20.
However, since we’re trying to model the 5 free GB that the plan describes, we can have x be multiplied by zero. This is because the first 5 GB are given to us for free, meaning that instead of paying the regular $20 free, you pay $0.
That means that our expression changes to “125 + 0x”, which we can simplify to be , “125 +0”, since zero multiplied by any number is 0.
Therefore, we’re left with the expression, “125” to model the first 5 GB of the Q-Mobile plan. To add this to our spreadsheets, we do the same thing that we did for the Unlimited Plan in that we fill in 125 to the first cell and autofill down to the row that contains a value of 5 for the column “GB of Data Used.”

Your spreadsheet for the first 6 cells in your Q-Mobile column should all say 125.

Now that we have the first expression entered into our spreadsheet, we can work through to find how to model the pricing of the Q-Mobile plan after 5 GBs are used.
Earlier, while we were figuring out what our first expression would be, we found that the general formula for this plan would be, “125+20x.”
However, before we input this into our spreadsheet, we have to modify it to avoid a common misconception.
If we just typed in, “125 +20x” into our spreadsheet we would get a misleading answer. Since this new expression would start when the number of GBs used is 6, it would compute “245”, which is incorrect. It would be incorrect since at 6 GBs used, we’re really only paying for 1 GB worth of data. This is because we received the first 5 GBs for free. Therefore, at 6 GBs of data used, the first GB we have to pay for is the 6th one. So, rather than start at the first GB like we normally, we shift our first paid GB down on the “GB of Data Used” column to 6. As a result, our formula needs to be changed in order to reflect this shifting of the column.
Since the first paid GB is shifted up from GB number 1 to GB number 6, we see that there’s a difference of 5. So, in order to change our formula accordingly, we can subtract 5 from our x before it is multiplied by our fee per GB used. After this change, your expression should look like “125 +(20*(x-5)).”
By doing this, when we input 6 for our x as the first paid GB, we’ll be subtracting 5, making it so that x is equal to 1, reflecting our intentions for 6 to be the first GB that we pay for. “125+(20*(6-5))” = “125+(20*1).”

After inputting this second expression into the 8th cell of your Q-Mobile column, fill down to the bottom using the autofill button. Afterwards your spreadsheet should look like figure 6.

Now that we finally have all of our phone plans properly modeled into our spreadsheet, we can start answering the questions posed by problem 2, starting with part a. At this point, your spreadsheet should look something like this.

Problem 2, Part a:

Part a asks,”How does Q-Mobile’s pricing compare to Herizon’s? Does Herizon need to be concerned?”

An easy way to do this is to look at the different prices for each of the plans for each GB used. Take the row where there is zero GBs being used for example. We can see from this row that in terms of pricing, Herizon’s Pay-Go Plan is the cheapest if you do not use any GBs of data. With the Pay-Go Plan, at zero GBs used, you pay $0 if you subscribe to the plan. In comparison, we can see from the spreadsheet that Herizon’s other plans, as well as Q-Mobile’s plan is far more expensive if you use no GBs of data, with the Unlimited Plan being the most expensive.

Now, we can repeat this process for every row, until we reach the end of our spreadsheet at 20 GBs of data used. In order to visually see the cheapest option for each row, I suggest coloring the cell a different color as I have below.

From this figure, we can visually see that that Herizon by far has the most cost efficient data plans. Out of 20 prices for each plan for each GB used, we can see that Herizon has the most cost-effective plans at 12 different GB intervals, which means that Herizon holds 60% of the most efficient plans, giving them a majority share of the market (assuming customers always choose the most cost-efficient plan for each individual GB sold).

To answer the problem directly, Q-Mobile’s prices are higher than Herizon’s outside of the interval of 5 GBs to 10 GBs, making them a less attractive option compared to those given by Herizon. As a result, I do not believe that Herizon should be concerned with Q-Mobile’s new data plan, due to the fact that Herizon should hold a majority share of the data plan market with their more cost-efficient prices.

Problem 2, Part b:

The second part of problem 2 asks, “How might Herizon adjust its existing three plans so that they are more competitive with Q-Mobile, but still as profitable as possible?”

What the problem is asking us here is how can we take our existing plans and modify them so that Herizon can maximize profit while still undercutting the prices of Q-Mobile, effectively making their data plan unattractive compared to those offered by Herizon.

Looking back at figure 8, we can see that 2 of Herizon’s plans, specifically the Pay-Go Plan and the Unlimited Plan both are cost-efficient for their own range of GBs used. However, the Select Plan is found to not be cost-efficient for any value for GBs used, which shows that it’s pricing needs to be adjusted in order to stay competitive with Q-Mobile’s.

To do this, we need to play around with the formula, “100+15x”, that we found for pricing the Select Plan. The easiest way to accomplish this is to compare the values of the Select Plan column to those found in the Q-Mobile column. The ranges that we especially want to compare are highlighted for clarity below.

Comparing the red shaded region to the green we can clearly see that the prices are way too high for the Select Plan when contrasted against those in the Q-Mobile plan. Something to note is that since the Select Plan has it’s fee per GB used set to $15, it’s prices per GB used is going higher in a linear, constant fashion. So, if we want to keep the prices per GB the same but also lower our prices, we have to lower the constant monthly fee. If we were graphing our formula for the Select Plan, this would be the equivalent of keeping our slope the same but lowering the y-intercept of the graph.

As we can see in figure 10, by lowering the monthly fee by half to $50, we have lowered our prices by just enough to undercut those offered by Q-Mobile while also maximizing Herizon’s profit. To get to $50 as the Select Plan’s monthly fee, we can start by our original monthly fee of $100 and subtracting by $10 to see how it affects our sequence. The goal being to get the updated Select Plan prices as close to the ones in the Q-Mobile column without being higher than them. By having the Select Plan prices be slightly lower than those in the Q-Mobile column, it makes the Select Plan be the new cost-efficient plan for the range that the Q-Mobile plan previously occupied.

Visually we can see this change by looking at figure 8 but with updated prices for the Select Plan.

To directly answer problem 2, part b, we changed the existing Select Plan which was not the most cost-efficient in it’s range by lowering the constant monthly value which maximized our profits by keeping the new prices as close as possible to those of Q-Mobile.

Conclusion:

In conclusion, we learned in this problem that by having different constant values and different rates of linear growth, we can get very different sequences. We also discovered how to be able to maximize a sequence in order to have the greatest profits while also being the most cost-efficient. By making our own formulas to model each data plan, we were able to develop a deep understanding of how to make linear expressions that model a real-world situation. Having this understanding made it possible for us answer compare and contrast questions like those found in problem 2, in addition to being able to modify our existing expressions in order to fit different criteria.

To continue to dive into this problem from the perspective of a teacher, please visit this URL in order to view the teacher discussion!

Share this: