Description of this paper

Devry BIS155 final exam Summer 2014




Question;Question 1. 1. (TCO 1) You work for a local construction;firm, "DeVry Engineering Group" and your supervisor wants to test;your knowledge and skills with Microsoft Excel and has instructed you to;develop a spreadsheet to calculate weekly payroll for ?15? employees with the;following assumptions;Note: This is a one part question.;? Each employee could have a standard hourly rate between;$10.00 and $30.00 per hour.;? Each employee qualifies to earn overtime at a rate of 1.5;of his or her hourly rate for every hour greater than 40 hours.;? Each employee will have a standard 7.65% deduction for;social security;? Each employee will have a standard 14.00% deduction for;Federal Taxes;? Each employee will have a standard 5.33% deduction for;State Taxes;Explain how you will structure and format your worksheet;including titles, column headings, and formulas to calculate payroll variables;for each employee to determine "Net Pay" including and not limited to;Total Hours, Gross Pay, Social Security Tax, Federal Withholding Tax, and Sate;Withholding Tax. In addition, determine;how you would extract overtime hours from a calculated value of "Total;Hours" using a conditional formula.;In addition, your supervisor will need this weekly payroll;report on a weekly basis and instructed you to keep the payroll history of all;weeks within "1" workbook but has allowed you to decide if you would;rather keep the payroll running on one worksheet or by assigning a new;worksheet for each week. Using your;knowledge learned in this class, descriptively explain whether you would keep;all weekly payrolls in one worksheet or assigned to new worksheets by;week. Defend the approach you take based;on what you have learned in this course.;(Points: 40);Question 2. 2. (TCO 3) You currently work for an automotive;parts supply store. Your company is;growing and is considering expansion.;The company currently has three locations (North, South, and Central) in;one state. Each parts supply store;carries inventory in four categories.;You have been presented with the sales figures for the last three years;for each location and inventory category by store. Based on this information, you're tasked with;analyzing current sales for each store by category and overall total sales by;store and category.;Note: This is a four part question.;1.) Explain your approach to setting up your worksheets and;organizing the data.;2.) Explain how you will visually represent the data for the;total sales of the individual inventory categories for each location for the;time periods shown.;3.) Explain how you will visually represent the consolidated;data for the sales of all stores and all inventory categories for all time;periods in one chart or graph.;4.) Once you have finished the above tasks, you plan to send;the Excel workbook to your manager for evaluation. Your manger is presenting your findings to;the Board of Directors for justification for additional capital expenditures. The visual representations need to be concise;and clearly able to support the requested expenditures. Explain how you would use the integration;features of MS Office to incorporate the Excel information into other;presentation media.;(Points: 40);Question 3. 3. (TCO 4) You are given a spreadsheet with;daily sales numbers ordered by date from January 1st to December 31st. You have;been tasked with finding the average sales of each month, then to reorder the;months so they are listed in order from highest to lowest average sales. Give a step-by-step explanation of how you;will rearrange the data so you can analyze the best and worst months.;Note: This is a one part question.;(Points: 40);Question 4. 4. (TCO 5) You've just joined the staff of the;XYZ Manufacturing Company (XYZ, for short). XYZ manufactures only one product;the gizmo. It comes in two sizes, the mini-gizmo and the magna-gizmo. Both are;difficult to manufacture, and consequently, the company closely monitors;rejected units. The company has three locations, each of which produces both;the mini and the magna-gizmos. You are automating the weekly production reports;so that you can easily calculate total production for the entire company each;week.;The mini-gizmo is priced at $3.25 per unit. The magna-gizmo;is priced at $7.00 per unit. The unit cost for a reject mini-gizmo is $1.75.;The cost for a reject magna-gizmo is $3.50. Respond fully to the following;questions regarding this task;Note: This is a two part question.;1.) The managers will e-mail their weekly reports to you on;Monday of the following week. You will then produce the summary report. Explain;the process for doing this. Give a sample formula to total the number of mini-gizmos;produced by the entire company in a week.;2.) Each week, you will present the combined report to your;boss, who wants to see both the summary and the individual sheets for each;location. You want to add a header with the date and your name to each page.;What is the easiest way to do this? Explain the process.;(Points: 40);Question 5. 5. (TCO 9) You have been tasked with analyzing;an extremely large amount of data and to ultimately produce a report to share with;the Board of Directors. The data is currently in a text file and has over two;thousand records of data. Explain how;you would use Excel to analyze this data and organize it to prepare a written;report. Be very specific on the variety of tools you would use and the steps;you would go through to analyze the data and to ultimately prepare a detailed;report with recommendations.;Note: This is a one part question. (Points: 40)


Paper#36718 | Written in 18-Jul-2015

Price : $42