logo Use SA10RAM to get 10%* Discount.
Order Now logo

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

expert
Patricia JohnsonNursing
(5/5)

517 Answers

Hire Me
expert
Neil BissonnetteeCriminology
(5/5)

782 Answers

Hire Me
expert
Avinash KumarData mining
(/5)

897 Answers

Hire Me
expert
Nathaniel KellyComputer science
(5/5)

783 Answers

Hire Me
Microsoft Excel
(5/5)

The basic wage of Rs/hour for a worker is not directly known. All employees are divided into five types A, B, C, D, and E.

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

Instructions

1. The test contains 2 main questions with sub-sections

2. The duration of the test is 40 minutes

3. Ensure that the workbook is formatted properly

4. Ensure the header & the footer are as specified in the file

 

Suppose you are given the following data related to a week on each employee of a company:

Employee code

Employee Type

Hours worked during the week

Basic wage in Rupees per hour

Number of pieces produced

 

The basic wage of Rs/hour for a worker is not directly known. All employees are divided into five types A, B, C, D, and E. The basic wage varies from one type to another as shown in the following table:

 

Employee Type Basic Wage

A 20

B 25

C 30

D 40

E 45

 

Further, the following data that applies to all employees is also given:

Standard rate of production in number of pieces per hour : 10 pieces per hour

Extra pay (or, incentive pay) for each piece produced over and above the standard production, in Rupees per piece: 5 Rupees per piece

 

Design a spreadsheet to contain the above data, and to compute using this data the following:

For each employee, the regular pay for the week

For each employee, the incentive wage, if any, for the week

For each employee, the total pay for the week

For all the employees put together, the sum of regular, incentive, and total pay

Total sum of the incentive pay as a percentage of the sum of total pay.

 

These formulae are devised based on the following rules:

Extra production = Maximum of {(actual production- standard production),0}

Regular pay = Basic wage in Rs per hour * hours worked

Incentive pay = Extra production * incentive rate in Rs per piece

Total pay = Regular pay + incentive pay

 

 

 

Employee Code Employee Type Hours worked Pieces produced

E1 A 40 440

E2 B 48 530

E3 E 56 490

E4 D 80 600

E5 C 20 200

E6 A 32 400

E7 A 45 600

E8 E 55 480

E9 D 50 540

E10 A 60 620

 

 

2. Given below is a range of a spread sheet showing the number of gold, silver and bronze medals won by each of the countries which participated in Atlanta Olympics. The table is arranged in alphabetical order of country names. Incorporate the necessary formulae and design the necessary operations in the spreadsheet to answer the following questions or perform the tasks stated:

 

1. Re-organize the data in the table according to descending order of number of gold medals; if there is a tie, use the number of silver medals for tie-breaking; if there is a further tie, use number of bronze medals; then finally, total number of medals. If the tie still remains unbroken, break it randomly.

2. Suppose the rank of a country is determined using the above rule. Countries with the same number of medals in each category should be ranked the same. Create a separate field for the rank of each country and determine the ranks. Is it possible to determine the ranks without actually sorting the data as mentioned above? Suppose the ranks are generated by treating one gold medal as equal to 4 silver medals, or 10 bronze medals. Using the same rule, one silver medal becomes equal to 2.5 bronze medals. What would be the new ranking as per this rule?

3. In a cell(s) of your choice, show the name of the country or countries with the maximum number of gold medals. Similarly, show in separate cells the names of countries with the maximum number of silvers and bronzes.

4. Classify the countries according to the continent to which they belong: Asia, Australia, Europe, Africa, North America, and South America. Generate the medal tally for each continent.

5. How many countries have won all three categories of medals?

6. List the names of countries each of which has won at least 5 gold medals and at least a total of 20 medals. Develop a two way table by varying the above two values (that is at least 6 gold medals and at least 21 total number etc.)

7. According to a hypothesis, the countries with top position in gold medals, do not rank high in bronze medals. Does the above data support this hypothesis?

8. Build a feature in the spreadsheet such that the color of the font for the country names changes as follows: A. red, if the country has a single digit total, B. blue, if the country has no gold nor silver and has single digit number of bronze medals, and C. green, if the country has a rank of less than or equal to ten.

 

 

(5/5)
Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme

Get Free Quote!

411 Experts Online