Phone Plan Analysis

You are planning to switch your cell phone provider. You have imported your data usage from the last six months with your previous provider and entered this information on the "Data" worksheet so that you can analyze which plan is best for you based on your prior data usage.$$

The Cellular worksheet presents two options for a cell phone plan with the new company. You could choose a pay$-$as$-$you$-$go plan or an unlimited plan. You want to evaluate which plan will be best for you. Both plans provide unlimited calling and texting. The unlimited plan also offers unlimited data usage. The pay$-$as$-$you$-$go plan includes $12$ gigabytes of data. Any data you use beyond the $12$ gigabytes will cost $$15$ per gigabyte.$$

Complete the tasks to compare what your bill would be for both plan options based on the past data usage.

$3\mathrm{.}1$

Complete the Megabytes Used row $($G$4$:L$4)$ of the Usage Summary table on the Data worksheet.

Build the formula to calculate total Megabytes Used for January $($G$4),$ by referencing the month name in$$G$3$and the appropriate columns in the data table $($B$3$:D$183).$

Reuse your formula to calculate the Megabytes Used for the other months.

Notice that the "Gigabytes Used" $($G$5$:L$5)$ row is already completed in the worksheet. "Gigabytes Used" is calculated as the "Megabytes Used" divided by $1024($the number of megabytes in a gigabyte$).$

The Gigabytes Used $($Rounded$)($G$6$:L$6)$ row is also completed in the worksheet. Since the mobile carrier rounds the Gigabytes Used up to the next whole gigabyte, the ROUNDUP function is used to adjust the Gigabytes Used up to the next integer.

$6$

$3\mathrm{.}2$

Complete the Monthly Charges row $($F$5$:K$5)$ of the Cost Comparison table on the Cellular worksheet for Option $1.$

Enter the monthly charges for January $($F$5)$ by referenceing the monthly charges for option $1($C$4)$ in the Plan Options table.$$

Reuse your formula to complete the monthly charges for Option $1$ for each month.

$5$

$3\mathrm{.}3$

Complete the Taxes and Fees row $($F$6$:K$6)$ of the Cost Comparison table on the Cellular worksheet for Option $1.$

Calculate the tax for January in cell$$F$6.$

The taxes and fees are calculated as the monthly charges in cell$$F$5($not including any data charges$)$ times the taxes and fees rate $($cell$$C$12)$ on the Plan Options table.

Reuse your formula to complete the taxes and fees for Option $1$ for each month.

$6$

$3\mathrm{.}4$

Complete the Gigabytes Used $($Rounded$)$ row $($F$7$:K$7)$ of the Cost Comparison table on the Cellular worksheet for Option $1.$ The phone carrier rounds the data used up to the nearest gigabyte for billing purposes.

Enter the rounded number of gigabytes used for January in cell$$F$7$by referencing its calculated value in the appropriate cell on the Data sheet.$$

Reuse your formula to complete the Gigabytes Used $($Rounded$)$ for Option $1$ for the remaining months.

$5$

$3\mathrm{.}5$

Complete the Data Charges row $($F$8$:K$8)$ of the Cost Comparison table on the Cellular worksheet for Option $1.$

Calculate the data charges for option $1$ in January in cell$$F$8.$

The phone carrier charges $$15$ for each gigabyte used $($rounded up to the next whole gigabyte$)$ that exceeds the data amount included with the plan.

Be sure to reference the Gigabytes Used $($Rounded$)$ in January as well as Data Charges per Gigabyte and Gigabytes Included from the Plan Options section of the worksheet.

Display a zero if the data does not exceed the gigabytes included with the plan.

Reuse your formula to complete the data charges for Option $1$ for each month.

$8$

$3\mathrm{.}6$

Complete the Total Cost row $($F$9$:K$9)$ of the Cost Comparison table on the Cellular worksheet for Option $1.$

Calculate the total cost for option $1$ in January in cell$$F$9.$

The total cost is the sum of the monthly charges, taxes and fees, and the data charges for January.

Reuse your formula to complete the total cost for Option $1$ for each month.

$6$

$3\mathrm{.}7$

Complete the Which is best? row $($F$17$:L$17)$ of the Cost Comparison table on the Cellular worksheet.

Notice that the costs associated with Option $2$ are already calculated in the worksheet.

Write a formula to display "Option $1"$ if the total cost of Option $1$ is less than the total cost of Option $2$ for that month.

If the total cost of Option $2$ is less than the total cost of Option $1,$ display "Option $2".$

Otherwise, display $"$No Difference".

Reuse your formula to determine which option is best for each month and in cell$$L$17$to determine which option is best overall. F$17$

$\backslash$begin$\{$tabular$\}\{|$c$|$c$|$c$|$c$|\}$

$\backslash$hline & A & B & C $\backslash \backslash$

$\backslash$hline $1$ & & & $\backslash \backslash$

$\backslash$hline $2$ & & $\backslash$multicolumn$\{2\}\{|$l$|\}\{$Plan Options$\}\backslash \backslash$

$\backslash$hline $3$ & & Option $1-$ Pay$-$as$-$you$-$go & $\backslash \backslash$

$\backslash$hline $4$ & & Monthly Charges & $\backslash$$$40\mathrm{.}00\backslash \backslash$

$\backslash$hline $5$ & & Data Charges per Gigabyte & $\backslash$$$15\mathrm{.}00\backslash \backslash$

$\backslash$hline $6$ & & Gigabytes Included & $12\backslash \backslash$

$\backslash$hline $7$ & & & $\backslash \backslash$

$\backslash$hline $8$ & & Option $2-$ Unlimited Plan & $\backslash \backslash$

$\backslash$hline $9$ & & Monthly Cost & $\backslash$$$70\mathrm{.}00\backslash \backslash$

$\backslash$hline $10$ & & & $\backslash \backslash$

$\backslash$hline $11$ & & Taxes and Fees & $\backslash \backslash$

$\backslash$hline $12$ & & Rate & $18\backslash \%\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$