"Times the text message charges for option 1"
Plan Options Usage Summa Plan Costs Option 1 -Pay-as-you-go Minute Charges Calls Option 1- Pay-as-you-go $0.05 Weekday Daytime Minutes 159 Minute Charges $0.03 Evening & Weekend Minutes Data Charges* Text Message Charges Data Charges per Megabyte Text Messages Included (no media) Total Minutes Used 500 175 Text Message Charges $0.05 Media Message Charges Media Message Charges $0.10 Data Used (gigabytes) Total Charges 9 Option 2 -Rate Plan w/Overages Messaging Taxesand Fees (cell F15) 10 Monthly Cost $89.00 Total Bill Text Messages Sent Minutes Included 700 Media Messages Sent Data Included (gigabytes) Total Messages Sent Option 2 - Rate Plan w/Overages Messages (any type) Included 1000 Monthly Cost $0.10 Cellular Tax/Fee Rate Over-minute Charges 18% Over-minute Charges* Over-data Charges** Additional Message Charges $0.05 16 Over-data Charges per megabyte # Kilobytes in a Gigabyte** # Megabytes in a Gigabyte** 1048576 $0.10 Over-message Charges per message 1024 19 Option 3 -Unlimited Total Charges Taxes and Fees (cell F15) Note: There are 1024 kilobytes in a megabyte and 1024 megabytes in a gigabyte. Therefore there are 1048576 kilobytes ina 119,00 Rate Total Bill *Night and weekend minutes are free for Option 2 gigabyte (1024 x 1024). Option 3 -Unlimited Monthly Cost You areplanning to switch your cell phone provider and there are three options available to you. You could choose a pay as-you-go plan, a traditional plan (which includes an allowance for minutes, data, and text and penalties for exceeding these allowances), and an unlimited plan. You want to evaluate which plan will be best for you. You have imported your Total Charges 26 Taxesand Fees (cell F15) Total Bill calls, data usage, and text message usage from the last month with your previous provider into separate worksheets in 29 the workbook (Calls, Data, and Messages, respectively) so that you can how much each plan would cost you based on 30 our usage that month. Complete the tasks to compare your bill for each plan option 36 38 39 40 CashFlow Budget Cellular Calls Data Messages Ready 100% Complete the "Time Charged" column of the "Calls" worksheet. The time charged for each call is the "Minutes" (column E) for that call rounded up to the next minute (e.g. the time charged for a call that lasts 1.01 minutes is 2 minutes) Calculate the "Weekday Daytime Minutes" used on the Cellular worksheet. This is this sum of all of the "Time Charged" for calls that occur on Weekdays during the Daytime on the Calls worksheet. Calculate the "Total Minutes Used" on the Cellular worksheet. This is the sum of all of 4 the "Time Charged" on the Calls worksheet. Calculate the "Evening & Weekend Minutes" used on the Cellular worksheet. This is thee difference between the "Total Minutes Used" and the "Weekday Daytime Minutes" on the Cellular worksheet Calculate the "Data Used" on the Cellular worksheet. This is the sum of all of the data 1 used on the Data worksheet. Notice that that data used on the Data worksheet is in kilobytes. Be sure to express the data used on the Cellular worksheet in gigabytes (divide the sum of the data used on the Data worksheet by the number of kilobytes in a gigabyte found in cell F16.) Calculate the "Texts Sent" on the Cellular worksheet. This is the count of the messages listed on the Messages worksheet that are listed as "Text" type of message Calculate the "Media Texts Sent" on the Cellular worksheet. This is the count of the 8 messages listed on the Messages worksheet that are listed as "Media" type of message Calculate the "Total Messages Sent" on the Cellular worksheet. This is the sum of the "Text" and "Media" messages sent. Calculate the Minute Charges for option 1. This is the "Total Minutes Used" times the "Minute Charges" for option 1 (notice that all calls are subject to the charges regardless of when the call is placed) 10 Calculate the Data Charges for option 1. This is the "Data Used (gigabytes)" times the "Data Charges per Megabyte" for option 1 (notice that the charges will need to be adjusted since the data used is calculated in gigabytes) 2 12 Calculate the Text Message(not media messages - these are billed separately and not included in the "free messages" for option 1) charges for option 1. This is calculated as number of text messages sent that exceed the number included in the plan times the Calculate the Media Message charges for option 1. This is calculated as number of media messages sent times the media message charges for option 1. 13 2 Calculate the Total Charges for option 1. This is the sum of all of the different charges 14 for this option. Calculate the Taxes and Fees for option 1. This is the total charges times the Cellular 15 Tax/Fee Rate Calculate the Total Bill for option 1. This is the sum of the total charges and the taxes and fees for this option 16 Calculate the Monthly Cost for option 2. Reference the monthly cost for option 2 in the model inputs 17 Calculate the Over-minute Charges for option 2. This is the number of minutes used that exceed the minutes included in the plan times the over minute charges. Note: In this plan night and weekend minutes are free 18 4 19 Calculate the Over-data Charges for option 2. This is the amount of data used that exceed the data included in the plan times the Over-Data Charges per megabyte. Make sure that you adjust the over-data charges since the data used is reported in gigabytes Calculate the Additional Message Charges for option 2. This is the number of total messages that exceed the messages included in the plan (notice this plan does not distinguish between text and media messages) times the Over-message Charges per 20 message Calculate the Total Charges for option 2. This is the sum of all of the different charges 21 1 for this option. Calculate the Taxes and Fees for option 2. This is the total charges times the Cellular 1 Tax/Fee Rate. Calculate the Total Bill for option 2. This is the sum of the total charges and the taxes and fees for this option 23 2 Calculate the Monthly Cost for option 3. Reference the monthly cost for option 3 in the model inputs 24 Calculate the Total Charges for option 3. This is the sum of all of the different charges 25 for this option Minutes Weekday? Daytime? Weekday/Daytime? Time Charged Date Monday 1-Oct 11:28:00 AM Yes Yes Monday 1-Oct 12:38:00 PM Monday Monday 1-Oct 1:10:00 PM 0.27 1-Oct 1:16:00 PM Monday 1-Oct 1:20:00 PM 4.98 Monday 1-Oct 2:41:00 PM Monday 1-Oct 2:49:00 PM Monday 1-Oct 3:33:00 PM 0.25 Monday 1-ct 3:37:00 PM 0.08 Monday 1-Oct3:41:00 PM Monday Monday 1-Oct 4:12:00 PM 1-Oct5:04:00 PM 0.36 Monday 1-Oct 5:08:00 PM Monday 1-Oct5:20:00 PM Monday Monday 1-Oct 6:30:00 PM 3.45 1-Oct 7:39:00 PM Monday 1-Oct 8:35:00 PM 0.05 Monday 1-Oct 8:38:00 PM Tuesday 2-Oct 10:00:00 AM Tuesday 2-Oct 10:31:00 AM 0.83 Tuesday 2-Oct 10:41:00 AM 0.36 2-Oct 10:45:00 AM Tuesday Tuesday Tuesday 2-Oct 11:56:00 AM 2-Oct 12:25:00 PM Tuesday 2-Oct 12:53:00 PM Tuesday Tuesday 2-Oct1:02:00 PM Yes 2-Oct1:48:00 PM Tuesday Tuesday 2-Oct 2:39:00 PM 2-Oct 3:49:00 PM 1.00 Tuesday 2-Oct 4:03:00 PM 0.82 4.00 Tuesday 2-Oct 5:33:00 PM Tuesday 2-Oct 5:54:00 PM Tuesday 2-Oct 6:38:00 PM 0.34 Tuesday 2-Oct7:09:00 PM 0.65 Tuesday Tuesday Tuesday 2-Oct7:33:00 PM 2-Oct 8:23:00 PM 2-Oct 9:11:00 PM Tuesday 2-Oct9:15:00 PM Tuesday 2-Oct 9:30:00 PM 1 nn CashFlow Budget Cellular Calls Data Messages Ready -+ 100% 0 4 0 334 8 2 2432443 5 2055 :2 3 5 1 3 0 0 0 3 2 2 3 3 4 2 1 5 1 4 0 4 1 1 1 2 1 5 0 1 2 5 3 1 1 1 6 68304586893525518890370661178 1234567890123 012345678901234567890123456789017 1111111112222222222333333333344 Date Time 1-Oct 7:33Text 1-Oct 8:41Text 1-Oct 9:01Text 1-Oct 9:19 Text 1-Oct 9:36 Text 1-Oct 9:39 Text 1-Oct 9:45 Text 1-Oct 10:03 Text 1-Oct 10:27 Text 1-Oct 10:51 Text 1-Oct 10:53 Text 1-Oct 10:59 Text 1-Oct 11:03Text 11:42 Media 1-Oct 1-Oct 11:48Text 1-Oct 12:19 Text 1-Oct 12:23 Media 1-Oct 12:37Text 1-Oct 12:50Text 1-Oct 13:16Text 1-Oct 14:15 Media 1-Oct 14:55 Text 1-Oct 15:28 Text 1-Oct 16:26 Text 1-Oct 16:37 Text 1-Oct 16:58Text 1-Oct 17:13Text 1-Oct 17:19Text 18:36 Text 1-Oct 1-Oct 19:35Text 1-Oct 19:37Text 1-Oct 20:04 Text 1-Oct 20:52 Text 36 1-Oct 21:05 Text 1-Oct 21:40 Text 1-Oct 22:23 Text 1-Oct 23:03 Text 0:16 Media 1-Oct 1-Oct 1:25Text -17Medi Budget Cellular Calls Data CashFlow Messages Ready + 100% Plan Options Usage Summa Plan Costs Option 1 -Pay-as-you-go Minute Charges Calls Option 1- Pay-as-you-go $0.05 Weekday Daytime Minutes 159 Minute Charges $0.03 Evening & Weekend Minutes Data Charges* Text Message Charges Data Charges per Megabyte Text Messages Included (no media) Total Minutes Used 500 175 Text Message Charges $0.05 Media Message Charges Media Message Charges $0.10 Data Used (gigabytes) Total Charges 9 Option 2 -Rate Plan w/Overages Messaging Taxesand Fees (cell F15) 10 Monthly Cost $89.00 Total Bill Text Messages Sent Minutes Included 700 Media Messages Sent Data Included (gigabytes) Total Messages Sent Option 2 - Rate Plan w/Overages Messages (any type) Included 1000 Monthly Cost $0.10 Cellular Tax/Fee Rate Over-minute Charges 18% Over-minute Charges* Over-data Charges** Additional Message Charges $0.05 16 Over-data Charges per megabyte # Kilobytes in a Gigabyte** # Megabytes in a Gigabyte** 1048576 $0.10 Over-message Charges per message 1024 19 Option 3 -Unlimited Total Charges Taxes and Fees (cell F15) Note: There are 1024 kilobytes in a megabyte and 1024 megabytes in a gigabyte. Therefore there are 1048576 kilobytes ina 119,00 Rate Total Bill *Night and weekend minutes are free for Option 2 gigabyte (1024 x 1024). Option 3 -Unlimited Monthly Cost You areplanning to switch your cell phone provider and there are three options available to you. You could choose a pay as-you-go plan, a traditional plan (which includes an allowance for minutes, data, and text and penalties for exceeding these allowances), and an unlimited plan. You want to evaluate which plan will be best for you. You have imported your Total Charges 26 Taxesand Fees (cell F15) Total Bill calls, data usage, and text message usage from the last month with your previous provider into separate worksheets in 29 the workbook (Calls, Data, and Messages, respectively) so that you can how much each plan would cost you based on 30 our usage that month. Complete the tasks to compare your bill for each plan option 36 38 39 40 CashFlow Budget Cellular Calls Data Messages Ready 100% Complete the "Time Charged" column of the "Calls" worksheet. The time charged for each call is the "Minutes" (column E) for that call rounded up to the next minute (e.g. the time charged for a call that lasts 1.01 minutes is 2 minutes) Calculate the "Weekday Daytime Minutes" used on the Cellular worksheet. This is this sum of all of the "Time Charged" for calls that occur on Weekdays during the Daytime on the Calls worksheet. Calculate the "Total Minutes Used" on the Cellular worksheet. This is the sum of all of 4 the "Time Charged" on the Calls worksheet. Calculate the "Evening & Weekend Minutes" used on the Cellular worksheet. This is thee difference between the "Total Minutes Used" and the "Weekday Daytime Minutes" on the Cellular worksheet Calculate the "Data Used" on the Cellular worksheet. This is the sum of all of the data 1 used on the Data worksheet. Notice that that data used on the Data worksheet is in kilobytes. Be sure to express the data used on the Cellular worksheet in gigabytes (divide the sum of the data used on the Data worksheet by the number of kilobytes in a gigabyte found in cell F16.) Calculate the "Texts Sent" on the Cellular worksheet. This is the count of the messages listed on the Messages worksheet that are listed as "Text" type of message Calculate the "Media Texts Sent" on the Cellular worksheet. This is the count of the 8 messages listed on the Messages worksheet that are listed as "Media" type of message Calculate the "Total Messages Sent" on the Cellular worksheet. This is the sum of the "Text" and "Media" messages sent. Calculate the Minute Charges for option 1. This is the "Total Minutes Used" times the "Minute Charges" for option 1 (notice that all calls are subject to the charges regardless of when the call is placed) 10 Calculate the Data Charges for option 1. This is the "Data Used (gigabytes)" times the "Data Charges per Megabyte" for option 1 (notice that the charges will need to be adjusted since the data used is calculated in gigabytes) 2 12 Calculate the Text Message(not media messages - these are billed separately and not included in the "free messages" for option 1) charges for option 1. This is calculated as number of text messages sent that exceed the number included in the plan times the Calculate the Media Message charges for option 1. This is calculated as number of media messages sent times the media message charges for option 1. 13 2 Calculate the Total Charges for option 1. This is the sum of all of the different charges 14 for this option. Calculate the Taxes and Fees for option 1. This is the total charges times the Cellular 15 Tax/Fee Rate Calculate the Total Bill for option 1. This is the sum of the total charges and the taxes and fees for this option 16 Calculate the Monthly Cost for option 2. Reference the monthly cost for option 2 in the model inputs 17 Calculate the Over-minute Charges for option 2. This is the number of minutes used that exceed the minutes included in the plan times the over minute charges. Note: In this plan night and weekend minutes are free 18 4 19 Calculate the Over-data Charges for option 2. This is the amount of data used that exceed the data included in the plan times the Over-Data Charges per megabyte. Make sure that you adjust the over-data charges since the data used is reported in gigabytes Calculate the Additional Message Charges for option 2. This is the number of total messages that exceed the messages included in the plan (notice this plan does not distinguish between text and media messages) times the Over-message Charges per 20 message Calculate the Total Charges for option 2. This is the sum of all of the different charges 21 1 for this option. Calculate the Taxes and Fees for option 2. This is the total charges times the Cellular 1 Tax/Fee Rate. Calculate the Total Bill for option 2. This is the sum of the total charges and the taxes and fees for this option 23 2 Calculate the Monthly Cost for option 3. Reference the monthly cost for option 3 in the model inputs 24 Calculate the Total Charges for option 3. This is the sum of all of the different charges 25 for this option Minutes Weekday? Daytime? Weekday/Daytime? Time Charged Date Monday 1-Oct 11:28:00 AM Yes Yes Monday 1-Oct 12:38:00 PM Monday Monday 1-Oct 1:10:00 PM 0.27 1-Oct 1:16:00 PM Monday 1-Oct 1:20:00 PM 4.98 Monday 1-Oct 2:41:00 PM Monday 1-Oct 2:49:00 PM Monday 1-Oct 3:33:00 PM 0.25 Monday 1-ct 3:37:00 PM 0.08 Monday 1-Oct3:41:00 PM Monday Monday 1-Oct 4:12:00 PM 1-Oct5:04:00 PM 0.36 Monday 1-Oct 5:08:00 PM Monday 1-Oct5:20:00 PM Monday Monday 1-Oct 6:30:00 PM 3.45 1-Oct 7:39:00 PM Monday 1-Oct 8:35:00 PM 0.05 Monday 1-Oct 8:38:00 PM Tuesday 2-Oct 10:00:00 AM Tuesday 2-Oct 10:31:00 AM 0.83 Tuesday 2-Oct 10:41:00 AM 0.36 2-Oct 10:45:00 AM Tuesday Tuesday Tuesday 2-Oct 11:56:00 AM 2-Oct 12:25:00 PM Tuesday 2-Oct 12:53:00 PM Tuesday Tuesday 2-Oct1:02:00 PM Yes 2-Oct1:48:00 PM Tuesday Tuesday 2-Oct 2:39:00 PM 2-Oct 3:49:00 PM 1.00 Tuesday 2-Oct 4:03:00 PM 0.82 4.00 Tuesday 2-Oct 5:33:00 PM Tuesday 2-Oct 5:54:00 PM Tuesday 2-Oct 6:38:00 PM 0.34 Tuesday 2-Oct7:09:00 PM 0.65 Tuesday Tuesday Tuesday 2-Oct7:33:00 PM 2-Oct 8:23:00 PM 2-Oct 9:11:00 PM Tuesday 2-Oct9:15:00 PM Tuesday 2-Oct 9:30:00 PM 1 nn CashFlow Budget Cellular Calls Data Messages Ready -+ 100% 0 4 0 334 8 2 2432443 5 2055 :2 3 5 1 3 0 0 0 3 2 2 3 3 4 2 1 5 1 4 0 4 1 1 1 2 1 5 0 1 2 5 3 1 1 1 6 68304586893525518890370661178 1234567890123 012345678901234567890123456789017 1111111112222222222333333333344 Date Time 1-Oct 7:33Text 1-Oct 8:41Text 1-Oct 9:01Text 1-Oct 9:19 Text 1-Oct 9:36 Text 1-Oct 9:39 Text 1-Oct 9:45 Text 1-Oct 10:03 Text 1-Oct 10:27 Text 1-Oct 10:51 Text 1-Oct 10:53 Text 1-Oct 10:59 Text 1-Oct 11:03Text 11:42 Media 1-Oct 1-Oct 11:48Text 1-Oct 12:19 Text 1-Oct 12:23 Media 1-Oct 12:37Text 1-Oct 12:50Text 1-Oct 13:16Text 1-Oct 14:15 Media 1-Oct 14:55 Text 1-Oct 15:28 Text 1-Oct 16:26 Text 1-Oct 16:37 Text 1-Oct 16:58Text 1-Oct 17:13Text 1-Oct 17:19Text 18:36 Text 1-Oct 1-Oct 19:35Text 1-Oct 19:37Text 1-Oct 20:04 Text 1-Oct 20:52 Text 36 1-Oct 21:05 Text 1-Oct 21:40 Text 1-Oct 22:23 Text 1-Oct 23:03 Text 0:16 Media 1-Oct 1-Oct 1:25Text -17Medi Budget Cellular Calls Data CashFlow Messages Ready + 100%
