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 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.