1. (TCO 1) Create a payment account for the employees in a supermarket. To do this maintain a record of the employees information, department, wages and include the number of hours of work invested by them individually. Additionally include the wages per hour to calculate the total daily wages of each worker. Perform the following tasks:
Part 1. Describe how to create an Excel workbook for payroll showing employee name, department, number of hours of work for a week taking into account regular hour and overtime hours (paying one and a half time of salary rate and double the rate for more than 55 hours of work (use appropiate formulas for calculations).
Part 2. Describe how to calculate the total wasges for each employee and grand total for all employees using Excel formulas.
Part 3. Describe how to sort the list in descending order of the total wages.
Part 4. Describe how to apply the concept of 3R's in your worksheet.
Part 5. Describe how to create a chart showing the wages per department.
Question 2. 2. (TCO 3) A retail chain keeps daily data with summaries of the dollar amounts of sales for each of 151 different products sold at 23 retail stores. The daily data is tracked by product and by store. At the end of the day each store sends its data to the sales manager at the corporate office. The data is sent in an Excel spreadsheet. Describe a data consolidation strategy and at least two different types of visualization techniques the sales manager can use to analyze the data received from all stores. Be sure to include formulas that are to be used in the Excel spreadsheet. (Points : 40)
Question 3. 3. (TCO 4) You are an event planner for a large global company. You have been asked to prepare a report on the best time of year to visit one of three different cities: Rome, Miami, and Honolulu. You have researched their temperatures for the entire year and obtained them on a daily basis. You have decided to find the two week period during the year when the temperatures average 72 degrees. Explain in detail how you would develop a spreadsheet to find the average two week temperature and arrange the data so that the two weeks period most close to 72 degrees are presented for each city in the appropriate table. (Points : 40)