#### Details of this Paper

##### Devry BIS155 final exam Spring 2014

Description

solution

Question

Question;Page 1;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#36940 | Written in 18-Jul-2015

Price : \$52