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 companys 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. (Points : 40) 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. 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? 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? 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? 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? (Points : 40) 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) EVAC Cost Performance Index (CPI) EV/AC Schedule Variance (SV) EVPV Schedule Performance Index (SPI) EV/PV Estimate to Completion (ETC) EACAC Estimate at Completion (EAC) BAC/CPI Variance at Completion (VAC) BACEAC 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. Based on the current calculated earned Status (Earned Value Index) for work order 0000001, is the current capital project in good standing? 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. 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. 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? (Points : 40) 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 (Points : 40) 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.
Please note that this is just a preview of a school assignment posted on our website by one of our clients. If you need assistance with this question too, please click on the Submit Your Instructions at the bottom of the page to get started.
Need Writing Help? Our writing specialists are here 24/7, every day of the year, ready to support you! Instantly chat with an online tutor below or click here to submit your paper instructions to the writing team.
More than just an assignment.
Who is this homework service for?
* If you are having a really hard class and want to get through it, then this is for you.
* If you have a medical emergency or someone close to you has a medical emergency and you don’t think you’ll be able to turn your assignment on time, this is definitely a service you could use.
* You can use us if you are having a tough Professor who won’t give you the grades you deserve.
* If you have a tight work schedule and you are getting points deducted for not submitting assignments on time.
* English might not be your first language and you feel like you are being left behind in class because of it.
* If you have a large project coming up and don’t think you have enough time to get it done well, definitely reach out to us.
Hi there! My name is Anna.
Super stoked you are checking us out! We would like to help you with your assignment. We just need a few things from you:
* The full assignment instructions as they appear on your school account.
* If a Rubric is present, make sure to attach it.
* Any relevant weekly readings or learning resources.
* Include any special announcements or emails you might have gotten from your Professor regarding your assignment.
* Any templates or additional files required to complete the assignment.
If your assignment is somewhat complex and you need to explain it, please don’t hesitate to reach out to me via live chat.
Frequently asked questions
How soon can I get my paper done?
Will it be completely original? I don't want to be caught in a case of Academic Integrity Violation.
We are as paranoid as you are. Maybe even more! And we understand that the greatest sin you can commit in your academic journey is plagiarizing your academic work. To that end, we have made sure that we check and double-check our papers using high quality plagiarism detection tools such as SafeAssign and Turnitin before submitting the paper to you.
Who is my writer? Is he/she a native English Speaker?
All our writers are native English Speakers. That is not to say that ESL writers are not good, we just prefer hiring native writers because we want the very best people working on your paper. This might mean paying a little bit more for your paper as opposed to when you pay a foreign company whose writers are non-native English Speakers.
What if I need revisions? Will your charge additional for this?
Of course not! If you do happen to require a revision on your paper, our team will handle it for you free of charge. Matter of fact, we won’t rest till you are happy with your paper. So, ask for as many revisions as you need, it’s completely FREE!
Will you give me my money back if I don't like my paper?
We have very few instances where we delivered a paper that a client didn’t fall in love with. But if it so happens that you don’t like your paper for any reason whatsoever, we’ll refund your money back no questions asked.
I have more assignments after this, can you help me with those too?
Of course! And what’s even better is that we can reserve a writer permanently to work on your entire class. This comes in handy for projects which build up on each other and where you need just one writer, one writing style.