PLEASE SHOW THE FORMULA

Task # Points Task Description 1 3 Create a named range called, "Income" that references the cell that calculates the total income in your budget. N 3 Create a named range called, "Expenses" that references the cell that calculates the total expenses in your budget. 3 4 Create a named range called, "Savings" that references the cell that calculates the monthly savings in your budget. The savings calculation should be the difference between the total income and total expenses in your budget. Be sure to reference the named ranges for income and expenses in your calculations. Total: 10 Task # Points Task Description 1 3 Calculate the total cash inflows for the six months. The total cash inflows are sum of the cash sales and customer payments. N 3 Calculate the total expenses for the six months. The total expenses are sum of all of the expenses incurred for each the month in which they are incurred. 3 3 Calculate the net cash flows for the six months. The net cash flows are calculated as the difference between the total cash inflows and the total expenses for each month. 4 3 Calculate the Shortfall/Surplus Cash for the six months. The shortfall/surplus cash is the beginning cash balance plus the net cash flows for each month. 5 6 Calculate the additional cash needed for the six months. When there is a cash shortfall (the shortfall/surplus cash calculation is less than zero), additional cash must be obtained from a loan to cover all of the monthly expenses. Therefore, additional cash needed is equal to any cash shortfall. It is zero if there is a cash surplus. The additional cash needed should be a positive number even though a shortfall will be a negative number in the model (row 26). 6 6 Calculate the cash available to retire debt for each of the six months. There is cash available to retire debt if there is a cash surplus at the end of a month (shortfall/surplus cash > 0). Therefore cash available to retire debt is equal to any cash surplus at the end of a month. If there is not a surplus, cash available to retire debt is 0. 7 6 Calculate the cash used to retire debt for each of the six months. Since the company can't pay down more debt than there is cash available to retire the debt, the cash used to retire debt cannot exceed the cash available to retire debt. Likewise, the company won't pay more money to retire debt than it has debt from the prior month to retire. Therefore, the cash used to retire debt will be equal to the cash available to retire debt if that total is less than the loan balance from the prior month. Otherwise, the cash used to retire debt will be the loan total from the prior month. 8 3 Calculate the loan balance for each of the six months. The loan balance is calculated as the additional cash needed for a given month minus any cash used to retire debt for that month plus the loan balance carried from the prior month. 9 3 Calculate the ending cash balance for each month. The ending cash balance is equal to the cash available to retire debt minus any cash used to retire debt. 10 3 Calculate the beginning cash balance for February through June. The beginning cash balance is equal to the ending cash balance from the prior month. 11 3 3 Calculate the loan interest for each month. The loan interest is equal to the loan balance for that month times the monthly interest rate in cell C3. This will create a circular reference. Configure Excel to appropriately handle the circular reference. 12 3 Calculate the line of credit needed. The line of credit needed is the credit limit the company needs to negotiate with the bank. It is calculated as the maximum loan balance that the company expects to carry over the course of the six month period. 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. N 1 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 O for the number of digits. 3 3 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. 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 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. 6 1 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 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.) 7 1 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. 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 1 Calculate the "Total Messages Sent" on the Cellular worksheet. This is the sum of the "Text" and "Media" messages sent. 10 2 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). 11 2 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) 12 3 3 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 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 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 1 Calculate the Total Bill for option 1. This is the sum of the total charges and the taxes and fees for this option. 17 2 Calculate the Monthly Cost for option 2. Reference the monthly cost for option 2 in the model inputs. 18 4 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. 19 3 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. 20 3 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 1 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 2 Calculate the Monthly Cost for option 3. Reference the monthly cost for option 3 in the model inputs. 25 2 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 2 Calculate the Total Bill for option 3. This is the sum of the total charges and the taxes and fees for this option. A B D E F . L M N P R S 1 2 3 4 5 Prepare a personal budget for one month. Be sure to think through your sources of income and expenses. Be as detailed as you wish; but include cells that calculate the following: Income (which calculates the sum of all income sources), Expenses (which calculates the sum of all expenses), and Savings (which calculates the difference between the total income and expenses). After completing your budget, complete the tasks to create named ranges that reference the important cells outlined above. 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Budget Cellular Calls Data Messages CashFlow # B H 1 2 Plan Costs 3 4 $0.05 Plan Options Option 1 - Pay-as-you-go Minute Charges Data Charges per Megabyte Text Messages Included (no media) Text Message Charges Media Message Charges Usage Summary Calls Weekday Daytime Minutes Evening & Weekend Minutes Total Minutes Used 5 $0.03 Option 1 - Pay-as-you-go Minute Charges Data Charges** Text Message Charges Media Message Charges 6 500 7 $0.05 8 $0.10 Data Used (gigabytes) 9 10 Total Charges Taxes and Fees (cell F15) Total Bill 11 $89.00 Messaging Text Messages Sent Media Messages Sent Total Messages Sent 12 700 13 1 Option 2 - Rate Plan w/Overages Monthly Cost Minutes Included* Data Included (gigabytes) Messages (any type) Included Over-minute Charges Over-data Charges per megabyte Over-message Charges per message 14 1000 15 $0.10 18% Option 2 - Rate Plan w/Overages Monthly Cost Over-minute Charges* Over-data Charges** Additional Message Charges 16 Cellular Tax/Fee Rate # Kilobytes in a Gigabyte** # Megabytes in a Gigabyte** $0.05 1048576 17 $0.10 1024 18 19 Option 3 - Unlimited Rate 20 $119.00 ** 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). Total Charges Taxes and Fees (cell F15) Total Bill 21 22 * Night and weekend minutes are free for Option 2 23 Option 3 - Unlimited Monthly Cost 24 25 26 27 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. Total Charges Taxes and Fees (cell F15) Total Bill 28 29 30 31 32 B D E F G H 1 2 Minutes Weekday? Daytime? Weekday/Daytime? Time Charged 3 1.62 Yes Yes 4 2.24 Yes Yes 5 Date Time 01-Oct 2! 11:28:00 01-Oct 2 12:38:00 01-Oct 1:10:00 01-Oct 1:16:00 01-Oct 1:20:00 01-Oct 2:41:00 0.46 Yes Yes 6 0.27 Yes Yes 7 4.98 Yes Yes 8 0.44 Yes Yes 9 01-Oct 2:49:00 1.57 Yes Yes 10 01-Oct 3:33:00 0.25 Yes Yes 11 01-Oct 0.08 Yes Yes 12 01-Oct 2.07 Yes Yes 13 01-Oct 1.30 Yes Yes 14 01-Oct 0.36 Yes Yes 15 Day Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Tuesday Tuesday Tuesday Tuesday Tuesday Tuesday Tuesday Tuesday 01-Oct 0.72 Yes Yes 3:37:00 3:41:00 4:12:00 5:04:00 5:08:00 5:20:00 6:30:00) 7:39:00 8:35:00 8:38:00 16 01-Oct 3.50 Yes Yes 17 01-Oct 3.45 Yes Yes 18 01-Oct 1.29 Yes No 19 01-Oct 0.05 Yes No 20 01-Oct 2.75 Yes No 21 02-Oct 210:00:00 2.58 Yes Yes 22 02-Oct 2! 10:31:00 0.83 Yes Yes 23 0.36 Yes Yes 24 2.18 Yes Yes 02-Oct 21 10:41:00 02-Oct 21 10:45:00 02-Oct 27 11:56:00 02-Oct 2 12:25:00 25 1.29 Yes Yes 26 1.51 Yes Yes 27 02-Oct 2= 12:53:00 0.14 Yes Yes 28 02-Oct 1.02.00 . 118 Yes Yes A B C D E 1 2 2 Date Time Data Used (Kb) 3 3 01-Oct 10:28 1068 4 01-Oct 11:05 60 5 01-Oct 11:55 2061 6 01-Oct 12:53 273 7 01-Oct 13:26 2027 01-Oct 13:32 63 8 9 9 01-Oct 13:54 4 10 01-Oct 14:19 21 11 01-Oct 14:38 62 12 01-Oct 15:04 1181 13 01-Oct 16:06 90 14 01-Oct 16:08 46 15 01-Oct 16:33 476 16 6 01-Oct 17:20 88 17 01-Oct 17:24 1414 18 8 01-Oct 18:35 365 19 01-Oct 18:38 2311 20 01-Oct 18:46 34 21 02-Oct 11:28 2040 22 02-Oct 12:19 4104 23 02-Oct 12:53 3069 24 02-Oct 13:15 2361 25 02-Oct 13:42 4071 26 02-Oct 14:05 4038 D B C D E F 1 2 Date Time Type 3 01-Oct 7:33 Text 4 01-Oct 8:41 Text 5 01-Oct 9:01 Text 6 01-Oct 9:19 Text 7 01-Oct 9:36 Text 8 01-Oct 9:39 Text 9 01-Oct 9:45 Text 10 01-Oct 10:03 Text 11 01-Oct 10:27 Text 12 01-Oct 10:51 Text 13 3 01-Oct 10:53 Text 14 01-Oct 10:59 Text 15 5 5 01-Oct 11:03 Text 16 01-Oct 11:42 Media 17 01-Oct 11:48 Text 18 01-Oct 12:19 Text 19 01-Oct 12:23 Media 20 01-Oct 12:37 Text 21 01-Oct 12:50 Text 22 01-Oct 13:16 Text 23 01-Oct 14:15 Media 24 01-Oct 14:55 Text 25 01-Oct 15:28 Text 26 01-Oct 16:26 Text 27 01-Oct 16:37 Text 28 Text 01-Oct 16:58 Budget Cellular Calls Data Messages CashFl B H K L M N O PQ 2 Cash Flow Budget Worksheet 1.5% 3 Monthly Interest Rate Complete the statement of cash flow for January through June by completing the tasks. As part of the process you will need to use a circular reference. You will need to set Excel to handle circular references. 5 Line of Credit Needed 6 7 December February March April May June January $5,000.00 8 Beginning Cash Balance 9 10 11 Cash Inflows (Income): Customer Payments Cash Sales Total Cash Inflows $35,000.00 $45,000.00 $53,000.00 $64,680.00 $76,540.00 $90,540.00 $52,000.00 $62,000.00 $73,500.00 $86,000.00 $100,600.00 $117,700.00 12 13 14 15 16 $20,000.00 $30,000.00 $35,000.00 $40,000.00 $50,000.00 $60,000.00 17 18 $4,500.00 19 Cash Outflows (Expenses): Advertising Loan Interest Insurance Inventory Purchases Office Supplies Payroll Rent or Lease Utilities & Telephone Total Expenses 20 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $46,000.00 $53,000.00 $54,500.00 $59,800.00 $71,900.00 $3,200.00 $3,200.00 $3,200.00 $3,500.00 $4,000.00 $11,000.00 $12,000.00 $13,500.00 $18,100.00 $17,700.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $2,700.00 $3,000.00 $3,100.00 $3,200.00 $3,500.00 21 $83,300.00 $4,000.00 $23,200.00 $12,000.00 $5,000.00 22 23 24 25 26 Cash Flows (Net): 27 28 Shortfall/Surplus Cash $6000.00+ 29 30 31 $0.00 32 Loan Calculatations Additional Cash Needed Cash Available to Retire Debt Cash Used to Retire Debt Loan Balance** 33 $6,000.00 $1,000.00 $0.00 34 35 36 Ending Cash Balance $5,000.00 37 38 39 40 41 * Note: there was a loan balance of $1000 in November which was paid to $0 with the cash surplus in December. This is why the beginning cash value in January is $1000 less than the ending cash in December. ** A loan taken out in any given month cannot be paid off in that month. The earliest a loan will be paid off will be the next month. 42 Budget Cellular Calls Data Messages Cash Flow Task # Points Task Description 1 3 Create a named range called, "Income" that references the cell that calculates the total income in your budget. N 3 Create a named range called, "Expenses" that references the cell that calculates the total expenses in your budget. 3 4 Create a named range called, "Savings" that references the cell that calculates the monthly savings in your budget. The savings calculation should be the difference between the total income and total expenses in your budget. Be sure to reference the named ranges for income and expenses in your calculations. Total: 10 Task # Points Task Description 1 3 Calculate the total cash inflows for the six months. The total cash inflows are sum of the cash sales and customer payments. N 3 Calculate the total expenses for the six months. The total expenses are sum of all of the expenses incurred for each the month in which they are incurred. 3 3 Calculate the net cash flows for the six months. The net cash flows are calculated as the difference between the total cash inflows and the total expenses for each month. 4 3 Calculate the Shortfall/Surplus Cash for the six months. The shortfall/surplus cash is the beginning cash balance plus the net cash flows for each month. 5 6 Calculate the additional cash needed for the six months. When there is a cash shortfall (the shortfall/surplus cash calculation is less than zero), additional cash must be obtained from a loan to cover all of the monthly expenses. Therefore, additional cash needed is equal to any cash shortfall. It is zero if there is a cash surplus. The additional cash needed should be a positive number even though a shortfall will be a negative number in the model (row 26). 6 6 Calculate the cash available to retire debt for each of the six months. There is cash available to retire debt if there is a cash surplus at the end of a month (shortfall/surplus cash > 0). Therefore cash available to retire debt is equal to any cash surplus at the end of a month. If there is not a surplus, cash available to retire debt is 0. 7 6 Calculate the cash used to retire debt for each of the six months. Since the company can't pay down more debt than there is cash available to retire the debt, the cash used to retire debt cannot exceed the cash available to retire debt. Likewise, the company won't pay more money to retire debt than it has debt from the prior month to retire. Therefore, the cash used to retire debt will be equal to the cash available to retire debt if that total is less than the loan balance from the prior month. Otherwise, the cash used to retire debt will be the loan total from the prior month. 8 3 Calculate the loan balance for each of the six months. The loan balance is calculated as the additional cash needed for a given month minus any cash used to retire debt for that month plus the loan balance carried from the prior month. 9 3 Calculate the ending cash balance for each month. The ending cash balance is equal to the cash available to retire debt minus any cash used to retire debt. 10 3 Calculate the beginning cash balance for February through June. The beginning cash balance is equal to the ending cash balance from the prior month. 11 3 3 Calculate the loan interest for each month. The loan interest is equal to the loan balance for that month times the monthly interest rate in cell C3. This will create a circular reference. Configure Excel to appropriately handle the circular reference. 12 3 Calculate the line of credit needed. The line of credit needed is the credit limit the company needs to negotiate with the bank. It is calculated as the maximum loan balance that the company expects to carry over the course of the six month period. 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. N 1 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 O for the number of digits. 3 3 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. 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 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. 6 1 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 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.) 7 1 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. 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 1 Calculate the "Total Messages Sent" on the Cellular worksheet. This is the sum of the "Text" and "Media" messages sent. 10 2 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). 11 2 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) 12 3 3 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 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 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 1 Calculate the Total Bill for option 1. This is the sum of the total charges and the taxes and fees for this option. 17 2 Calculate the Monthly Cost for option 2. Reference the monthly cost for option 2 in the model inputs. 18 4 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. 19 3 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. 20 3 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 1 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 2 Calculate the Monthly Cost for option 3. Reference the monthly cost for option 3 in the model inputs. 25 2 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 2 Calculate the Total Bill for option 3. This is the sum of the total charges and the taxes and fees for this option. A B D E F . L M N P R S 1 2 3 4 5 Prepare a personal budget for one month. Be sure to think through your sources of income and expenses. Be as detailed as you wish; but include cells that calculate the following: Income (which calculates the sum of all income sources), Expenses (which calculates the sum of all expenses), and Savings (which calculates the difference between the total income and expenses). After completing your budget, complete the tasks to create named ranges that reference the important cells outlined above. 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Budget Cellular Calls Data Messages CashFlow # B H 1 2 Plan Costs 3 4 $0.05 Plan Options Option 1 - Pay-as-you-go Minute Charges Data Charges per Megabyte Text Messages Included (no media) Text Message Charges Media Message Charges Usage Summary Calls Weekday Daytime Minutes Evening & Weekend Minutes Total Minutes Used 5 $0.03 Option 1 - Pay-as-you-go Minute Charges Data Charges** Text Message Charges Media Message Charges 6 500 7 $0.05 8 $0.10 Data Used (gigabytes) 9 10 Total Charges Taxes and Fees (cell F15) Total Bill 11 $89.00 Messaging Text Messages Sent Media Messages Sent Total Messages Sent 12 700 13 1 Option 2 - Rate Plan w/Overages Monthly Cost Minutes Included* Data Included (gigabytes) Messages (any type) Included Over-minute Charges Over-data Charges per megabyte Over-message Charges per message 14 1000 15 $0.10 18% Option 2 - Rate Plan w/Overages Monthly Cost Over-minute Charges* Over-data Charges** Additional Message Charges 16 Cellular Tax/Fee Rate # Kilobytes in a Gigabyte** # Megabytes in a Gigabyte** $0.05 1048576 17 $0.10 1024 18 19 Option 3 - Unlimited Rate 20 $119.00 ** 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). Total Charges Taxes and Fees (cell F15) Total Bill 21 22 * Night and weekend minutes are free for Option 2 23 Option 3 - Unlimited Monthly Cost 24 25 26 27 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. Total Charges Taxes and Fees (cell F15) Total Bill 28 29 30 31 32 B D E F G H 1 2 Minutes Weekday? Daytime? Weekday/Daytime? Time Charged 3 1.62 Yes Yes 4 2.24 Yes Yes 5 Date Time 01-Oct 2! 11:28:00 01-Oct 2 12:38:00 01-Oct 1:10:00 01-Oct 1:16:00 01-Oct 1:20:00 01-Oct 2:41:00 0.46 Yes Yes 6 0.27 Yes Yes 7 4.98 Yes Yes 8 0.44 Yes Yes 9 01-Oct 2:49:00 1.57 Yes Yes 10 01-Oct 3:33:00 0.25 Yes Yes 11 01-Oct 0.08 Yes Yes 12 01-Oct 2.07 Yes Yes 13 01-Oct 1.30 Yes Yes 14 01-Oct 0.36 Yes Yes 15 Day Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Monday Tuesday Tuesday Tuesday Tuesday Tuesday Tuesday Tuesday Tuesday 01-Oct 0.72 Yes Yes 3:37:00 3:41:00 4:12:00 5:04:00 5:08:00 5:20:00 6:30:00) 7:39:00 8:35:00 8:38:00 16 01-Oct 3.50 Yes Yes 17 01-Oct 3.45 Yes Yes 18 01-Oct 1.29 Yes No 19 01-Oct 0.05 Yes No 20 01-Oct 2.75 Yes No 21 02-Oct 210:00:00 2.58 Yes Yes 22 02-Oct 2! 10:31:00 0.83 Yes Yes 23 0.36 Yes Yes 24 2.18 Yes Yes 02-Oct 21 10:41:00 02-Oct 21 10:45:00 02-Oct 27 11:56:00 02-Oct 2 12:25:00 25 1.29 Yes Yes 26 1.51 Yes Yes 27 02-Oct 2= 12:53:00 0.14 Yes Yes 28 02-Oct 1.02.00 . 118 Yes Yes A B C D E 1 2 2 Date Time Data Used (Kb) 3 3 01-Oct 10:28 1068 4 01-Oct 11:05 60 5 01-Oct 11:55 2061 6 01-Oct 12:53 273 7 01-Oct 13:26 2027 01-Oct 13:32 63 8 9 9 01-Oct 13:54 4 10 01-Oct 14:19 21 11 01-Oct 14:38 62 12 01-Oct 15:04 1181 13 01-Oct 16:06 90 14 01-Oct 16:08 46 15 01-Oct 16:33 476 16 6 01-Oct 17:20 88 17 01-Oct 17:24 1414 18 8 01-Oct 18:35 365 19 01-Oct 18:38 2311 20 01-Oct 18:46 34 21 02-Oct 11:28 2040 22 02-Oct 12:19 4104 23 02-Oct 12:53 3069 24 02-Oct 13:15 2361 25 02-Oct 13:42 4071 26 02-Oct 14:05 4038 D B C D E F 1 2 Date Time Type 3 01-Oct 7:33 Text 4 01-Oct 8:41 Text 5 01-Oct 9:01 Text 6 01-Oct 9:19 Text 7 01-Oct 9:36 Text 8 01-Oct 9:39 Text 9 01-Oct 9:45 Text 10 01-Oct 10:03 Text 11 01-Oct 10:27 Text 12 01-Oct 10:51 Text 13 3 01-Oct 10:53 Text 14 01-Oct 10:59 Text 15 5 5 01-Oct 11:03 Text 16 01-Oct 11:42 Media 17 01-Oct 11:48 Text 18 01-Oct 12:19 Text 19 01-Oct 12:23 Media 20 01-Oct 12:37 Text 21 01-Oct 12:50 Text 22 01-Oct 13:16 Text 23 01-Oct 14:15 Media 24 01-Oct 14:55 Text 25 01-Oct 15:28 Text 26 01-Oct 16:26 Text 27 01-Oct 16:37 Text 28 Text 01-Oct 16:58 Budget Cellular Calls Data Messages CashFl B H K L M N O PQ 2 Cash Flow Budget Worksheet 1.5% 3 Monthly Interest Rate Complete the statement of cash flow for January through June by completing the tasks. As part of the process you will need to use a circular reference. You will need to set Excel to handle circular references. 5 Line of Credit Needed 6 7 December February March April May June January $5,000.00 8 Beginning Cash Balance 9 10 11 Cash Inflows (Income): Customer Payments Cash Sales Total Cash Inflows $35,000.00 $45,000.00 $53,000.00 $64,680.00 $76,540.00 $90,540.00 $52,000.00 $62,000.00 $73,500.00 $86,000.00 $100,600.00 $117,700.00 12 13 14 15 16 $20,000.00 $30,000.00 $35,000.00 $40,000.00 $50,000.00 $60,000.00 17 18 $4,500.00 19 Cash Outflows (Expenses): Advertising Loan Interest Insurance Inventory Purchases Office Supplies Payroll Rent or Lease Utilities & Telephone Total Expenses 20 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $46,000.00 $53,000.00 $54,500.00 $59,800.00 $71,900.00 $3,200.00 $3,200.00 $3,200.00 $3,500.00 $4,000.00 $11,000.00 $12,000.00 $13,500.00 $18,100.00 $17,700.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $2,700.00 $3,000.00 $3,100.00 $3,200.00 $3,500.00 21 $83,300.00 $4,000.00 $23,200.00 $12,000.00 $5,000.00 22 23 24 25 26 Cash Flows (Net): 27 28 Shortfall/Surplus Cash $6000.00+ 29 30 31 $0.00 32 Loan Calculatations Additional Cash Needed Cash Available to Retire Debt Cash Used to Retire Debt Loan Balance** 33 $6,000.00 $1,000.00 $0.00 34 35 36 Ending Cash Balance $5,000.00 37 38 39 40 41 * Note: there was a loan balance of $1000 in November which was paid to $0 with the cash surplus in December. This is why the beginning cash value in January is $1000 less than the ending cash in December. ** A loan taken out in any given month cannot be paid off in that month. The earliest a loan will be paid off will be the next month. 42 Budget Cellular Calls Data Messages Cash Flow