Microsoft Office Essay

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) The creation of the Weekly Payroll Workbook Since this is a weekly payroll for construction workers, the computation for Excel will be on a weekly basis. Here are the insights as to how to create the Payroll workbook. Using a separate worksheet, like Sheet1, you need to indicate the Week Number in the sheet. This represents which week in the calendar the payroll is computer. Normally we have 52 weeks in one year so Week Number can be Week 1, Week 2, †¦ Week 52. In this same sheet, enter the names of the 15 employees in the first Column, starting from cell A4 to provide enough cells for heading. You can add and delete rows after this. In cell A1, enter your company name. In cell A2, enter Week No.: 1 for the first week. In cell A3, enter â€Å"Employees† or Employee Names† which will serve as the heading of the table. In cell A4 through A18, enter the names of the employees. For the cell B3 to F3, enter the 5 working days of the week. For G3, enter â€Å"Total Hours Worked†. Now Merge & Center the range B2:F2 and then enter the heading title â€Å"Daily Hours Worked† Now all headings are done and we proceed to entering the formula to compute the total hours each employee had worked. For cell G4, enter the formula =SUM(B4:F4). Then drag to copy this formula up to G18 for the last employee. For I3 enter the heading â€Å"Hourly Rate†. For J3 enter Overtime Rate. Enter the formula for overtime as =I4*1.5 since it will be 1.5 of the regular hourly rate. Column K will be the Hourly Pay and the formula shall be =IF(G4>40,40*I4,G4*I4). This means that if the total hours is greater than 40, it will compute only for 40 hours since the remainder will be an overtime. If it is less than 40, it will compute the actual number of hours multiplied by the rate. Column L is for Overtime Pay and the formula is =IF(G4>40,(G4-40)*J4,0). This means that the computation is done this way: If total is greater than 40, then you subtract the total with 40 and the remainder will be the overtime hours. This will be multiplied by the overtime rate. If it is less than or equal to 40, overtime is set to 0. Column M is for Gross pay and it is the total of Hourly Pay and the Overtime Pay. For the deductions, use columns N, O and P for Social Security, Federal Taxes and State Taxes respectively. The formulas will be: Social Security => =M4*7.65% (Gross Pay times 7.65%) Federal Taxes => =M4*14% (Gross Pay times 14%) State Taxes => =M4*5.33% (Gross Pay times 5.33%) For the total deductions use column Q and the formula shall be =SUM(N4:P4) as the sum of all deductions columns. The NET Pay is Gross Pay – Total Deductions and it can be in column R. Also, it is better to use ONE sheet per weekly payroll so that it will be organized better. You can also easily export sheets to other workbooks in the future if the Excel file gets too bloated after long period of use and the accumulated data in it. 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) For the automotive part: 1) 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. The data can be represented by an Excel chart. Just use the total sales against the corresponding categories compared with the data on store locations. 2) Once you ahve finished the above tasks, you plan to send the Excel workbook to your manager for evaluation. Your manager 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. This is easily done by integrating the actual Excel table or range in the PowerPoint presentation. Even better yet, you can â€Å"embed† an Excel chart with live data in the presentation itself. This is a good approach so that the data in the presentation will change when the data in the actual embedded Excel file changes. Look the EXCEL FILE 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) For this question, suppose the sales and dates are listed by rows so that January 1st to December 31st are the row labels. A pivot table will help in this task. I will create a pivot table of the data; put the Order dates in row labels and the Average of Sales in Values. Then, I will right click on the dates, select group, and then select groupings by month. This should create a table of Monthly Average sales. Next, I will click the dropdown box under row labels, click More Sort Options, and choose Descending Order by Average of Sales. From here on, I can analyze the best and worst months and make preparations in the following year. 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) First, I will consolidate the worksheets into one workbook by doing Home-Cells-Format-Move or Copy sheet. Now, for this data, I will expect the columns to be separated, with labels proper mini-gizmo, proper magna-gizmo, rejected mini-gizmo, rejected magna-gizmo, and row labels of days in the week. After that, I will sum all of the column data below, so I will arrive at the sum of all proper and reject gizmos. Suppose the proper mini-gizmos are in column B and rejected mini-gizmos are in column D, then a sample formula to total all the mini-gizmos produced in a week would be Sum(B2:B8,D2:D8). This is already located in the Summary worksheet, after consolidating the data from four locations. This is assuming that the data is given per week, divided into 7 days. I will do the same for the magna-gismos. Afterwards, just below the Sums, I will put another row labeled Revenue. Then I will multiply the Sums by the respective prices, 3.25 for the proper mini-gizmo, 7.00 for the proper magna-gizmo, 1.75 for  the rejected mini-gizmo, and 3.50 for the rejected magna-gizmo. 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. Since the data is in text file, I have to open Excel first. Then, I will go to File-Open, and then choose All Files. I can now open the text file in Excel. Upon opening, a Text Import Wizard will appear. I can choose Delimited or Fixed width, depending on the data. Click next, then I can choose my own delimiters if it is Delimited. I can also click anywhere on the preview box to separate the data to my liking. After that, I can designate their Text Formats. After clicking finish, I have a whole data of text file in Excel. Since it came from a text file, there may be formatting issues. For example, I will click all the numbers in the data, then Find and Replace spaces with nothing so that the numbers are not in General Format but in Number Format. I will also spellcheck the whole worksheet for grammar and spelling errors. After all this is done, I can now start with the analysis. There are lots of things I can do from summing the data to averaging them to creating pivot tables and even pivot charts. In charts, I can choose to add trend lines and data tables for the charts to be more detailed. After all the analysis is done, I will prepare a written report in MS Office by copy pasting the charts (the visual representations of the analysis) and explaining and interpreting them afterwards. Then, I can provide conclusions and recommendations to complete the analysis.