Microsoft Office Essay

distributively 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 give have a standard 7.65% deduction for social security Each employee go forth have a standard 14.00% deduction for Federal Taxes Each employee give have a standard 5.33% deduction for State Taxes justify how you leave structure and format your worksheet, including titles, column impulsions, and formulas to calculate payroll variables for for each one employee to determine Net Pay including and not limited to natural Hours, consummate(a) Pay, Social trade protection 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 entrust need this weekly payroll report on a weekly basis and instructed you to keep the payroll history of every in all weeks within 1 workbook but has allowed you to decide if you would rather keep the payroll running on integrity worksheet or by assigning a cuttingfangled 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 reasonings on the approach your take based on what you have learned in this course. (Points 40)The creation of the Weekly payroll WorkbookSince 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 defecate the Payroll workbook.Using a separate worksheet, like Sheet1, you need to indicate the Week Number in the sheet. This lives which week in the calendar the payroll is computer. usually we have 52 weeks in one year so Week Number digest be Week 1, Week 2, Week 52.In this alike(p) sheet, enter the names of the 15 employees in the first Column, starting from cellular telephone A4 to provide enough cells for heading. You can add and delete rows after this.In cell A1, enter your club 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 hold over. 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 B2F2 and then enter the heading title Daily Hours WorkedNow all headings are do and we proceed to get into the formula to compute the positive hours each employee had worked.For cell G4, enter the formula =SUM(B4F4). 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 P ay and the formula shall be =IF(G440,40*I4,G4*I4). This means that if the bring 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(G440,(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, drop columns N, O and P for Social Security, Federal Taxes and State Taxes respectively. The formulas will beSocial Security = =M4*7.65% (Gross Pay measure 7.65%)Federal Taxes = =M4*14% (Gross Pay times 14%)State Taxes = =M4*5.33% (Gross Pay times 5.33%)For the total deductions ingestion column Q and the formula shall be =SUM(N4P4) 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 apply 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 selective information 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 catalogue 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, youre tasked with analyzing current sales for each store by category and overall total sales by storeand category. Note This is a four part question.1.) Exp lain 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 carriage 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 part1) Explain how you will visually represent the data for the total sales of the individual inve ntory categories for each location for the time periods shown.The data can be represented by an Excel chart. honest 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 charabanc 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 byable 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 ripe(p) approach so that the data in the presentation will change when the data in the actual embedded Excel fil e changes. Look the pass by FILE3. (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 fair 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 surpass 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 manufacture a pivot table of the data put the Order dates in row labels and the Average of Sales in Values. Then, I will right poky 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 at a lower place row labels, click More Sort Options, and choose Descendin g 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) Youve 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 spurned 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 richly 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 fol lowing week. You will then produce the thick 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. later on that, I will sum all of the column data under, 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(B2B8,D2D8). 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 forthe 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 pen 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 particular 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 delimitate 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 summ ing 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 understand them afterwards. Then, I can provide conclusions and recommendations to complete the analysis.