The spreadsheet in Microsoft Excel file Ch01Ex01_U10e.xlsx contains records of employee activity on special projects. Open this workbook and examine the data that you find in the three spreadsheets it contains. Assess the accuracy, relevancy, and sufficiency of this data to the following people and problems.
- a. You manage the Denver plant, and you want to know how much time your employees are spending on special projects.
- b. You manage the Reno plant, and you want to know how much time your employees are spending on special projects.
- c. You manage the Quota Computation project in Chicago, and you want to know how much time your employees have spent on that project.
- d. You manage the Quota Computation project for all three plants, and you want to know the total time employees have spent on your project.
- e. You manage the Quota Computation project for all three plants, and you want to know the total labor cost for all employees on your project.
- f. You manage the Quota Computation project for all three plants, and you want to know how the labor-hour total for your project compares to the labor-hour totals for the other special projects.
- g. What conclusions can you make from this exercise?
Explanation of Solution
a.
Time taken by employees for completing the special project in Denver plant:
Special project at Denver involves Market Analysis, Quota computation, Sourcing review and Production planning.
- Time taken by employees for Quota computation is “22+30+17+31=100” hours.
- Time taken by employees for Market analysis is “17+21=38” hours.
- Time taken by employees for sourcing review is “9+22+10+21+5=67” hours.
- Time taken by employees for production planning is “12+15+21+35+20+19=122 hours”
Therefore, time taken by employees for completing the special project in Denver plant is “327”hours.
Explanation of Solution
b.
Time taken by employees for completing the special projects in Reno plant:
Special project at Reno plant involves Quota computation, Sourcing review and Production planning.
- Time taken by employees for Quota computation is “27+42=69” hours.
- Time taken by employees for Sourcing review is “17+23+21+27=88” hours.
- Time taken by employees for Production planning is “19+20+11=50” hours.
Therefore, the time taken by employees for completing the special project in Reno plant is “207” hours.
c.
Explanation of Solution
Time taken by employees for Quota Computation in Chicago plant:
Time taken by employees for special project at Chicago plant for Quota computation is: “27+12+22+29+11+29 = 130” hours.
Therefore, the time taken by employees for computation in Chicago plant is “130” hours.
Explanation of Solution
d.
Total time taken by employees for Quota Computation for all plants:
Time taken by employees for special project at Chicago plant, Denver plant and Reno plant for Quota computation is:
- Time taken by employees for Quota computation in Denver plant is “22+30+17+31=100” hours.
- Time taken by employees for Quota computation in Reno plant is “27+42=69” hours.
- Time taken by employees for special project at Chicago plant for Quota computation is: “27+12+22+29+11+29 = 130”hours.
Therefore, the total time taken by employees is: “100+69+130=299” hours.
Explanation of Solution
e.
Total labor cost for all employees in the Quota computation project:
- Suppose the hourly pay of an employee is “$30”.
- The number of hours worked by the employees on Quota computation project is “299” hours.
Labor cost= hourly wage × number of hours = 30 × 299 = $8970
Therefore, the total labor cost for all employees in the quota computation project is “8970$”.
Explanation of Solution
f.
Comparison of labor hour total for Quota computation with other projects:
- The total time taken by employees for Quota computation is: “100+69+130=299” hours.
- The total time taken by employees for market analysis is “38” hours.
- The total time taken by employees for sourcing review is “67+88+52=207” hours.
- The total time taken by employees for production planning is “122+75+50 =247” hours.
Thus, the ratio of Quota computation with Market analysis = 299/38=7.86 hours, the ratio of Quota computation with sourcing review = 299/207=1.44 hours, and the ratio of Quota computation with production planning = 299/247= 1.21 hours.
Explanation of Solution
g.
Conclusion:
From the above questions, it can be observed that Quota computation almost takes “37%” of the total time taken to complete all projects as compared to the projects – market analysis, sourcing review and production planning.
Therefore, it can be said that the labor employed in Quota computation project is less efficient as compared to the other three projects.
Want to see more full solutions like this?
Chapter AE Solutions
Using MIS (10th Edition)
Additional Engineering Textbook Solutions
Concepts of Programming Languages (11th Edition)
Starting Out With Visual Basic (7th Edition)
Starting Out with Programming Logic and Design (4th Edition)
Starting out with Visual C# (4th Edition)
Starting Out with Programming Logic and Design (5th Edition) (What's New in Computer Science)
- I built a snowflake table using the same columns as an Excel file. How do I move the information from Excel to Snowflake?arrow_forwardQUESTION 2 Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The materials manager of this company maintains a list of suppliers for each of the materials items purchased by the company from outside vendors. The following table shows the essential data required for this application. Hillar Furniture Company Purchasing Data is shown below: Attribute Name Material_ID Material_ Name Unit_of_Measure Standard_Cost Vendor_ID Vendor_name Unit_price Terms_Code Terms Sample Value 3792 Hinges 3” locking Each GHC 5.00 V300 Apex Hardware GHC 4.75 1 COD a) Using the dependency diagram, identify the functional dependencies for this data. You may assume the following: Each material has one or more suppliers. Each supplier may have supply one or more items or may not supply many items. The unit price for a material may vary from one vendor to another The terms of a code for a supplier uniquely identifies the terms of a sale (e.g. code 2…arrow_forwardThe Car Maintenance team wants to learn how many times each car is used in every month and day to organize their maintenance schedules. The team wants a table with the following column names and information: Car ID Month Day Count You need to create a summary table using the WITH ROLLUP modifier and grouped by the specific column names, listed above, and send the data back to the team. The table and the output is attachedarrow_forward
- Project 3 Details:This project is based on eight tables (AIRPORT, FLIGHT, DEPARTURES, PASSENGER,RESERVATION, EQUIP_TYPE, PILOTS, and TICKET) that contain data about the Belle Airlines. Downloadand use data in Project 3 zip file for this project.Some Background on Belle AirlinesBelle Airlines is a regional carrier that operates primarily in the southwestern United States. At thepresent time, Belle Airlines operates its own reservation information system. To simplify our analysis,we will assume that all reservations on Belle Airlines flights are placed through Belle Airlines employees.Flights are not booked through travel agents and Belle Airlines does not participate in industry-widereservations services. Each flight is assigned a unique flight number and has its own set of flightcharacteristics (ie. flight number, origin, destination, departure time, arrival time, meal code, base fare,mileage between origin and destination, and number of changes in time zone between the origin…arrow_forwardTo vary your reporting, you decided to include graphical representation. Create a clustered column chart that shows the total amount each active customer with a data plan must pay. Format the total pay column to currency and give the chart an appropriate title. Remember to label the axes. (Hint: Create a query and export it to a spreadsheet application to create the chart.) Ensure that the chart is labelled appropriately and is placed on a new sheet. Name the Spreadsheet as MGMT2006_G#_<first name last name>. For example, MGMT2006_G50_ Jane Doe.arrow_forwardA set of 11 data points is given in the attached Excel spreadsheet: PS5_prob1_FD_data.xlsx.Task: Perform numerical differentiation to approximate the second derivative of theprovided data. In doing so, address the following prompts:1. Write your own MATLAB code to perform an appropriate Finite Difference (FD)approximation for the second derivative at each point in the provided data. Note:You are welcome to use the “lowest order” approximation of the second derivative f ′′(x).a) “Read in” the data from the Excel spreadsheet using a built-in MATLAB com-mand, such as xlsread, readmatrix, or readtable – see docs for more info.b) Write your own MATLAB function to generally perform an FD approximationof the second derivative for an (arbitrary) set of n data points. In doing so, usea central difference formulation whenever possible.c) Call your own FD function and apply it to the given data. Report out/displaythe results.2. Reflect on your results by comparing to the exact solution.a) The…arrow_forward
- While the datamodel design process was still underway, an enterprising accountant started putting together data in a spreadsheet already. Tshepo applauded his enthusiasm, but he is worried about the integrity of the data, despite the fact that the accountant says his data is in first normal form already. Here is a sample of the spreadsheet data, in first normal form (underlined columns names indicate the composite primary key): Company Company Department Department Product Product Name ID Name ID ID Name VR1000 Product Product Type ID Type Name 1 Headset 1 1 ACME VR ACME VR ACME VR 1 1 1 0 No Product Date Value 3306/01/23 521 3306/01/24 -250 3306/01/23 105 3306/01/23 999 3306/01/23 50 1 Production Production HR Finance Production 2 0 No Product 2 ACME Movies 3 0 No Product MC9 2 ACME Movies 4 2 2 Cameraarrow_forwardThe Car Maintenance team wants to learn how many times each car is used in every month and day to organize their maintenance schedules. The team wants a table with the following column names and information:•Car ID•Month•Day•CountYou need to create a summary table using the WITH ROLLUP modifier and grouped by the specific column names, listed above, and send the data back to the team.arrow_forwardanswer a and b questions below Examine the table shown below. This table represents the hours worked per week for temporary staff at each branch of a company staffNo branchNo BranchAddress name position hoursPerWeek S4555 B002 City Center Plaza, Seattle, WA 98122 Ellen Layman Assistant 16 S4555 B004 16 – 14th Avenue, Seattle, WA 98128 Ellen Layman Assistant 9 S4612 B002 City Center Plaza,Seattle, WA 98122 Dave Sinclair Assistant 14 S4612 B004 16 – 14th Avenue, Seattle, WA 98128 Dave Sinclair Assistant 10 a. Identify the functional dependencies represented by the data shown in the table. State any assumptions you make about the data (if necessary b. Using the functional dependencies identified in part (b), describe and illustrate the process of normalization by converting Table 1 to Third Normal Form (3NF) relations. Identify the primary and foreign keys in your 3NF relations.arrow_forward
- Proving yourself worthy of being able to handle bigger tasks, the tech lead has decided to give you a challenging job. However, this time, the data that you would be handling is very sensitive and no one wants this data to be leaked. Therefore, instead of getting the entire table, the tech lead has given you the list of attributes that the table contains and the table name. The information given is as follows: You are tasked with building the MySql queries to retrieve the following information: 1. Find the first_name, last_name, email, phone_number, hire_date, and department_id of all the employees with the latest hire_date. 2. Find the first_name, last_name, employee_id, phone_number, salary and department_id of all the employees with the lowest salary in each department. 3. Find the first_name, last_name, employee_id, commission_pct and department_id of all the employees in the department 'DPT007' who have a lower commission_pct than all of the employees of the department…arrow_forwardYou are asked to create a database for a busing company. You need to keep track of the differ- ent buses, each having a plate number, make year, and number of seats. You also need to keep track of drivers that work in the company. The information kept on each driver should include the license number, age, and name. Finally, you need to keep information on which drivers are allowed to drive different buses. Drivers cannot drive any bus, but rather each driver is assigned with specific buses in the fleet he/she is allowed to drive. Accordingly, a driver may be allowed drive many different buses, and a bus can be driven by many different drivers. 2. Convert the ER diagram that is found in the next page to a logical relational data model. Submit in your file the relational model you arrived at (i.e. the list of tables, their columns, indicating their primary and foreign keys). Note: You are NOT asked to create an Access Database, but rather only provide the list of tables you would have in…arrow_forwardWhen doing away with rows, columns, and tables, what is the most critical consideration to bear in mind?arrow_forward
- Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:CengageDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning