I have a school project I need done. This is the description. I have 12 excel sheets to go along with this that has dats. How many hours would this take you
Management Information Systems
Spring 2013 Project
Gottlieb Mowers, Inc.
Note to student: Do not procrastinate. Read the project during the first week and start working on the project during the second week of class. If you wait until the last week of class to start, there is a good chance that you will not get the project completed. In addition, attention to detail in any business is critical. The same applies to this project. Be sure to pay attention when entering formulas and data; ensure that the correct cell(s) are used.
Gottlieb Mowers, Inc. is a medium sized company located in Frankfurt, Germany. The company has been in business for three years. The company experienced moderate profits the first two years in business; however, a downward trend started in the 4th quarter of the second year. The decline continued during the third year. The President of the company is at a loss. Metrics show a loss in revenue due to quality defects. The Quality Manager provides reports on a monthly basis showing defects measured in Parts per Million (PPM). The Quality Manager has ensured the President that the factory has focused on reducing defects with the highest PPM. The defects include missing spark plug, noisy motor, and missing warning labels.
You work for a consulting firm who specializes in assisting companies with financial difficulties. Gottlieb Mowers, Inc. has hired your firm. Your firm’s role is to review the companies Quality Metrics and determine if it is focusing on reducing the defects that have the highest financial impact.
Gottlieb Mowers, Inc. sent your firm the defect raw data for 2012 in csv format. The raw data includes the number of defects and production numbers for each month. The data show that the company operates on three shifts. Your firm will need to analyze the data to determine if Gottlieb’s efforts have been focused on reducing the correct defects. Along with the raw data, Gottlieb’s Quality Manager sent you the company's goal for each defect.
Since Gottlieb Mowers is familiar with Excel, your firm will need to create an Excel workbook to analyze the data. The workbook should include the following worksheets: Set Up, Executive Summary, one for each month of the year, each quarter of the year, and a year-to-date.
Since you are enrolled at a local college in an Information System course focused on Excel, your firm has chosen you to lead this project. Your supervisor has challenged you to design a dynamic workbook that Gottlieb can use in subsequent years without creating new ones.
Open Microsoft Excel. The default number of worksheets in an Excel workbook is three. Rename Sheet1 to “Set Up.” The Set up worksheet needs to include the name of the company, the report name, and the current year. In addition, the Set Up worksheet needs to list the defects with their respective goals. The defects and goals should be set up in columns. You will use this to populate the other worksheets.
Example of Set Up Worksheet:
Gottlieb Mowers, Inc.
Stripped Head Bolt
Enter Set Up table data in cells B3:D6 and enter the defects and goals into B9:D34. The defects and goals data are located in the [url removed, login to view] file. Be sure to enter the data correctly. If the proper cells are not used, the project will not be correct.
Suggestion: Build the Jan worksheet completely, then copy the worksheet and rename for the subsequent months, quarters, and YTD.
The monthly worksheets should show the number of defects and the PPM per defect by shift. In addition, the monthly worksheet should have a table that displays the monthly production by shift. The worksheets should total the number of defects by shift, by defect, and show the overall total defects and production. See the example below for what the Defect Table should look like:
Spark Plug Wire Short
Monthly Worksheets (continued):
Right click on the Sheet2 tab, choose rename, and type Jan.
In cell B2, enter “Lawn Mower Defects.”
In cell B3, use the concatenate formula to display the month and current year. By using this formula, your client can recycle the workbook for the following year. When the year in C6 on the Set Up worksheet is changed, the change will be reflected in B3 on the month worksheet.
As you work in Excel, you will learn that there are multiple methods of completing tasks. You can enter the formula by typing in the formula bar, or by clicking on the function button: (reference EX 131).
Create the defect table in cells B5:K32. Use what you learned in Tutorial One to format the defect table with borders and shading. The shift column headings should be in C, E, and G. There are different methods for centering the shift titles across the QTY and PPM columns. For example, you can select adjacent cells C5:D5 and click on the Merge and Center button:
The example on page 2 shows the position of the title “1st Shift” in relation to the titles “QTY” and “PPM.”
Another method that can be used to center the headings is to select adjacent cells C5:D5, right click on the selected range, choose Format Cells, click on the Alignment Tab, and choose Center Across Selection in the Horizontal drop down box as show on the following page.
Monthly Worksheets (continued):
The remaining column headings should be entered in B6:K6. Once the headings are in place, you are now ready to display the defect titles in cells B7:B31. Use what you learned in Tutorial 6 to display the defect names. To display the defect names, click in cell B7, and enter “=”, then click on the Set Up worksheet tab, click in cell B10, and then enter on your keyboard. This enters the formula to display the first defect.
Next, you can use the Fill Handle to display the remaining defect names. The Fill Handle is discussed on page EX 141.
Perform the same methods to display the Goal values in cells K7:K31.
Next, create a table to display the production numbers for the three shifts in cells M6:N11. The table should look like:
Next, center “Production Quantity” across cells M6:N6.
The next step is to set up the formulas to calculate the totals and PPM. What is PPM? PPM is a metric that businesses use to calculate quality performance. A lower PPM indicates a lower defect rate. To calculate PPM, divide the number of defects by the amount produced, and then multiply by 1,000,000. For example, if the company produces 25 noisy lawn mowers on 1st shift, and the total produced was 160,000, the calculation would be 25/160,000*1,000,000 = 156 PPM. To calculate PPM for Noisy mowers on first shift, enter “=C7/N7*1000000” in cell D7. The result will be “#DIV/0!” Since there is not a quantity in N7, Excel returns a divide by zero error. To prevent this error from displaying, revise the formula to: =IF(ISERROR(C7/$N$7*1000000),"",C7/$N$7*1000000).
The above revised formula uses the IF and ISERROR functions; therefore, since the formula C7/N7*1000000 creates a divide by zero error, Excel displays nothing in D7. If the formula did not result in an error, then Excel would perform the calculation.
Pages EX 120 and EX121 discuss relative and absolute references and page EX 125 discusses mixed references. Notice that the revised formula contains a mixed reference. In this case, you will use a mixed reference because you will use the Fill Handle to copy the revised formula in D7 through to D31. When copying through to D31, the mixed reference will allow the row number reference in the numerator to increment by one, but the row number reference in the denominator will remain the same. For example, the formula C8/$N$7 becomes C9/$N$7, C10/$N$7, C11/$N$7, etc. as it is copied into subsequent cells.
Enter formulas to calculate PPM values for all of the defects on all shifts. Be sure to reference the correct cells for the production numbers for the applicable shifts.
Next, enter a formula to calculate the total noisy lawn mowers. Earlier, it was discussed that there are many ways to perform tasks in Excel. Likewise, different formulas can be used to display the same result. For example, “=SUM(C7,E7,G7)” and “=C7+E7+G7” will display the same result. Enter one of these formulas in cell I7. Use the Fill Handle to copy the formula through to I31. In cell I32, enter a formula to calculate the total defects.
Next, enter formulas to calculate the totals for each shift. Example, in cell C32 enter “=SUM(C7:C31)”. Then copy and paste special (formulas) in cells E32, and G32. For the total PPM for each shift, divide the total defects by the total production for the shift and multiply by one million. Where formula errors are displayed, enter functions that will not show the error, but will perform the calculation if there is no error.
The next step is to apply conditional formatting that will alert Gottlieb when any defect PPM goes over the established goal. Conditional formatting is discussed starting on EX95. To apply conditional formatting, select adjacent cells J7:J32. On the Home tab, in the Styles group, select Conditional formatting, Highlight Cells Rules, and then click Greater Than as shown on the following page.
After clicking on Greater Than, the following window will appear:
Click on the button that is circled above. Then click in cell K7 and the Enter on your keyboard. Next, click on the “with” drop down box and choose Custom Format… In the Font Style list box, choose bold. In the color drop down box, choose white. Click on the Fill Tab and choose red under the background color list. Then choose ok. Next click in cell J7, right click, and copy. Select range J8:J32 and paste special (Formats).
The picture on the following page displays what the excel worksheet should look like at this point.
The next stop is to open the [url removed, login to view] file, copy, and paste the shift data into the applicable cells. Next open the Production .csv file, copy, and paste the production numbers for January. Noisy, Spark Plug Missing, and Missing Paint should be flagged by the conditional formatting as over goal.
Notice that Throttle Cable Stuck displays a value of 5.5. Since it is impossible to have a half of a defect, data validation will be applied to prevent erroneous data from being entered. Select nonadjacent cells C7:C31, E7:E31, and G7:G31 and then click on the Data Tab and choose Data Validation and then choose Data Validation as shown below. Selecting nonadjacent cells is discussed on page EX25 and data validation is discussed starting on page EX438.
The following window appears:
To prevent decimal numbers from being entered chose Whole Number in the Allow drop down box. To prevent negative numbers from being entered, choose greater than from the Data drop down box and enter -1 in the Minimum field.
Next, to display a message when the user clicks in one of the selected cells, click on the Input Message tab. Enter a title and an input message that gives the user some instruction. For example, a title might be “Defect Quantity” and the input message might be “Only whole numbers great than -1 are allowed.” To display an error alert if the user enters invalid data, click on the Error Message tab and select stop in the Style drop down box. Enter a title and error message. Test the data validation by entering a negative number or a decimal number. The error will look similar to the picture below:
Be sure to change the value in C19 from 5.5 to 5.
After finished setting up the table, you show your supervisor and she makes a suggestion to add a Pareto Chart.
“A Pareto chart is a bar graph. The lengths of the bars represent frequency or cost (time or money), and are arranged with longest bars on the left and the shortest to the right. In this way the chart visually depicts which situations are more significant.”
Source: [url removed, login to view]
The Pareto chart below was created from sample lawn mower defect data.
By looking at the Pareto chart above, one can quickly see that Spark Plug Missing, Missing Paint, and Noisy represent over 81% of the issues. This helps the user to direct improvement efforts.
Note to Student: In the next section of the project, you will work with recording macros and editing Visual Basic for Applications (VBA) code. Macros and VBA make Excel a very powerful tool; however, recording complicated macros can be frustrating if you mess up a step. Below are detailed instructions for recording a macro, which will be used to automatically build a table for the Pareto Chart.
Recording macros can take some practice. If you mess up, delete the macro and start over. When recording a macro, Excel actually writes VBA code. After recording the Macro and inserting a chart, you will revise the code to set the primary and secondary axis, and the minimum and maximum values for the Pareto chart.
Macros are discussed in Tutorial 8 on page EX 454.
Before you can record a macro, you will need to display the Developer Tab. To do this in Excel 2010, click the File Tab, Options, and Customize Ribbon. On the right side, with Main Tabs selected in the Customize Ribbon drop down box, check Developer. See the picture on the following page.
Recording the Macro:
Click the Developer Tab and choose Record Macro. Notice that the Record Macro changes to Stop Recording.
The following window appears:
Change the macro name to MakeParetoTable. Type the name of the macro with no spaces and capitalize the first letter of each word; this is called camel casing. When recording macros or writing VBA code, name objects, variables, etc using camel casing. An alternative way to name a macro is Make_Pareto_Table. Creating names without spaces makes referenceing them in VBA code much easer. In addition, variable names cannont have spaces. So what is a variable? It is a memory location used to store data.
Next, click OK. Once OK has been clicked, Excel records every action performed. Select nonadjacent cells ranges B6:B31 and I6:I31. Right click on the selection and copy. Right click in cell P6 and paste special, values. Double click between columns P and Q to auto fit the contents that were just pasted. Select range P6:Q31. Click the data tab. Click the sort button shown below.
After clicking Sort, the window below appears. Ensure that Total is selected in the Sort by field, that Values is selected in the Sort On field, and ensure that Largest to Smallest displays in the Order field.
Select the range P6:Q32 and add borders.
In cell Q32, enter =SUM(Q7:Q31).
Enter the formula =Q7/$Q$32 in cell R7. Use the fill handle to fill this formal down through cell R31. This calculates the defect rate. Next,you will enter formals that will calculate values that you will use for the secondary axis of the Pareto Chart. This is the line that displays the accumlative percentage. Enter =R7 in cell S7. Then enter =S7+R8 in cell S8. Use the fill handle to copy the formual in S8 through to cell S31. If the formuals were entered correctly, the value in S31 will be 1.
Click on the developer tab and click the Stop Recording button as shown below:
The code created during the record process needs to be edited. Excel related the code to the Jan worksheet. Since you will use the Jan worksheet to copy and create the remaining months and Qtr worksheets, you need to remove the “Jan” reference and make the code generic. On the Developer Tab, in the Code Group, click the Visual Basic Button. The macro recorded should be located in Module 1. Find the the section of code shown on the following page: Notice the highlighted reference to Jan.
[url removed, login to view] = False
[url removed, login to view]("Jan").[url removed, login to view]
[url removed, login to view]("Jan").[url removed, login to view] Key:=Range("Q7:Q31"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With [url removed, login to view]("Jan").Sort
The next step is to edit the code. Add the following line as the first line in the macro.
Dim strActiveWorkSheet as String
Type the following right above the code below.
strActiveWorksheet = [url removed, login to view]
The replace “Jan” with strActiveWorksheet
Next, you will add a command button and assign the marco to that button. Click on the devloper tab and click on Insert. Choose the Command Button as shown below.
Place the command button near cell P2. Right click on the button and choose Properties. The Properties window will display. In the Properties window, click in the Name field. Type cmdMakePareto. Programmers create names for objects such as command buttons by using naminng conventions. Below is a list of some objects and the prefix used for naming conventions:
Example: Start the object name by typing the naming convention followed by the object name. For example, chkFormat. This would be the name of a check box that performs a format action.
Change the caption on the command button by clicking in the caption field of the command button’s property window. Type Transfer and Sort. Notice that the caption on the button changes. Close the command button’s properties window.
Next, you will assign the macro created above to the new command button. Right click on the command button and choose View Code. The Visual Basic Editor window will open as shown on the following page. The first line in the window starts with Private Sub cmdMakePareto_Click(). This line starts the sub procedure. The code that is written between the first line and the second like will excecute when the button is clicked. Currently, there is no code between the Private Sub line and the End Sub line. To get the button to excute the MakeParetoTable macro, type Call MakeParetoTable in between the first two lines of code.
Visual Basic Editor Window.
The revised code is shown below.
Try the command button. Delete the data in range P6:S32. Then click on the command button. Click in cell V1 to deselect the currently selected range.
There is only one thing left to do. Insert a graph and revise the VBA code to set the primary and secondary axis. Click on the Insert Tab, Column, then choose the clusted column.
Insert the graph near cell B36. Right click on the chart and choose select data. The following window appears. Click on the Add button.
This will display the Edit serious window. Click on the button that is circled below and select range Q7:Q31 and then click OK.
Next, click the Add button again and select range S7:S31. Next, click on Series 1 as shown on the following page.
After clicking the Edit button, select range P7:P31. Then click OK.
The chart should look like the following at this point:
Notice that both series are displayed. Click on the chart. The Chart Tools Group will be displayed. Click on the Format tab find the Current Selection Group. In the drop down box, choose series 2. Series 2 is going to become the accumulative line in the Pareto Chart.
After choosing Series 2, click the Format Selection Button. The Format Data Series window will appear.
Choose Secondary axis under Plot Series On. Click close. The graph will now look like the following:
Next, right click on one of the bars for series 2 and select Change Series Chart Type. In the Line section of the Change Chart Type window, choose the Line with Markers type and click OK. Next, right click one of the bars for series 1 and choose Format Data Series. Change the Gap Width to 0. Click the Layout tab, Chart Title, and then choose Above Chart. Enter an appropriate title. Next, click on the Axis Titles button. Choose Primary Veriticle Axis. Choose Rotated Title. Name the axis QTY. Click on the legends button and choose None. Right click the seconday verticle axis and choose Format Axis. Click Number, then Percentage and change the Decimal places to 0. Then click Close. Next, Click the Insert tab, Text Box, and place the text box under the title of the chart. With the insertion point in the text box, click in the formula bar, enter = and then click in cell B3. Then enter on your keyboard. Center the text box under the chart title.
The last step will use VBA to assign values to the primary and secondary axis. You will edit the VBA code to accomplish this. Right click on the command button and choose view code. The Visual Basic Editor window appears. Right click on MakeParetoTable and choose Definition. This will take you to the code.
Scroll to the bottom of the code and place the insertion point above the End Sub line and enter the following:
[url removed, login to view]("Chart 1").Activate
With [url removed, login to view](xlValue, xlPrimary)
.MaximumScale = Range("Q32")
.MinimumScale = 0
With [url removed, login to view](xlValue, xlSecondary)
.MaximumScale = 1
.MinimumScale = 0
Congratulations. You are almost finished!
You will need a worksheet for each month of the year as well as each quarter and one for year-to-date.
Right Click on the Jan worksheet and choose Move or Copy.
Copy the Jan worksheet as many times as needed. Rename the works sheets Feb, Mar, QTR 1, etc. Enter formulas in the quarter worksheets to add the quarterly data. Do the same for the YTD worksheet. Tutorial 6 discusses working with multiple worksheets. Make sure that you replace the Jan data on the new worksheets with the data that is in the csv files for Feb, Mar, Apr, etc and click Transfer and Sort on each of the new worksheets.
Analyze the data on each of the worksheets, with special attention to the year-to-date data. What do you think? Should Gottlieb Mowers be focused on reducing the number of defects for Spark Plug Missing, Noisy, and Warning Label Missing?
It depends on how much it costs to repair each of the defects. For example, Spark Plugs Missing occurred more than any other defect, but the cost to repair a missing spark plug would be far less than repairing a locked up motor. With this in mind, you decide to ask Gottlieb for the cost of each defect.
You ask a colleague to help you design an Executive Summary worksheet that will provide Gottlieb the analysis to make better decisions. The first step is to add the Average Cost of Repair to each defect on the Set Up worksheet and insert a worksheet for the Executive Summary. Your colleague worked on this project over the weekend and placed an Excel Workbook on the company network for you to download.
Download the workbook named [url removed, login to view] from Blackboard. Open the new workbook. Ensure that your workbook is open as well. The new workbook has three worksheets, the Executive Summary worksheet, a new Set Up sheet that details the repair costs for each defect, and a Current Month Worksheet. Right click on the Executive Summary worksheet and choose Move or Copy. In the To book drop down box, choose your workbook name. This copies the worksheet to your workbook. Do the same for the remaining worksheets. You will have to rename your Set Up worksheet to Set Up Old and rename the new one to Set Up.
On the Executive Summary worksheet, click the Fix Formulas button. Note: When copying worksheets that have formulas with references to other worksheets into a new workbook, Excel transfers the workbook name in the formulas. The Fix Formulas button removes the reference to the previous workbook. The Fix button uses VBA code to enter the correct formulas, which do not contain references to the ExecSummary workbook.
Click the Transfer button on the Executive Summary worksheet. Analyze the data. Do you have the same conclusion? Should Gottlieb Mowers be focused on Spark Plug Missing, Noisy, and Warning Label Missing based on their PPM values or should they be focused on the defect with the highest repair value.
Write a 2-3 page report to Gottlieb Mowers and explain your analysis and the workbook that you created. Embed the Pareto Chart on the Executive Summary worksheet. When embedding objects in a report, introduce the object, display it, and then discuss it.
King College© 2012
Information Systems for Decision Making Project
Page 22 of 22