BIS 155 Final Exam New

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.

(Points 40)

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)

Question 4.4 (TCO 5) Michael and Sally own 89 ice shops across the southeastern United States. In efforts to manage sales data collected from these ice cream shops, Michael and Sally currently have several workbooks developed with the same purpose and structure but with so much information, Michael and Sally need help consolidating this data for more efficient decision making. Given the needs of Michael and Sally, describe in detail how you would explain to Michael and Sally how to use Excel to develop and produce summary reports with consolidated sales data displaying periods of weekly, monthly, and quarterly reports along with visual options to trend and compare data across all ice cream shops. Be sure to include sample Excel formulas. Also, explain in detail the benefits of using consolidated summary reports in the decision making process. (Points 40)

5. (TCO 9) Note: This question has three parts:

1. As the Sales Manager of a global company, you would like to use MS Excel to analyze the sales data for the entire company. The first challenge is, the Sales Data System cannot export its data as a MS Excel Workbook. It can however, generate a TXT file. Please explain how you would get the data into Excel for analysis.

2. As the Sales Manager, decide two things you would like to know from the sales data (data available: country, product, price per product, date product was sold, quantity sold, sales person). Then select two features you would use to analyze the data to provide you with the information you desire. Please be clear on what two things you would like to know and how you will use the selected feature to provide that information.

3. With the information now known, please describe how you would present this information to senior management. Please be specific and be sure to consider the audience. (Points 40)