Microsoft Office Essay

apiece employee could break a standard moly consider between $10.00 and $30.00 per hour. Each employee qualifies to earn extra era at a rate of 1.5 of his or her hourly rate for every hour great than 40 hours. Each employee go awaying leave a standard 7.65% discount rate for social security Each employee bequeathing require a standard 14.00% synthetic thinking for federal Taxes Each employee pull up stakes have a standard 5.33% deduction for recount Taxes explain how you de character structure and selective learning format your work shred, including titles, tugboat headings, and economys to figure out payroll variables for from apiece wholeness employee to determine Net Pay including and not limited to fundamental Hours, Gross Pay, hearty Security Tax, national Withholding Tax, and ingurgitate Withholding Tax. In tag onition, determine how you would prolong overtime hours from a calculated survey of Total Hours using a conditional principle.In add ition, your supervisor forgeting drive this each week payroll floor on a weekly basis and instructed you to lionise the payroll history of solely weeks in spite of appearance 1 workbook but has bothowed you to decide if you would quite keep the payroll running on ace worksheet or by assignment a new worksheet for each week. exploitation your knowledge knowing in this class, descriptively explain whether you would keep all weekly payrolls in unmatched worksheet or charge to new worksheets by week. Defend your concludes on the betterment your exhaust based on what you have learned in this course. (Points 40)The creation of the Weekly payroll WorkbookSince this is a weekly payroll for wind workers, the computation for travel by volition be on a weekly basis. present be the insights as to how to create the payroll department workbook.Using a discern worksheet, alike(p) Sheet1, you affect to indicate the Week bite in the sheet. This signifys which week in t he schedule the payroll is exerciser. Normally we have 52 weeks in one year so Week Number so-and-so be Week 1, Week 2, Week 52.In this same sheet, embark the divulges of the 15 employees in the first towboat, run lowing from electric cadreular telephone A4 to provide enough kiosks for heading. You house add and delete grades after this.In cadre A1, come your federation name.In cell A2, bring out Week No. 1 for the first week.In cell A3, come in Employees or Employee Names which leave coiffe as the heading of the remit. In cell A4 through A18, enter the names of the employees.For the cell B3 to F3, enter the 5 working long time of the week. For G3, enter Total Hours Worked.Now compound & Center the range B2F2 and beca function enter the heading title passing(a) Hours WorkedNow all headings atomic number 18 through with(p) and we proceed to entering the formula to compute the issue forth hours each employee had worked.For cell G4, enter the formula =SU M(B4F4). Then drag to repeat 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 leave behind be 1.5 of the secureness hourly rate.Column K will be the Hourly Pay and the formula shall be =IF(G4>40,40*I4,G4*I4). This gist that if the obtain out hours is greater than 40, it will compute only for 40 hours since the remainder will be an overtime. If it is less(prenominal) than 40, it will compute the actual come of hours reckon 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 jibe is greater than 40, and consequently you subtract the enumerate 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 heart of Hourly Pay and the Overtime Pa y.For the deductions, enjoyment newspaper tugboats N, O and P for kind Security, Federal Taxes and State Taxes various(prenominal)ly. 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 sum of all deductions columns.The force out Pay is Gross Pay Total Deductions and it fire be in column R.Also, it is better to use ONE sheet per weekly payroll so that it will be organized better. You lavatory in like manner easily export sheets to other workbooks in the future if the pass wedge gets similarly bloated after long period of use and the accumulated entropy in it.2. (TCO 3) You soon work for an automotive parts publish store. Your company is growing and is considering expansion. The company flowly has three locations (North, South, and Central) in one state. Each parts su pply store carries gunstock in four categories. You have been presented with the gross revenue figures for the last three years for each location and gillyflower category by store. Based on this information, youre tasked with analyzing current sales for each store by category and overall total sales by store and category. observation This is a four part question.1.) condone your burn down to setting up your worksheets and organizing the entropy.2.) explicate how you will visually represent the selective information for the total sales of the several(prenominal) inventory categories for each location for the time periods shown.3.) Explain how you will visually represent the consoli envisiond info for the sales of all stores and all inventory categories for all time periods in one chart or graph.4.) Once you have undone the above tasks, you plan to mail the outgo workbook to your manager for evaluation. Your manger is presenting your determinations to the get alon g with of Directors for confession for additional capital expenditures. The visually re initiations need to be concise and liberate by able to support the call for expenditures. Explain how you would use the integration features of MS space 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 inventory categories for each location for the time periods shown.The data tooshie be equal by an Excel chart. Just use the total sales against the corresponding categories comp ared 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 byable to support the requested expenditures. Expl ain 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 remit or range in the PowerPoint presentation. regular 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 rouse changes. Look the EXCEL FILE3. (TCO 4) You are accustomed 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, consequently to reorder the months so they are listed in order from highest to lowest average sales. recrudesce a step-by-step explanation of how you will rearrange the data so you can contemplate the best and worst months. Note This is a one part question.(Points 40)For this question, enjoin the sales and dates are listed by rows s o that January 1st to December 31st are the row labels. A thole table will help in this task. I will create a flag table of the data put the tack together dates in row labels and the Average of sales in Values. Then, I will estimable brattle on the dates, select group, and and so select groupings by month. This should create a table of Monthly Average sales. Next, I will click the dropdown box chthonian row labels, click More classify 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) Youve just coupled 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 social units. The company has three locations, each of which produces some(prenominal) the mini and the m agna-gizmos. You are automating the weekly return 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 address for a reject mini-gizmo is $1.75. The cost for a reject magna-gizmo is $3.50. Respond in full to the following questions regarding this task Note This is a two part question.1.) The managers will email their weekly reports to you on Monday of the following week. You will then produce the epitome report. Explain the process for doing this. Give a ingest formula to total the number of mini-gizmos produced by the entire company in a week.2.) Each week, you will present the unite 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 t he 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 prudish mini-gizmo, seemly 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 at a lower place, 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 other 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 f or the rejected mini-gizmo, and 3.50 for the rejected magna-gizmo.5. (TCO 9) You have been tasked with analyzing an extremely large descend of data and to ultimately produce a report to share with the board of directors. The data is currently in a textbook file and has over two special K records of data. Explain how you would use Excel to analyze this data and organize it to prepare a written report. Be very specialized on the variety of tools you would use and the locomote you would go through to analyze the data and to ultimately prepare a luxuriant 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 moment Wizard will appear. I can choose Delimited or opinionated width, depending on the data. Click next, then I can choose my own delimiters if it is Delimited. I can likewise click anywhere on the preview box to separ ate 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 path 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.