Question;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 your reasoning's on the approach your take;based on what you have learned in this course.;(Points: 40)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 visually representations need to be concise and clear;by 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)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.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)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.
Paper#36948 | Written in 18-Jul-2015Price : $47