1. Paycheck Calculator

You work in the human resources department of your company helping new employees fill out the necessary paperwork to get their first paycheck. There are a number of decisions that employees must make when they complete this paperwork, including (1) which health insurance package to buy (this impacts how much money will be deducted each pay period to pay for the premium), (2) how much money to put in a flexible spending account annually to cover medical and childcare expenses, and (3) the percentage of their regular paychecks to invest in the company’s 401k retirement savings plan. The expenses paid for these three items are not subject to Federal or State income taxes.

New employees must also complete a W4 form where they claim their marital status and number of allowances for deductions for tax purposes. Often these employees will ask you to project their first paycheck so that they know how much money to plan on for their personal budget.

You have decided to create a spreadsheet model that will make these projections given the information the new employee provides on the paperwork.

Note: Because the employee information will change as you work with different employees, your solution should be designed to handle changing employee information. For example, the current employee has worked overtime (the employee has worked 85 hours, and a regular work period has 80 hours). Your solution should calculate this employee’s pay information and correctly handle an employee who does not work overtime.

1.1

Enter the hours worked and the pay rate for the employee into the Paycheck Calculations section of the model.

a. Reference the hours worked (C3) and pay rate (C4) values in the “Employee Information” area of the spreadsheet model.

1.2

Calculate the regular pay.

a. Reference cell C21 for the “Hours Worked”.

b. The regular pay will be the hours worked times the pay rate unless the employee works overtime (more than the number of regular hours in the pay period – described in the model assumptions).

c. If the employee works overtime, the regular pay is the rate times the number regular hours in the pay period.

1.3

Calculate the overtime pay.

a. Reference cell C21 for the “Hours Worked”.

b. The employee is paid 1.5 times the regular pay rate for any time the employee works more than the number regular hours in pay period.

1.4

Calculate the total gross pay.

· The total gross pay is the sum of the regular pay and the overtime pay.

1.5

Reference the health insurance deduction.

a. Reference the appropriate cell in the employee information section of the model for the health insurance deduction.

b. Notice the amount in this section is already calculated for each paycheck.

1.6

Calculate the flexible spending deduction.

a. Reference the appropriate cell in the employee information section of the model.

b. Notice that the flexible spending deduction is an annual rate.

c. You will need to divide this by the number of paychecks per year in the Model Assumptions section of the model.

1.7

Calculate the retirement savings deduction.

· The retirement savings deduction is the total gross pay times the retirement savings percentage for the employee.

1.8

Calculate the total deductions.

· The total deductions equals the sum of the insurance, flexible spending, and retirement savings deductions.

1.9

Calculate the adjusted income.

· The adjusted income is the difference between the total gross pay and the total deductions.

1.10

Use an IF function to calculate the Federal Income Tax Rate.

a. The federal tax rate is a function of the adjusted income and the employee’s marital status.

b. Reference the tax tables in the Model Assumptions and the marital status in the Employee Information section to construct a formula (or set of nested formulas) to calculate the tax rate.

c. For example, a single employee who earned $15,000 would pay a 33% marginal tax rate.

1.11

Calculate the federal income tax.

a. The federal income tax is the adjusted income times the federal income tax rate calculated in cell C36.

1.12

Calculate the tax adjustment for allowances. An employee will have less federal income tax withheld for every allowance that they claim. Allowances account for the number of dependents they will claim on their taxes and other factors that will ultimately reduce the amount of federal taxes they will pay.

a. The adjustment for allowances is calculated as the product of the number of allowances (in the employee information section), the allowance deduction amount (in the model assumptions), and the employee Federal Income Tax rate (calculated in task 10).

1.13

Calculate the net federal income tax.

a. The net federal income tax is the difference between the federal income tax and the adjustment for allowances.

b. If the adjustment for allowances is greater than the federal income tax, then the net federal income tax is zero (0).

1.14

Calculate the state income tax.

· The state tax is the adjusted income times the state tax rate in the Model Assumptions section.

1.15

Calculate the medicare tax rate.

· The medicare tax rate is the total gross pay times the medicare tax rate in the Model Assumptions section.

1.16

Calculate the social security tax.

· The social security tax is the total gross pay times the social security tax rate in the Model Assumptions section.

1.17

Calculate the total taxes.

· The total taxes is the sum of the net federal income tax, the state income tax, the medicare tax, and social security tax.

2. 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.

2.1

Complete the Megabytes Used row (G4:L4) of the Usage Summary table on the Data worksheet.

a. Build the formula to calculate total Megabytes Used for January (G4), by referencing the month name in G3 and the appropriate columns in the data table (B3:D183).

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

c. Notice that the “Gigabytes Used” (G5:L5) 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).

d. The Gigabytes Used (Rounded) (G6:L6) 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.

2.2

Complete the Monthly Charges row (F5:K5) of the Cost Comparison table on the Cellular worksheet for Option 1.

a. Enter the monthly charges for January (F5) by referenceing the monthly charges for option 1 (C4) in the Plan Options table.

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

2.3

Complete the Taxes and Fees row (F6:K6) of the Cost Comparison table on the Cellular worksheet for Option 1.

a. Calculate the tax for January in cell F6.

b. The taxes and fees are calculated as the monthly charges (not including any data charges) times the taxes and fees rate (cell C12) on the Plan Options table.

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

2.4

Complete the Gigabytes Used (Rounded) row (F7:K7) 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.

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

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

2.5

Complete the Data Charges row (F8:K8) of the Cost Comparison table on the Cellular worksheet for Option 1.

a. Calculate the data charges for option 1 in January in cell F8.

b. 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.

c. 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.

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

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

2.6

Complete the Total Cost row (F9:K9) of the Cost Comparison table on the Cellular worksheet for Option 1.

a. Calculate the total cost for option 1 in January in cell F9.

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

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

2.7

Complete the Which is best? row (F17:L17) of the Cost Comparison table on the Cellular worksheet.

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

b. 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.

c. If the total cost of Option 2 is less than the total cost of Option 1, display “Option 2”.

d. Otherwise, display “No Difference”.

e. Reuse your formula to determine which option is best for each month and in cell L17 to determine which option is best overall.

3. Data Validation Assessment

RideShare USA is private taxi service that provides scheduled rides for customers from the airport to any location within 100 miles. The form on the RideShare worksheet is a spreadsheet model used to quote pricing to customers. A salesperson enters information about the trip and the model calculates the price. The cost of the trip is determined by the number of travelers and the miles traveled. RideShare USA charges a premium for peak times of the day. A deposit is required for trips with more than 4 travelers. Complete the tasks by using data validation to constrain the trip information entries to help ensure that valid data is entered.

3.1

Use data validation in cell C3 to constrain entries for the customer name.

a. Allow text with a length of at most (less than or equal to) 30 characters.

b. Configure the input message with a title of “Customer Name” and “Enter the customer name.” as the message.

c. Set the error alert with the title of “Invalid Customer Name”, and “The customer name contains more than 30 characters.” as the message.

3.2

Use data validation in cell C4 to constrain entries for the trip date.

a. Allow a date occuring on or after (greater than or equal to) 1/1/2021.

b. Configure the input message with a title of “Trip Date” and “Enter the trip date.” as the message.

c. Set the error alert with the title of “Invalid Trip Date”, and “The trip date must occur on or after 1/1/2021.” as the message.

3.3

Use data validation in cell C5 to constrain entries for the time of the trip.

a. Allow a time occuring during normal business hours (6:00 AM to 11:00 PM).

b. Configure the input message with a title of “Time of the Trip” and “Enter the time of the trip.” as the message.

c. Set the error alert with the title of “Invalid Trip Time”, and “The trip must occur during normal business hours (6:00 AM to 11:00 PM).” as the message.

3.4

Use data validation in cell C6 to constrain entries for the number of travelers.

a. Allow a whole number between 1 and 12.

b. Configure the input message with a title of “Number of Travelers” and “Enter the number of travelers.” as the message.

c. Set the error alert with the title of “Invalid Number of Travelers”, and “The number of travelers must be a whole number between 1 and 12.” as the message.

3.5

Use data validation in cell C7 to constrain entries for the number of mile.

a. Have the user select the number of miles from the list of 25, 50, 75, and 100.

b. Configure the input message with a title of “Number of Miles” and “Select the number of miles.” as the message.

c. Set the error alert with the title of “Invalid Number of Miles”, and “The number of miles was not selected from the list.” as the message.

3.6

Use data validation in cell C8 to constrain entries for whether the deposit is required.

a. Use a custom data validation rule.

b. The value for deposit required must be “Yes” if the number of travelers is more than 4, otherwise it is should be “No”.

c. Hint – a valid formula for the custom rule is ‘=C8=IF(C6>4,”Yes”,”No”)’

d. Configure the input message with a title of “Deposit Required” and “Enter whether the deposit is required.” as the message.

e. Set the error alert with the title of “Invalid Deposit Required Entry”, and “The value for deposit required is not correct given the number of travelers.” as the message.