Question 1.1. (TCO 1) You are the Payroll Manager for your employer. Your payroll clerk has submitted the worksheet displayed here. All employees earn their regular pay rate times the hours worked plus an overtime bonus of 50% of the hours worked in excess of 40 hours.

Part 1: Write a formula as if it were in Cell E2 that will calculate gross pay. Write the formula so that it may be copied to the other employees without modification.

Part 2: You must also calculate the Income Tax which is assessed at an accelerated rate as shown in the Tax Rate Table. Write a formula as if it were in Cell F2 that calculates the income tax for the first employee. Write the formula so that it may be copied to the other employees without modification.

Part 3: Hourly employees must pay union dues of $0.25 per hour on hours worked in excess of 25 hours up to and including 40 hours plus $0.35 per hour on all hours worked in excess of 40 hours. Write a formula as if it were in Cell G2 that calculates the union dues (if any) for the first employee. Write the formula so that it may be copied to the other employees without modification.

Part 4: Salaried and Exempt employees (Pay Codes E and S) are covered by the company's health insurance. The deduction is $10.00 per week. Write a formula as if it were in Cell H2 that calculates the Insurance deduction (if any) for the first employee. Write the formula so that it may be copied to the other employees without modification.

Question 2.2. (TCO 3) You are a Microsoft Office trainer for DeVry University. The training topic for the current month is charts using Microsoft Excel. During each training session from different departments or cohorts, the following frequently asked questions were asked and you decide to create a document formalizing answers and suggestions.
1. My supervisor, Sally, likes my charts; however, she instructs me to take more time planning my charts before creating the actual chart itself. Why is planning the creation and development of a chart so important?
2. My supervisor, John, ask me to chart some comparative data but my first attempt with a column chart did not illustrate the data as well as he would have liked. What type of chart is ideal and best to illustrate comparative data of individual items in a data series and why?
3. My supervisor, Jessica, asked me to project future data trends using a chart. Most of the data is associated with sales and depending on the nature of what is sold, data either fluctuates or increases quickly. What chart feature added to a column chart can help project these data trends and why?
4. My supervisor, Thomas, asked me to add some creativity with color and enhancements on all charts used during weekly meetings. What should I consider to avoid being overly creative and why is this important?

Question 3.3. (TCO 5) You work for a local construction firm "DeVry Engineering Group" and your supervisor, Jessica, needs an updated earned value analysis index of the prior month for an existing capital project (0000001) that has a current Budget at Completion (BAC) of $1500.00, a current earned value of (EV) of $300, a current actual cost (AC) of $500.00, and a current planned value (PV) of $350.00. In order to calculate the current average index, assume the following variables and standard formulas below:
Cost Variance (CV) EV-AC
Cost Performance Index (CPI) EV/AC
Schedule Variance (SV) EV-PV
Schedule Performance Index (SPI) EV/PV
Estimate to Completion (ETC) EAC-AC
Estimate at Completion (EAC) BAC/CPI
Variance at Completion (VAC) BAC-EAC
Status (Earned Value Index) (EVI) (CPI+SPI)/2
Also assume that a final calculated average index of 1.00 or greater represents a project being on track and in good standing. In addition, there could be several options for the descriptive methods to design this worksheet and below is a worksheet setup with the following "3" columns including any other needed top labels like for the existing Month already calculated.


1. Based on the current calculated earned Status (Earned Value Index) for work order 0000001, is the current capital project in good standing?
2. Your supervisor, Jessica, wants the worksheet, on row 16 or 17, to automatically provide a project status as being "In the Red" (Poor Standing) or "In the Black" (Good Standing). Explain descriptively how you could use a conditional formula to report the project status.
3. Your Supervisor, Jessica, has 3 additional projects with work orders, 0000002, 0000003, and 0000004 she wants added to this Excel Workbook where each work order is monitored on a separate worksheet like work order 0000001. Jessica would like to have a summary worksheet which compiles all information for all work orders. The overall design of the summary worksheet will match the same general design as with work orders 0000001 through 0000004. Descriptively describe steps needed to link these worksheets together along with any ideas for formulas and functions to give an overall picture of all project work orders combined so that Jessica can see an overall holistic view of the Status (Earned Value Index) across all these work orders being managed.
4. Based on the summary worksheet, Jessica, would like to see a chart which can help with future projections of the Status (Earned Value Index). Descriptively describe which chart you would recommend with any features that will help Jessica with future projections. Also using your intuition and creative mind, descriptively describe if any other information needs to be charted from the summary worksheet which may help Jessica with future cost projections and why?

Question 4.4. (TCO 5) A table named "Daily Sales" in a Microsoft Access database has the following fields:

Product A
Product B
Product C
Product D

Each record on the table represents a day of total sales for each product and the data covers the last five years. Your boss asks you to create a visual report of the sales performance of each product during the last 12 months.

Prepare a step-by-step description of how you would use Microsoft Excel to prepare the report and email it to your boss following these steps:

Step 1 - Import data from Access to Excel
Step 2 - Summarize Monthly Data for each product during the last 12 months
Step 3 - Create a visual representation of monthly data
Step 4 - Email the spreadsheet with your comments to your boss
Question 5.5. (TCO 9) You have been tasked with analyzing an extremely large amount of data and to ultimately produce a report to share with the Board of Directors. The data is currently in a text file and has over two thousand records of data. Explain how you would use Excel to analyze this data and organize it to prepare a written report. Be very specific on the variety of tools you would use and the steps you would go through to analyze the data and to ultimately prepare a detailed report with recommendations.

Note: This is a one part question.

Part 1: Write a formula as if it were in Cell E2 that will calculate gross pay. Write the formula so that it may be copied to the other employees without modification.

Part 2: You must also calculate the Income Tax which is assessed at an accelerated rate as shown in the Tax Rate Table. Write a formula as if it were in Cell F2 that calculates the income tax for the first employee. Write the formula so that it may be copied to the other employees without modification.

Part 3: Hourly employees must pay union dues of $0.25 per hour on hours worked in excess of 25 hours up to and including 40 hours plus $0.35 per hour on all hours worked in excess of 40 hours. Write a formula as if it were in Cell G2 that calculates the union dues (if any) for the first employee. Write the formula so that it may be copied to the other employees without modification.

Part 4: Salaried and Exempt employees (Pay Codes E and S) are covered by the company's health insurance. The deduction is $10.00 per week. Write a formula as if it were in Cell H2 that calculates the Insurance deduction (if any) for the first employee. Write the formula so that it may be copied to the other employees without modification.