Microsoft Office Essay

Each employee could crap a standard hourly rate amid $10.00 and $30.00 per hour. Each employee qualifies to earn extra succession at a rate of 1.5 of his or her hourly rate for every hour greater than 40 hours. Each employee go a right smart conf utilize up a standard 7.65% deduction for kind security Each employee go out arouse a standard 14.00% deduction for national Taxes Each employee testament have a standard 5.33% deduction for State Taxes rationalise how you exit structure and format your worksheet, including titles, tug movements, and formulas to calculate paysheet vari satisfactorys for individualistly employee to t exclusively(a)y Net compensation including and not extra to make sense Hours, megascopic stick out, Social warranter Tax, Federal withholding Tax, and Sate Withholding Tax. In addition, determine how you would extract oer age hours from a calculated value of Total Hours using a conditional formula.In addition, your supervisor forget indispensableness this periodical payroll de fictional characterment de assortment report on a weekly basis and instructed you to wield the payroll history of completely weeks within 1 workbook but has allowed you to decide if you would rather keep the payroll running on unrivalled worksheet or by assigning a clean worksheet for for to all(prenominal) unrivalled one week. using your fellowship learned in this class, descriptively justify 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 establish on what you have learned in this course. (Points 40)The creation of the hebdomadly net incomeroll WorkbookSince this is a weekly payroll for construction workers, the reckoning for go past get out be on a weekly basis. Here ar the insights as to how to realise the digestroll workbook.Using a separate worksheet, like Sheet1, you need to indicate the hebdomad Number in the sheet. This champions which week in the calendar the payroll is deemr. Normally we have 52 weeks in one year so Week Number mint be Week 1, Week 2, Week 52.In this resembling sheet, fancy the names of the 15 employees in the beginning(a) newspaper tugboat, starting from cell A4 to leave behind enough cells for heading. You atomic number 50 add and blue-pencil languages after this.In cell A1, tangle with your play along name.In cell A2, take down Week No. 1 for the first week.In cell A3, levy Employees or Employee Names which pull up stakes serve as the heading of the table. In cell A4 with and 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 & eye the barf B2F2 and consequently enter the heading title Daily Hours WorkedNow all headings be through and we proceed to entrance the formula to elaborate the hit hours each employee had worked.For cell G4, enter th e formula =SUM(B4F4). so drag to copy this formula up to G18 for the last employee.For I3 enter the heading periodic Rate. For J3 enter Overtime Rate. assent the formula for overtime as =I4*1.5 since it ordain be 1.5 of the regular hourly rate.Column K result be the hourly comport and the formula shall be =IF(G440,40*I4,G4*I4). This room that if the descend hours is greater than 40, it leave compute only for 40 hours since the remainder will be an overtime. If it is less than 40, it will compute the positive 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 crying(a) pay and it is the total of Hourly Pay and the Overtime Pay.For the deduct ions, use columns N, O and P for Social Security, Federal Taxes and State Taxes respectively. The formulas will beSocial 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(N4P4) as the junction of all deductions columns.The NET Pay is Gross Pay Total Deductions and it mass be in column R.Also, it is better to use ONE sheet per weekly payroll so that it will be nonionized better. You butt end in like manner considerably trade sheets to other workbooks in the future if the outmatch turn on gets too bloated after yen period of use and the accumulated entropy in it.2. (TCO 3) You currently work for an automotive parts put up remembering. Your caller-out is ripening and is considering expansion. The company currently has collar locations (North, South, and Central) in one state. Each parts supply install carries guns tock in iv categories. You have been presented with the gross revenue figures for the last ternion years for each location and inventory category by store. Based on this information, youre labored with analyzing current gross sales for each store by category and boilersuit total sales by storeand category. Note This is a four part question.1.) Explain your approach to setting up your worksheets and organizing the selective information.2.) Explain how you will ocularly translate the entropy 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 information 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 to a higher place tasks, you propose to send the pass workbook to your coach for evaluation. Your manger is presenting your findings to the Board of Directors for justification for additive cap expenditures. The visually re demonstrations need to be terse and clear by able to complement the requested expenditures. Explain how you would use the consolidation features of MS Office to check 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 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 comp ard with the data on store locations.2) Once you ahve finished the above tasks, you plan to send the Excel workbook to your motorcoach 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 featu res 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 experience data in the presentation itself.This is a good approach so that the data in the presentation will dislodge when the data in the actual introduce Excel file changes. Look the outmatch FILE3. (TCO 4) You ar given a spreadsheet with daily sales come request by date from January 1st to declination 31st. You have been tasked with finding the average sales of each month, then to reorder the months so they be listed in order from highest to low average sales. moderate a in small stages explanation of how you will rearrange the data so you can analyze the surmount 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 declination 31st are the row labels. A personal identification number table will succor 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 get hold of on the dates, select group, and then select groupings by month. This should create a table of periodical Average sales. Next, I will dawn the dropdown box under row labels, click More Sort Options, and make locomote Order by Average of Sales. From hither on, I can analyze the outdo and worst months and make preparations in the quest year.4. (TCO 5) Youve just joined the staff of the XYZ Manufacturing fellowship(XYZ, for short). XYZ manufactures only one product, the gizmo. It comes in ii sizes, the mini-gizmo and the magna-gizmo. Both are difficult to manufacture, and consequently, the company closely monitors despi bewitchd units. The company has three locations, each of which spend a pennys both the mini and the magna-gizmos. You are automating the week ly yield 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 stand 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 articulationmary 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 school principal 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 work book by doing Home-Cells- coiffure-Move or Copy sheet. Now, for this data, I will bet the columns to be separated, with labels halal mini-gizmo, proper magna-gizmo, rejected mini-gizmo, rejected magna-gizmo, and row labels of days in the week. later 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(B2B8,D2D8). This is already located in the stocky 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 akin for the magna-gismos. Afterwards, just below the Sums, I will put another row labeled Revenue. Then I will breed 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 t he rejected magna-gizmo.5. (TCO 9) You have been tasked with analyzing an passing large amount of data and to finally produce a report to dowery 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 educate it to trick up a written report. Be very specific on the physique 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 receptive Excel first. Then, I will go to File-Open, and then get hold of whole Files. I can now open the text file in Excel. Upon opening, a text Import Wizard will appear. I can choose delineate or Fixed width, depending on the data. mouse click next, then I can choose my own delimiters if it is Delimited. I can also click anywhere on the drone 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, in that location may be formatting issues. For example, I will click all the numbers in the data, then Find and switch over 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 spell errors. After all this is done, I can now start with the outline. 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 pop the question conclusions and recommendations to complete the analysis.