Assignments Cellular 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. (1 point) Home Insert Fage Layout Formulas Data Review View Assignme 2- X Caller -11 AA - BIU A- Cand General " 1313 28 ber 3 14 Plan Options Option 1 Pay as you go Calls Minute Charges $0.05 Data Charges per Megabyte 50.03 Text Messages included (no media) 500 Text Message Charges 50.05 Media Message Charges 50.10 Data Used (gigabyte) M 30 Option 2 Rate Plan w/Overages Messaging Monthly Cost $89.00 Text Messages Sent Minutes Included 700 Media Messages Sent Data included tegabytes) 1 Total Messages Sent Messages (any type) Included 1000 Over-minute Charges 50.10 Cellular Taklee Rate 18% Over-data Charges per megabyte 50.05 Over-message Charges per message 50.30 Kilobytes in a Gigabyte Megabytes in a Gigabyte 1048576 3004 Option 3-Unlimited tate $119.00 Budget Cellular Data Messages Usage Summary Weekday Daytime Minutes Evening & Weekend Minutes Total Minutes Used Note: There are 1024 kilobytes megabyte and 1024 megabytes in s pgabyte Therefore there are 104837 Canflow Plan Costs you Option 1-Pay Minute Charges Osta Charges Text Message Charges Media Message Charges Total Charges Taxes and Fees (cell 15 Total Bill Option 2 Rate Plan w/Overages Monthly Cost Over-minute Charges Additional Message Charges Total Changes Taxes and Fees (cell) Total Bill Over-data Charger 1 2 3 File C/Users/k del/Downloads/assignment.html 1 1 Complete the "Weekday/Daytime?" column of the "Calls" worksheet. Each cell in this column should display "Yes" if the call for that row was made on a weekday and during the daytime and "No" otherwise (e.g. cell H3 will display "Yes" if cells F3 and G3 are both "Yes" otherwise it will display "No"). Notice that column F is "Yes" if the call was placed on a weekday and column G is "Yes" if the call occured during the daytime. 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). Use the ROUNDUP function with "Minutes" (column E) as the number argument and 0 for the number of digits. 1 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. * O 4 1 Calculate the "Total Minutes Used" on the Cellular worksheet. This is the sum of all of the "Time Charged" on the Calls worksheet. 5 1 9 Calculate the "Evening & Weekend Minutes" used on the Cellular worksheet. This is the 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 used on the Data worksheet. Notice that that data 1 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. 7 1 8 1 Calculate the "Media Texts Sent" on the Cellular worksheet. This is the count of the messages listed on the Messages worksheet that are listed as "Media" type of message. 9 10 10 - 2 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). Cilebte the Data Charner for option. This is the "Dats Hood (miestne)" time the "Das Charone Manabute" for nation fnation that no 90 1135 Auruments-Lace-UNT C x Assignment File C/Users/kdel/Downloads/assignment.html 11 2 12 3 subject to the charges regardless of when the call is placed). 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). 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 text message charges for option 1. 13 33 2 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. 14 1 Calculate the Total Charges for option 1. This is the sum of all of the different charges for this option 15 1 Calculate the Taxes and Fees for option 1. This is the total charges times the Cellular Tax/Fee Rate. 16 17 99 1 Calculate the Total Bill for option 1. This is the sum of the total charges and the taxes and fees for this option. 2 19 18 19 19 A 4 Calculate the Monthly Cost for option 2. Reference the monthly cost for option 2 in the model inputs, 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 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-Dota 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 message 21 1 Calculate the Total Charges for option 2. This is the sum of all of the different charges for this option 22 22 1 Calculate the Taxes and Fees for option 2. This is the total charges times the Cellular Tax/Fee Rate, 20 20 3 3 12:00 PM sments-Excel-UNT x Assignment File C:/Users/k del/Downloads/assignment.html Calculate the Total Charges for option 1. This is the sum of all of the afferent charges for this option. 14 1 15 1 Calculate the Taxes and Fees for option 1. This is the total charges times the Cellular Tax/Fee Rate. 16 1 Calculate the Total Ball for option 1. This is the sum of the total charges and the taxes and fees for this option. 17 41 2 Calculate the Monthly Cost for option 2. Reference the monthly cost for option 2 in the model inputs. 18 > a 4 4 3 3 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. 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 message. 19 19 a B 21 1 Calculate the Total Charges for option 2. This is the sum of all of the different charges for this option. 2 22 1 Calculate the Taxes and Fees for option 2. This is the total charges times the Cellular Tax/Fee Rate. 23 2 Calculate the Total Bill for option 2. This is the sum of the total charges and the taxes and fees for this option. 24 23 25 2 2 Calculate the Monthly Cost for option 3. Reference the monthly cost for option 3 in the model inputs. Calculate the Total Charges for option 3. This is the sum of all of the different charges for this option. 26 2 Calculate the Taxes and Fees for option 3. This is the total charges times the Cellular Tax/Fee Rate. 27 2 Calculate the Total Bill for option 3. This is the sum of the total charges and the taxes and fees for this option. Total: 45 15 20 20 O 12053 6/20 1 F6 =SUM(Calls!H3:135) D G Plan Options Usage Summary Option 1-Pay-as-you-go Calls Plan Costs. Option 1 Pay-as-you-go 4 Minute Charges 50.05 Weekday Daytime Minutes 65 Minute Charges Data Charges per Megabyte $0.03 Evening & Weekend Minutes 6 Data Charges 6 Text Messages Included (no media) 500 Total Minutes Used 71 Text Message Charges Text Message Charges 50.05 Media Message Charges B Media Message Charges 50.10 Data Used (gigabytes) 9 10 Option 2-Rate Plan w/Overages Messaging 11 Monthly Cost $89.00 Text Messages Sent 12 Minutes Included 700 Media Messages Sent 13 Data included (gigabytes) 1 Total Messages Sent 14 Messages (any type) Included: 1000 15 Over-minute Charges $0.10 Cellular Tax/Fee Rate 16 Over-data Charges per megabyte 50.05 17 Over-message Charges per message $0.10 18 19 Option 1 Unlimited 20 Rate $119.00 21 Budget Cellular Calls Data Messages #Kilobytes in a Gigabyte** #Megabytes in a Gigabyte Note: There are 1024 kilobytes in a megabyte and 1024 megabytes in a gigabyte. Therefore there are 1048576 Cashflow Total Charges Taxes and Fees (cell F15) Total Bill Option 2 Rate Plan w/Overages Monthly Cost Over-minute Charges Additional Message Charges Total Charges Taxes and Fees (cell F15) Total Bill 18% 1048576 1024 Over-data Charges AutoSave Off H advanced Modeling-RO-Kristiana Guzman Search (Alt+Q) File Home Insert Page Layout Formulas Data Review View Help Assignment Assignment Tasks Cellular Kristina De Li Instruction Task Sheet Guide Submit View Report Show Instructions Automatic Scoring F6 A B 10 vixvfx SUM(Calls!H3:135) Option 2-Rate Plan w/Overages: 3 Calculate the "Weekday Daytime Minutes" used on the sum of all of the "Time Charged" for calls that occur on on the Calls worksheet. [1 point] C D Monthly Cost $89.00 2 Minutes Included" 700 Messaging Text Messages Sent Media Messages Sent H Taxes and Fees (cell F15) Total Bill Data Included (gigabytes) 1 Total Messages Sent 14 Messages (any type) Included 1000 15 Over-minute Charges $0.10 Cellular Tax/Fee Rate 18% Option 2-Rate Plan w/Overages Monthly Cost Over-minute Charges 16 Over-data Charges per megabyte $0.05 17 Over-message Charges per message $0.10 #Kilobytes in a Gigabyte #Megabytes in a Gigabyte" 1048576 1024 Over-data Charges** Additional Message Charges 10 19 Option 3 Unlimited 20 Rate 5119.00 21 22 Night and weekend minutes are free for Option 2 23 24 25 27 20 29 30 Note: There are 1024 kilobytes in a megabyte and 1024 megabytes in a gigabyte. Therefore there are 1048576 kilobytes in a gigabyte (1024 x 1024). You are planning 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 calls, data usage, and text message usage from the last month with your previous provider into separate worksheets in the workbook (Calls Data, and Messages, respectively) so that you can how much each plan would cost you based on your usage that month. Complete the tasks to compare your bill for each plan option. Taxes and Fees (cell F15) Total Bill Option 3 Unlimited Monthly Cost Total Charges Taxes and Fees (cell F15) Total Bill Total Charges Budget Cellular Calls Data Ready Accessibility Investigate Messages C Cashflow D