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.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
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
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
