Do you know the GANTT chart helps us to do follow up for the events scheduled in the plan.
In excel the we can create the GANTT chart. Click below to down load the GANTT chart which would help to build a chart for your project.
GANTT chart - Down load the excel file
GANTT chart - Download excel file to work
When a project is under way, Gantt Charts help you to monitor whether the project is on schedule. If it is not, it allows you to pinpoint the remedial action necessary to put it back on schedule.
Sequential and parallel activities:
An essential concept behind project planning is that some activities are dependent on other activities being completed first. As a shallow example, it is not a good idea to start building a bridge before you have designed it!
These dependent activities need to be completed in a sequence, with each stage being moreor-less completed before the next activity can begin. We can call dependent activities 'sequential' or 'linear'.
Other activities are not dependent on completion of any other tasks. These may be done at any time before or after a particular stage is reached. These are nondependent or 'parallel' tasks.
To draw up a Gantt diagram (Gant diagram), follow these steps:
1. List all activities in the plan
2. Head up graph paper with the days or weeks through to task completion
3. Plot the tasks onto the graph paper
4. Schedule Activities
5. Presenting the Analysis
Down load the excel sheet and fill up the activities and dates in the respective columns. The excel will plot the gantt chart for you.
Please give your comments on this.
Key points:
Gantt charts are useful tools for planning and scheduling projects. They allow you to assess how long a project should take, determine the resources needed, and lay out the order in which tasks need to be carried out. They are useful in managing the dependencies between tasks.
When a project is under way, Gantt charts are useful for monitoring its progress. You can immediately see what should have been achieved at a point in time, and can therefore take remedial action to bring the project back on course. This can be essential for the successful and profitable implementation of the project.
Audit template with Evaluation Radar Chart
The below audit work sheet template will be usefull for your process. Audit work template - Download xls file Fill the questioner value and Audit chart ready - Excel file Pre-Production |
|
| Pre-Production relates to activities that occur prior to the creation of the product or service being sourced at the Supplier. | |
| 1 | Does the
supplier have all latest ABB drawings and specs? Were they internally
approved and distributed? Should include disposal of obsolete documents |
| 2 | Does
the Quality File include all specific ABB required documents? Refer to Supplier Requirements for list of documents, I.e. Design Review docs |
| 3 | Are open engineering issues addressed with an appropriate action plan? |
| 4 | How
does the supplier incorporate learning from field experiences into their
engineering activity? Improved product design, improved processes... |
| 5 | Does the Supplier have a well defined process flow chart? |
| 6 | Has
a Process Failure Mode Effects Analysis been performed? Are the corrective
actions documented, implemented and effective? Applicable if ABB has requested a Process FMEA. Check that FMEA indices are reviewed and updated on a regular basis |
| 7 | Has the supplier approved internally its process before submitted Control Plan to ABB? |
| 8 | Has
Control Plan been submitted and accepted by ABB? Check that last revision has been incorporated. |
| 9 | Are there any major differences between the Control Plan and the actual process and product/process controls? |
| 10 | Has process capacity study been completed? Is there adequate capacity to produce the ABB deliverable? |
| 11 | Have bottlenecks been identified and addressed? |
| 12 | Does
the supplier have adequate containment plans for first production runs; if
not in-house, is there an external containment plan in place? Containment plans are necessary to ensure that initial production runs meet all requirements |
| Sub-Supplier Management | |
| 13 | Does
the Supplier have a effective sub-supplier approval process? Check the sub-supplier corrective action requests... |
| 14 | Does the supplier maintain a list of approved sub-suppliers for all components, materials and services? |
| 15 | How
does the supplier express ABB requirements in their Purchase Orders? Such as: material specifications, important dimensions and tolerances, restricted substances, … Should include ABB approved suppliers if required. |
| 16 | Have
all purchased components, materials and services from sub-suppliers
been approved by the supplier through a formal process? Check Initial Samples reports for each component and material |
| 17 | In case of subcontracting, did the supplier audit the sub-supplier process, and does the sub-supplier deliver a Conformance Report to the supplier? |
| 18 | Does
the supplier perform an Incoming Inspection? Is it according to the Control
Plan? Non-conformances effectively handled in corrective action system? |
| 19 | Are
incoming lots isolated until they have been approved? Check that sampling rules and decision criteria are clear |
| 20 | How
does the supplier handle special lots? Such as: Initial Samples, Prototypes, Trial Orders, … |
| 21 | Is there a formal process for the self-certification status? Are there random checks in place for self-certified components and materials? |
| 22 | How
does the supplier document the receipt of materials and services? Receipts are in local business system and includes quantity, lot numbers, receipt date, …information that supports part traceability |
| 23 | Is
there a sub-supplier performance-Quality, Delivery, monitoring process
defined and used for improvement? Also check the criteria and responsiveness of sub-suppliers in case of customer complaints, Sub-supplier corrective action requests. |
| 24 | Are
customer supplied products controlled? Applicable in case ABB provides products to be assembled in the final product |
| Production/Service Execution | |
| 25 | Is
there a formal start-up procedure? First part should be approved. In case of a long stoppage in operations, start-up procedure should be applied |
| 26 | Are
operating and inspection instructions clearly posted at point of operations,
and are they known by operators? Check that they are in conformance with Product/Process Control Plan. Do they adequately detail how to perform the operations and what to inspect? |
| 27 | Are
all special characteristics checked by a mistake proofing or process control
methods like Statistical Process Control? Applicable in case the component has special characteristics. If not controlled by mistake proofing, are containment plans and corrective actions in place? Is this documented in the Control Plan? |
| 28 | Are
customer approved master samples available at required workstations? Applicable if ABB has requested it. |
| 29 | Are
accredited labs used to perform testing? Applicable when testing is requested by ABB. Check that labs used by the supplier are linked with international calibration standards |
| 30 | Is there a formal approval procedure before the lot is transferred to the next stage? |
| 31 | Does the packaging used in production comply with safety requirements? Does it protect efficiently components and materials? |
| 32 | Is
a disposal system in place for rejected material/components? Check that bins are marked with a color code for easy identification |
| 33 | How
does the supplier monitor process effectiveness and implement changes for
improvement? Use of SPC, process capability studies, kaizen events, feedback to engineering,... |
| 34 | Does
the Supplier have a documented Maintenance Plan, including Preventive
Maintenance? Does it include all machinery, tools and auxiliary equipment? Are number of spare parts stocks are on line with Plan? Are tooling waiting for maintenance clearly marked as such? |
| 35 | Is
there an estimation of the tool life available? Is it in line with ABB
production plan? Does the estimation match the minimum guaranteed production volume on ABB purchase order? |
| 36 | Is
proper tooling available for production? Is it in good condition? Has the tooling been validated for this use prior to production? |
| Logistics | |
| 37 | Is
the supplier able to receive and understand ABB order releases and delivery
dates? Check EDI or web connections - Ask the supplier to review last release |
| 38 | Does
the supplier have a systematic incoming/WIP/finished goods inventory check? Does it include incoming/WIP/finished goods inventory check? Are the results accurate enough? |
| 39 | Does
the supplier work on a FIFO system? Check how they monitor dates and control sequence of usage. |
| 40 | Are
safety/consignment stocks levels monitored? Is the frequency appropriate for ABB needs? Are inventory checks performed by the supplier? |
| 41 | Does the supplier respect the validity dates for perishable items? |
| 42 | Are
packaging and shipping instructions clearly posted at point of operations,
and are they known by operators? Check that they are in conformance with Product/Process Control Plan. Do they adequately detail how to perform the operations and what to inspect? Check for customer complaints/warranty claims from errors in packaging and shipment. |
| 43 | Are finished goods properly identified according to ABB requirements? |
| 44 | Is
the quantity/weight indicated on bill of lading accurate? Check the process with the operator |
| 45 | How
have transportation suppliers been identified. Are transport/warehousing
companies regularly inspected? How do they monitor the performance of these suppliers? Corrective actions? |
| 46 | How
does the supplier measure its lead-time and On-Time Delivery to customers? If not in conformance with ABB needs, review the corrective actions? |
| General | |
| (These items cross departmental boundaries and should be evaluated throughout the audit. It is expected that these items will be sampled at various times during the audit and the final score will reflect the overall status.) | |
| 47 | Has the supplier implemented corrective actions requested from the last Quality Management System audit? |
| 48 | Have corrective actions from last environmental management system audit been implemented? |
| 49 | Are quality and financial results of the company communicated to personnel on a regular basis? |
| 50 | Are
safety regulations applied in work areas? Look at worker's behavior to check if the policy is understood and applied. Are there electrical cabinets left open? Do they wear ear/eye/feet protection? Are machines equipped with guards? Is appropriate handling equipment available? |
| 51 | Are personnel trained and certified for their assigned function? |
| 52 | Is
there an effective identification/traceability system in place? Check that for the particular item, you can trace it from source to delivery to ABB. This should include any 'special' lots as well. |
| 53 | Is
the status clear for all work within the operations? This applies to work performed, office, factory, site; parts, components, products; are quarantined items segregated? |
| 54 | Does the supplier handle non-conformances according to procedures? |
| 55 | Are immediate/corrective actions taken and are they efficient? |
| 56 | Does
the supplier use a Problem Solving Method, such as 8D, PDCA, …? Review a sample of Supplier's corrective actions, How do they handle customer complaints? Check effectiveness of problem solving with quality monitoring and check control plan has been updated. |
| 57 | Does
the supplier have a formal modification process? Check the last modification for the considered item. |
| 58 | Has
an adequate gauge control program been implemented? Calibrations traceable to internationally recognized standards, Gage R&R studies performed, all gages in calibration, checking fixtures available; operators trained. |
| 59 | Are work areas clearly identified, clean and organized, well lighted? |
| Global Score |
Sarbanes-Oxley (SOX) - Pivot
This diagram is a pivot diagram showing the data from an
audit.
SOX audit Pivot - Excel File - Down load
SOX audit Pivot - Excel File - Down load
SOX audit Pivot - Excel file download
Diagram Flow
Create a excel file with following data headers
- Control Number – Unique ID for the data table
- Control Name – The unique name for each control
- Component – The What the control does
- Department – Department name
- Business Unit – The name of the department
- Risks
- Risk Level – The level of risk, 1 to 5
- Process Complexity – Complexity level, 1 to 5
- Maturity Level – Maturity, 1 to 5
How to create a Pivot chart
Step 1 : Create data in an Excel file or Database
Step 2 : Use the data to create a PivotDiagram via the Insert Pivot Diagram menu
Step 3: Expand the Pivot Node to show various levels of sub-nodes that correspond to the data you want to analyze
A Pivot Diagram is a collection of shapes arranged
in a tree structure that helps you to analyze and summarize data in a visual,
easy-to-understand format. It starts out as a single shape, called a top node
(the “Total” shape in the diagram), that contains information imported from the
Excel worksheet. You can break the top node into a level of sub nodes to view
your data in various ways.
The PivotDiagram enables users to create data linked dynamic drawings faster & easier than ever before
Data Graphics are made up of elements that give the user a more complete view of their system
Data
Graphic - Pivot
• Title – Text element
• Control Number – Text element
• Risk Level – Data bar
• Maturity Level – Data bar
• Risk Level(Avg) – Icon set
• Maturity Level(Avg) – Icon set
Tips - Exact Formula copy or Value of Formula
This problem is how to copy the result of the formula as a value.
If you go to cell and Press Ctrl+Shift+",Excel copy the result from cell above as a value in cell current cell.
How to copy the formula
Use the Ctrl+'quotation mark to make an exact copy of a formula.
Use the Ctrl+d to do copy paste the above cell value / formula in the current cell.
The above tips will be useful for fast data entry and processing.
DEAL WITH DATES BEFORE 1900
| B | C | D | E | F | G | |
| 5 | DEAL WITH DATES BEFORE 1900 | |||||
| 4 | START | END | START MODIFIED | END MODIFIED | DELTA DAYS | DELTA YEARS |
| 5 | 2/17/1850 | 5/15/1960 | 2/17/2850 | 5/15/2960 | 40264 | 110 |
| 6 | 1/5/1880 | 6/14/1900 | 1/5/2880 | 6/14/2900 | 7465 | 20 |
| 7 | 11/13/1600 | 11/13/1900 | 11/13/2600 | 11/13/2900 | 109573 | 300 |
| 8 | 8/8/1760 | 9/10/1880 | 8/8/2760 | 9/10/2880 | 43863 | 120 |
| 9 | 10/11/1660 | 12/9/1980 | 10/11/2660 | 12/9/2980 | 116937 | 320 |
| 10 | 3/6/1670 | 6/6/1940 | 3/6/2670 | 6/6/2940 | 98708 | 270 |
| 11 | 7/5/1889 | 10/11/1990 | 7/5/2889 | 10/11/2990 | 36987 | 101 |
| 12 | 12/22/1740 | 4/23/1900 | 12/22/2740 | 4/23/2900 | 58196 | 159 |
| 13 | 7/20/1650 | 5/29/1950 | 7/20/2650 | 5/29/2950 | 109521 | 299 |
| 14 | 12/12/1888 | 10/28/1970 | 12/12/2888 | 10/28/2970 | 29904 | 81 |
If the cell contains a real date,you want to add,1000 years.An easy way to do this is to use the EDATE function and add 12,000 months to the date. =EDATE(a5,12000) returns a date that is 1000 years after a valid date in a5. note that tis function requires the analysis toolpack in versions prior to excel 2007. if you can ensure the analysis tooolpack is installed ,you can use "=date(year(a5)+1000,month(a5),day(a5))" if the cell does not contain a real date,you need to break the date apart,add 1000 years, put the date back together,and convert it to a real date:
* To get the left portion of the date,use=left(a5,len(a5)-4) * To get the year portion of the date,use=right(a5,4) * To add 1000 years to the date,use=right(a5,4)+1000 * To put the month,day,year+1000 back together,use =left(a5,len(a5)-4)&right(a5,4)+1000 * To convert that rasult back to a true date use =datevalue(left(a4,len(a4)-4)&right(a4,4)+1000)
You now need to selectively use either the EDATE or the DATEVALUE portion of the formula, depending on whether excel sees the date in a5 as text.enter this if statement in cell c5: =if(istext(a5),datevalue(left(a5,len(a5)-4&right(a5,4)+1000),edate(a5,12000)) copy it to cell c5 in cell e5 calculates the number of elapsed days with=d5-c5, in cell f5 calculate the number of years with=dateif(c5,d5,"y") you can combine the formulas from c5,d5and e5 into a single mege formula: =if(istext(b5),datevalue(left(b5,len(b5)-4)&right(b5,4)+1000),edate(b5,12000))-if(istext(a5),datevalue(left(a5,len(a5)-4)&right(a5,4)+1000),edate(a5,12000))
Database functions applications
There are powerful buildin function in excel which may by used with the databases for complex calcultions. The commonly used database functions are DMAX; DAVERAGE & DSUM.
DSUM would be the used as dynamic formula where you want to get database values. This functions gives the summation value of data which meets the critiria specified in the function. The picture shows the sample application of the fuction.
Subscribe to:
Posts (Atom)