- Excalibur's Sheath

May 2, 2012
by Jordan
0 comments

Accounting Basics: Internal Audit

This entry is part 2 of 2 in the series Accounting Fundamentals

The Institute of Internal Auditors defines an internal audit:

Internal auditing is an independent, objective assurance and consulting activity designed to add value and improve an organization’s operations. It helps an organization accomplish its objectives by bringing a systematic, disciplined approach to evaluate and improve the effectiveness of risk management, control, and governance processes.

Internal Audits can take the form of a ten step process, like the one which I found on the Cornell University Audit Office website:

Notification
Planning
Opening Meeting
Fieldwork
Communication
Report Drafting
Management Response
Closing Meeting
Report Distribution
Follow-up

Notification

The start of the process is a notification to the company, or unit of a company to be audited. In the notification, usually a letter the auditor asks for information. The information the auditor may ask for includes organizational charts, financial statements, and other documents which will let the auditor become familiar with the organization.

Planning

The auditor then determines where risks may exist. They will draft an audit plan, and schedule a meeting.

Opening Meeting

The opening meeting will involve senior staff, and employees involved in the audit. The scope of the audit will be discussed and employees can ask the auditor to extend the scope to cover other areas that they are concerned about. The time frame of the audit, and timing of vacations or other days off is discussed.

Fieldwork

After the meeting the audit plan is finalized and field work will begin. Fieldwork involves talking to staff, reviewing manuals, learning about your processes, and testing for compliance with company policies, procedures, and the law. Internal controls will also be examined.

Communication

Throughout the audit the auditor should communicate with you about issues, and to discuss solutions.

Report Drafting

After the field work the auditor should draft a report which includes the following:

the distribution list
the follow-up date
a general overview of your unit
the scope of the audit
any major audit concerns
the overall conclusion
detailed commentary describing the findings and recommended solutions

Management Response

After the final report is issued get a response from the managers of the areas audited. Look for whether they agree or not with the findings, and how they intend to implement changes to solve the issues discovered.

Closing Meeting

After the audit and management responses are completed the closing meeting will allow everyone involved to respond to the findings, proposed solutions and if there are any more problems to be discussed.

Report Distribution

The final report is then distributed to everyone involved.

Follow-up

Follow up reviews can be scheduled to check on the progress of correcting issues that were found during the audit.

March 14, 2012
by Jordan
0 comments

Accounting Basics: The Financial Statements

Financial Accounting is the part of accounting which deals the creation of the financial statements. Financial statements are created for decision makers. Decision makers are found both inside and outside the company. Some examples of decision makers are:

Stockholders
Owners
Lenders

The financial statements that are used by these decision makers are:

The Balance Sheet
The Income Statement
The Statement of Cash Flows

The Balance Sheet

The balance sheet shows how a company’s assets balance with its liabilities and owners equity. Assets are balanced by liabilities and owner’s equity. This is so assets can be tied to how they were funded.

The Income Statement

The income statement shows how the net income was arrived at. It shows the revenues earned for a specific time period with all costs and expenses removed.

The Cash-Flow Statement

The cash-flow statement shows a company’s inflows and out flows of cash. The cash flow statement is divided into three sections:

Operating Activities
Investment Activities
Financing Activities

Using the Financial Statements

The financial statements once prepared are given to a primary audience of owners/shareholders, and lenders. Publicly traded companies also see their financial statements go to a wider audience which includes:

Customers
Competitors
Employees
Labor Groups

Faith in the Financial Statements

The financial statements are so important that another branch of accounting, auditors, examine the statements to ensure their accuracy.

Conclusion

Have your bookkeeper, accountant, or CPA prepare financial statements for you regularly. They can tell you things about your business. They can help you find where you are profitable and not.

March 11, 2012
by Jordan
0 comments

Accounting Basics: Accounting Functions & History

This entry is part 1 of 2 in the series Accounting Fundamentals

Accounting is the process of measuring and communicating financial & economic information about an entity for making informed decisions about the entity. Different groups of people need different information about the entity, because they will make different decisions. The decisions different groups of people need to make are:

To lend to the entity
To invest in the entity
How to be more profitable
To stay employed by the entity
If the entity is following the rules

The Accounting Functions

Financial Accounting – Creating and interpreting financial statements
Managerial & Cost Accounting – Use economic and financial information to run an entities operations and to make production decisions
Auditing – Verifying financial statements for another entity.
Internal Auditing – Verify the accounting systems & processes for the company.
Governmental & Not For Profit Accounting – Specialization in the unique needs of entities which are not formed to create a profit
Income Tax Accounting – Performing income and other tax filing for entities and people.

Accounting History

a Mesopotamian Accounting System developed around 10,000 years ago. Modern double entry accounting began in 1494 with the publishing of Method of Venice. As the industrial revolution blossomed the audit function developed in response to the corporate form of business, with investor-owners who did not necessarily run the business in which their money was invested. In the early 1900s Accountants, in the United States of America began to organize and to establish standards. Starting in 1932 the American Institute of Accountants & The New York Stock Exchange came to agree on five basic accounting principles. The Securities acts of 1933 & 1934 helped to further establish accounting principles for companies in the United States. In 1973 The Financial Accounting Standards Board (FASB) was created to create and manage the accounting industry standards in a more complete way. As time has progressed other standards groups have come into existence. GASB for governmental IASB – an international standards board are notable examples of these other boards which have developed over time.

March 4, 2012
by Jordan
0 comments

Excel: Date and Time Functions

This entry is part 7 of 7 in the series Excel Functions

The Date and Time functions of Microsoft Excel can be divided naturally into two types of functions; those which deal with dates and those which deal with times. The date and time functions provide a way to manipulate time and dates.

Date and Time functions work with Excel’s date-time format. The date-time format is a numerical format, which can be styled into a date. The DATEVALUE function is instrumental in getting starting Dates.

Date Functions

Date

The DATE Function gives the Excel date-time code for a specific date. It takes the inputs of Year, Month, Day. Inputting 2012,2,29 for Year/Month/Day gives a result of 40968.

=DATE(2012,2,29)

DateValue

DATE VALUE takes in a date in a mostly numerical format for example 1/1/2012 entered for the date gives a Microsoft date time code of 40909.

=DATEVALUE(“1/1/2012”)

Day

The DAY function takes a Excel date code and gives a number of 1 to 31 to show the date of the month.

=DAY(40909)

Days360

The DAYS360 tells us the number of days between two dates on a 360 day calendar with twelve months of thirty days. It takes as inputs the starting date, the ending date, and a TRUE / FALSE for the method to use in calculating the dates.

=DAYS360(40909,41274,FALSE)

EDate

EDATE returns an excel date code for the date that is the number of months before or after the starting date. The inputs are Starting Date and Months. Use negative numbers for before the starting date and positive numbers after the starting date. The starting date number needs to be in Excel’s date-time format. It outputs in the date-time format.

=EDATE(40909,-24)

=EDTE(40909,24)

EOMonth

EOMONTH is like EDATE, but it will return the date-time code of the last day of the month before or after the start date.

=EOMONTH(40909,-24)

=EOMONTH(40909,24)

NetworkDays

NETWORKDAYS will tell us how many working days are between two dates. Use the date-time format to tell Excel what the start and end dates are. You can also enter dates in the date-time format which are holidays. NETWORKDAYS does not count weekends, or the holidays.

=NETWORKDAYS(40909,41305,40913:40915)

Year

YEAR takes a date-time formatted date and returns its year.

=YEAR(40909)

YearFrac

YEARFRAC returns the fractional of a year between two dates. Enter your start and end dates in date-time format. Then tell Excel which basis to use.

The basis you can use are (From Excel 2007 Online Help):

Basis	Day count basis
0 or omitted	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

=YEARFRAC(40909, 41305,3)

Today

TODAY returns today’s date every time the sheet is loaded or calculated.

=TODAY()

Weekday

WEEKDAY returns the number of the day of the week for the date given. Enter the date-time format for your date. Then select which return type to use. The possible return types are (From Excel 2007 Online Help):

Return_type	Number returned
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2	Numbers 1 (Monday) through 7 (Sunday).
3	Numbers 0 (Monday) through 6 (Sunday).

=WEEKDAY(40909,2)

WeekNum

WEEKNUM returns the week number for the specified date-time number. It takes in the date in date-time number and a flag to tell the function if you want the weeks to begin on Monday or Sunday. Here is what the return types are and what they mean for this function from Microsoft Excel 2007′s Online Help.

Return_type	Week Begins
1	Week begins on Sunday. Weekdays are numbered 1 through 7.
2	Week begins on Monday. Weekdays are numbered 1 through 7.

=WEEKNUM(40909,2)

Workday

Returns the date-time number for a day before or after the start date which takes holidays you specify into consideration. Use negative numbers to go to a date before your start date.

=WORKDAY(40972,5,40952:40955)

=WORKDAY(40972,-5,40952:40955)

Month

Returns the month of a date in date-time format as a number (1-January to 12-December).

=MONTH(40972)

Time Functions

Hour

HOUR will give you the hour from a date-time number or from the time entered as text.

=HOUR(“6:40PM”)

=HOUR(0.777778)

Minute

MINUTE works like HOUR but returns the minutes.

=MINUTE(“6:40 PM”)

=MINUTE(0.777778)

Now

NOW changes its value with the current date and time.

=NOW()

Second

SECOND will return the seconds from either a text representation of the time, or from a date-time code.

=SECOND(“6:40:30 PM”)

=SECOND(0.778125)

Time

TIME will convert text for hour, minute, & seconds to a date-time number and format it with a time format.

TimeValue

TIMEVALUE converts time in a text value to a date-time number.

=TIMEVALUE(“6:40 PM”)

Conclusion

If you need to work with dates and times in Excel there are many useful tools to work with. The Date-Time format that many of these tools use can be confusing at first but it does not need to be.

February 27, 2012
by Jordan
0 comments

Excel: Conditional Formatting (Part Three)

This entry is part 3 of 3 in the series Conditional Formatting

This is the third and last part of my articles on using conditional formatting in Excel. In this article I am going to go over managing and creating rules, and using Visual Basic as an alternative to the built in conditional formatting tool.

Managing and Creating Rules

It is easy to manage and create rules for conditional formatting. Under the conditional formatting menu there are three choices:

New Rule
Clear Rules
Manage Rules

New Rule

The New Rule option allows you to create a custom designed rule. Here you can specify any of the rules I have written about, but you can also use one more type of rule, the formula. The formula is powerful, because you can use it to set the formatting of a cell based on the value of another cell. You can use it to create flags, and change the formatting for a row of cells, for example.

Clear Rules

Clear rules will clear all the rules in your selection.

Manage Rules

Manage Rules will let you add, delete, or change the order of rules on the cells you have selected.

Create Conditional Formatting with VBA

You can embed code into your workbooks to create conditional formatting. This is really helpful in older versions of Excel where you were limited to three conditional formats. Using code to create conditional formatting also allows you to have more control over your formatting.

To use code to create conditional formatting you need to be able to specify which cells in your workbooks to format. You need to be able to set the formatting you want. Then you need to be able to evaluate the values of the cells and change the formatting.

Where to put code

Put the code on the Sheet where you want the formatting to be. I found the code I am using here.

Open your code like this:

Private Sub Worksheet_Change(ByVal Target As Range) – This will execute the code every time a change is made to the sheet.

To Define Cells to Format & Their Formatting

Set MyPage = Range(“A1:H10″) – This sets the range of cells where we want this conditional formatting to be.

For Each Cell In MyPage – This says for each cell in the range

This is an example of all the values I found for formatting a cell.

If Cell.Value = 7 Then

Cell.Interior.ColorIndex = 4

Cell.Font.Name = “Times New Roman”

Cell.Font.Size = 24

Cell.Font.Bold = True

Cell.Font.Regular = True

Cell.Font.Italic = True

Cell.Font.Underline = True

End If

End Sub – End of the code.

Put all your If statements between the last End If and Next.

The ColorIndex values are:

For setting the font, use a font name in quotation marks. Be sure that the font is on all the machines where it will be used.

The size attributes takes a number.

Use the Bold, Italic, Underline, and Regular carefully. They are true false switches.

The power of the VBA formatting comes in that it is easy to set the conditions to look for. You can set an unlimited number of values to check for.

February 21, 2012
by Jordan
0 comments

Excel: Conditional Formatting (Part Two)

This entry is part 2 of 3 in the series Conditional Formatting

This time around I am going to cover Top/Bottom Rules, Data Bars, Color Scales, & Icon Sets.

Conditional Formatting Options

Top/Bottom Rules

The Top/Bottom rules help us find the top, bottom, above or below average items quickly. The Top/Bottom Rules section contains the following options:

Top 10
Top 10%
Bottom 10
Bottom 10%
Above Average
Below Average

Top 10

To use the Top 10 rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Top/Bottom Rules, then select Top 10. Now choose the Top however many cells you want and the formatting rule to use.

Top 10%

To use the Top 10% rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Top/Bottom Rules, then select Top 10%. Now choose the top percent you want and the formatting rule to use.

Bottom 10

To use the Bottom 10 rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Top/Bottom Rules, then select Bottom10. Now choose the Top however many cells you want and the formatting rule to use.

Bottom 10%

To use the Bottom10% rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Top/Bottom Rules, then select Bottom 10%. Now choose the bottom percent you want and the formatting rule to use.

Above Average

To use the Above Average rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Top/Bottom Rules, then select Above Average. Now select the formatting for all of the cells which are above average.

Below Average

To use the Below Average rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Top/Bottom Rules, then select Above Average. Now select the formatting for all of the cells which are below average.

Data Bars

data bars represent the values in the cells as bars, with higher value bars being bigger. In the menu there are many choices of color schemes to pick for your data bars.

Color Scales

Color scales color a range of cells in different colors depending on the values of the cells and how they relate to each other. Select the cells you want to find and then select the color scheme from the list of color scales.

Icon Sets

The icon sets allow you to specify sets of icons to display with the values in ranges of cells.

This finishes off the list of predefined functions in Excel. Next time I will go over a few ways to extend the power of conditional formatting in new ways.

February 20, 2012
by Jordan
0 comments

Excel: Conditional Formatting (Part One)

This entry is part 1 of 3 in the series Conditional Formatting

Most spreadsheets, like Excel, have a feature which is useful for data analysis. This feature is conditional formatting. Conditional formatting uses the values of cells to decide what formatting to apply to cells. I am going to use Excel 2007 to show conditional formatting. Because there are so many options with conditional formatting in Excel 2007 look for two other posts on this topic.

Conditional Formatting Options

In Excel 2007 conditional formatting is found on the Home ribbon. In the drop down menu for conditional formatting there are several options available:

Highlight Cell Rules
Top/Bottom Rules
Data Bars
Color Scales
Icon Sets
New Rule
Clear Rules
Manage Rules

Excel has made using conditional formatting easier by setting up common rules which you can click on and use. Each of the menu items above, except for New Rule and Manage Rules are menus to let you select options for conditional formatting.

Highlight Cell Rules

Highlight Cell Rules allows you to choose various values to highlight rules. The predefined rules are:

Greater Than
Less Than
Between
Equal To
Text That Contains
A Date Occurring
Duplicate Values

Greater Than

To use the Greater Than rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Highlight Cells, then select Greater Than. Now choose the value cells should be greater than and the formatting to use.

Less Than

To use the Less Than rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Highlight Cells, then select Less Than. Now choose the value cells should be less than and the formatting to use.

Between

To use the Between rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Highlight Cells, then select Between. Now choose the value cells should be greater than and the value the cells should be less than. Then select the formatting to use.

Equal To

To use the Equal To rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Highlight Cells, then select Equal To. Now choose the value cells should be equal to and the formatting to use.

Text That Contains

To use the Text That Contains rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Highlight Cells, then select Text That Contains. Now choose the text cells should contain and the formatting to use.

A Date Occurring

To use the A Date Occurring rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Highlight Cells, then A Date Occurring. Now choose the date the cells should be equal to and the formatting to use.

Duplicate Values

To use the Duplicate Values rule make a selection on your spreadsheet where the rule will be applied, then select the Conditional Formatting from the ribbon, then select Highlight Cells, then select Duplicate Values. Now choose whether to format unique or duplicate cells and the formatting to use.

Wrap up

The Highlight Cells Options allow a lot of formatting options to cells and ranges of cells based on cell values.

February 17, 2012
by Jordan
0 comments

Excel: Type, Error.Type, IsBlank, IsError, IsEven, IsErr, IsLogical, IsNA, IsNontext, IsNumber, IsOdd, IsRef, IsText

This entry is part 6 of 7 in the series Excel Functions

Today I am going to cover several of the logical functions available in Excel. These functions give information about cells, and their contents. These can be mixed with other functions to be powerful. The functions are:

Type
Error.Type
IsBlank
IsError
IsEven
IsErr
IsLogical
IsNA
IsNontext
IsNumber
IsOdd
IsRef
IsText

The Type function returns a number representing the data type in the referenced cell.

=TYPE([Reference Cell])

The IsError and IsErr functions return True or False depending on if the referenced cell contains an error.

=ISERROR([Reference Cell])

=ISERR([Reference Cell])

The Error.Type function returns a number for the type of error returned.

=ERROR.TYPE([Error to Identify])

The IsBlank Function tests to see if the Referenced Cell is blank.

=ISBLANK([Reference Cell])

The IsEven and IsOdd functions test the reference cell to see if they are even or odd respectively. The functions return TRUE and FALSE.

=ISEVEN([Reference Cell])

=ISODD([Reference Cell])

The IsLogical Function checks a reference for TRUE or FALSE and returns TRUE if the value is TRUE or FALSE and FALSE if the value is not TRUE or FALSE

=ISLOGICAL([Reference Cell])

The IsNA function tests for the #NA error. If it is found it returns TRUE otherwise it returns FALSE.

=ISNA([Reference Cell])

The IsText, IsNontext, and IsNumber functions test the referenced cells are the stated values, numerical, text, or not text. They return TRUE or FALSE.

=ISTEXT([Reference Cell])

=ISNONTEXT([Reference Cell])

=ISNUMBER([Reference Cell])

The IsRef function tests whether a cell is a reference or not, and return TRUE or FALSE.

=ISREF([Cell Reference])

February 15, 2012
by Jordan
0 comments

Excel: Clean, Concatenate, Trim, Right, Left, Mid, Len

This entry is part 5 of 7 in the series Excel Functions

Today I want to tell you how to use some important text manipulation functions found in Excel. Just like the Commodore Data Cassette Drive recorded important information a spreadsheet can record information for your use. The functions I am going to cover today are:

Clean
Trim
Right
Left
Mid
Concatenate
Len

These functions help keep text tidy, put text together, or take it apart.

Keeping Text Tidy

Clean and Trim help keep text tidy. Clean will remove all non-printable characters. It is used like this:

=TRIM([Text to Trim])

Clean removes all white space except for one space between words.

=CLEAN([Text to Trim])

Splitting Text

Right, Left, and Mid are text splitting functions. Right starts at the right end of a string, and Left starts at the left end of the string. They are used like this:

=RIGHT([Source Text],[Number of Characters to Get])

=LEFT([Source Text],[Number of Characters to Get])

Mid is a little different, it needs a starting point.

=MID(([Source Text],[Start Point],[Number of Characters to Get])

Combining Text and Finding Length of Text in Cells

To combine text use the Concatenate Function:

=CONCATENATE(,[Text To Combine 1],[Text To Combine 2])

You can use as many texts as you want. They can be cell references or text.

The Len function will return the length of text in a cell. It is:

=LEN([Reference to a cell])

January 19, 2012
by Jordan
0 comments