# We Helped With This Excel Programming Assignment: Have A Similar One?

SOLVED
Category Programming Excel College Solved Excel Assignments With Solutions

## Assignment Description

CIS 2003

Excel Project

Spring 2019

You are a new employee at the Landmark Company.  Your job is analysis, in particular, assessment of employee data and creation of relevant reports.  Management has just given you a set of Excel spreadsheets to be analyzed.  The spreadsheets are as follows: Employment, Salaries, Ethnic, Performance, District, and State.  Management would like a number of reports created, but before that can be done, you must first have a deep understanding of the data that has been entered in the spreadsheets.

Step 1: Data Validation – for each of the following on the “Employment” sheet, use a formula to validate that the data has been entered correctly.  For example, according to the “Salaries” sheet, the only Job Classifications allowed are 1A-9I.  Use a formula to mark incorrect entries as “Invalid” in red font (using conditional formatting), and leave correct entries blank.  Columns have already been setup on the sheet for your entry. (3 pts. each)

·   Note: When using Vlookup, IFNA must be used as well.  Also, no entries are allowed to be blank; blanks should appear as “Invalid” until corrected in Step 2 below.

(1a)   Male/Female (must be M or F)

(1b)   Ethnic Code

(1c)   State Code

(1d)  Company District

(1e)   Shares of Stock (must be between 0 and 300, inclusive)

(1f)   Job Classification

(1g)   Performance Rating

Step 2: Data Correction – for all invalid entries, correct the data.  For example, if a job classification is incorrect, change it to any value that will make it valid.  In the real world, you would return the incorrect data to the appropriate party for correction.  Since we are dealing with a fictional company, just make the data correct. (4 pt.)

Step 3: Additional Calculations Needed – management has requested that the “Employment” sheet contain calculations to produce the following.  Use conditional formatting (red font) for “Invalid” entries. When using Vlookup, IFNA must be used as well.  (4 pts. each)

·   Note: Be sure to format data appropriately, for example, \$ dollar signs.

·   Note: Salary ranges (on the “Salaries” sheet) have been provided based on the job classification; therefore, for each employee, management needs to know if the new salary (step 3a) is still within that range.  Therefore, first determine the min and max salaries (steps 3b and 3c) for each employee given his/her job classification. Then, use a formula to determine if each employee’s new salary is out of its appropriate range (step 3d).

(3a)   New Salary for each employee (this is a result of the old salary plus the employee raise based on that employee’s performance rating)

(3b)   Minimum Salary (see note above)

(3c)   Maximum Salary (see note above)

(3d)  Out of Salary Range (show “Overpaid” in green font and “Underpaid” in blue font (using conditional formatting) for invalid salaries) (see note above)

(3e)   Security Authorization Level 4.  Produce a new security authorization code using the first letter of the employee’s first name as the first character, the first letter of the employee’s last name as the second character, the last (3) digits of the employee id as the third, fourth, and fifth characters, and “TU” as the sixth and seventh characters.

Step 4: Reports – use pivot tables and graphs (were requested) to answer the following: (4 pts. each)

·   Note: Be sure to format data appropriately, for example, \$ dollar signs.

·   Note: Each report and graph is required to have a meaningful title, and graphs must have x and y axes labeled appropriately.  Place the title in cell A1 of each pivot table sheet.  Do not use the sheet name as either the report or graph title. Failure to use an appropriate title will result in a score of zero on the question.

(4a)   The total amount of money spent on old salaries and new salaries by ethnic group and gender. Create a pivot table only. Show the ethnic name, not the code. Name the new sheet – 4a-Salaries Report.

(4b)   The number of employees and the average shares of stock owned by ethnic and district.  Show the district name and the ethnic name, not the codes.  Create a pivot table only.  Name the new sheet – 4b-Stock Shares Report.

(4c)   The number of employees by job perforance.  Create a pivot table and graph.  Name the new sheet – 4c-Job Report.

(4d)  The number of employees and the total new salary for each district. Show the district name, not the code.  Create a pivot table only.  Name the new sheet – 4d-District Report.

(4e)   Create a report that will be useful to management (i.e., use your imagination).  Create a pivot table only. Name the new sheet – 4e-Management Report.

Step 5: Explanations – for each of the five reports in step 4, briefly explain below each (on the worksheet itself) what the report tells management and how management might use the report in decision making.  You should cite numbers from your tables in your explanations. (2 pt. each)

Before turning in your project, make sure your worksheets are in the order indicated below and your Excel file is named as indicated below.  Failure to do so will result in a five-point reduction in your overall project score.

·   Worksheet order: (1) Employment, (2) 4a-Salaries Report, (3) 4b-Stock Shares Report, (4) 4c-Job Report, (5) 4d-District Report, (6) 4e-Management Report, and (7) all other sheets.

·   Excel file name: yourlastname_firstname_classtime.xlsx (If working in teams, list all ...

Is it free to get my assignment evaluated?

Yes. No hidden fees. You pay for the solution only, and all the explanations about how to run it are included in the price. It takes up to 24 hours to get a quote from an expert. In some cases, we can help you faster if an expert is available, but you should always order in advance to avoid the risks. You can place a new order here.

How much does it cost?

The cost depends on many factors: how far away the deadline is, how hard/big the task is, if it is code only or a report, etc. We try to give rough estimates here, but it is just for orientation (in USD):

 Regular homework \$20 - \$150 Advanced homework \$100 - \$300 Group project or a report \$200 - \$500 Mid-term or final project \$200 - \$800 Live exam help \$100 - \$300 Full thesis \$1000 - \$3000

How do I pay?

Credit card or PayPal. You don't need to create/have a Payal account in order to pay by a credit card. Paypal offers you "buyer's protection" in case of any issues.

Why do I need to pay in advance?

We have no way to request money after we send you the solution. PayPal works as a middleman, which protects you in case of any disputes, so you should feel safe paying using PayPal.

Do you do essays?

No, unless it is a data analysis essay or report. This is because essays are very personal and it is easy to see when they are written by another person. This is not the case with math and programming.

Why there are no discounts?

It is because we don't want to lie - in such services no discount can be set in advance because we set the price knowing that there is a discount. For example, if we wanted to ask for \$100, we could tell that the price is \$200 and because you are special, we can do a 50% discount. It is the way all scam websites operate. We set honest prices instead, so there is no need for fake discounts.

Do you do live tutoring?

No, it is simply not how we operate. How often do you meet a great programmer who is also a great speaker? Rarely. It is why we encourage our experts to write down explanations instead of having a live call. It is often enough to get you started - analyzing and running the solutions is a big part of learning.

What happens if I am not satisfied with the solution?

Another expert will review the task, and if your claim is reasonable - we refund the payment and often block the freelancer from our platform. Because we are so harsh with our experts - the ones working with us are very trustworthy to deliver high-quality assignment solutions on time.

## Popular Solved Assignments Like This

Customer Feedback

"Thanks for explanations after the assignment was already completed... Emily is such a nice tutor! "

Order #13073

Find Us On