Author Archives: admin

Presidential Analytics: Profit and Loss

In a world where misinformation is common place it is truly an asset to be able to analyze data.  The table below was derived with data from the Federal Election Commission.  We used simple groupings to easily compare the Trump and Clinton campaigns.

2016-election-tableWe can quickly see that Clinton received more in the way of revenue.  This trend was primarily driven by individual donors.  We can also see that Trump self-funded his campaign at a greater rate then Clinton.  Overall both candidates spend a majority of the money collected.  This is demonstrated by the Net Margin’s of 15% and 13%.  This simply means that 15% and 13% of the money received was left at the end of the month.

At times it’s helpful to visualize data as well.  Pictures tend to capture the attention of audiences faster.  Below is an example of a visual way to display a Profit and Loss statement.

2016-election-table-visualize

One thing you may notice right away is that the detail is omitted.  When we use charts and graphs we automatically have to summarize data.  To much data on a graph will create noise, a chart that doesn’t make any sense.  In this example we just focus on the major bucket items: revenue, expense and net income (difference between revenue and expense).

Sign-up for our free monthly enewsletter get 154 Excel Shortcuts.  Sign-up Here.

 

Excel 2016 – Chronicles Vol. II

For many people saying thank you carries more weight then a big celebration of accomplishment or award. It’s the little things that matter.  Excel 2016 has many of those little things.  Slight differences which go unnoticed by most but add tremendous value.  One of the little things added to Excel 2016 is ability to add to completely independent windows.  In  the past windows could be created but they both were controlled by the same ribbon.  This limited the flexibility in both windows.  For example you always had to keep in mind which window was active to preform operations.  The small change into two completely independent windows makes it easier to work with multiple monitor setups.  Here’s a quick run through on how to create windows in Excel 2016.

1.Under the Ribbon click View

Excel 2016 Ribbon

2.Under View select New Window

blog-pic-2

3. Two independent windows appear. Notice two separate Ribbons.

blog-pic-3

It is important to note that even though the same Excel workbook is separated in two windows communication between them still exists. This is a small change in the users eyes but a powerful one.

 

Excel 2016 Chronicles – Vol. I

To say the least there is a little anxiety with learning, experiencing something new. To add another layer of complexity I am in time crunch.  Yes, I know a bit about Excel but unlocking this new version felt a little uneasy.  To start from the beginning, I upgraded to 2016 for a special project.  The executive team was looking for fancier charts to articulate company performance, something to capture the attention of key stakeholders.  I was limited to the charts available in 2010.  Eventually I was led to something called a sunburst chart.  A starburst chart is an advanced level pie chart showing subcomponents of a pie a circular pattern.  My first attempt was to fall back on a tried and true method of combining chart types to create something new and unique Excel 2010.  I started by creating a simple pie chart then adding a second donut chart on top.  I then made the background clear in order to view both charts at once.  The result…a quirky chart which just didn’t look right.

Sunburst Excel 2010

If you notice the alignment is all wrong, color scheme not eye catching enough.  In short not there yet.  After a little research I noticed this new visualization was easily accessible in 2016.  After some play here is a quick rundown on how to put it all together.

 

1. Setup your data up correctly.  The data must be setup in a tabular fashion, showing the different layers of the sunburst.  The first level is the core or center.  Additional levels show up in subsequent columns.

Sunburst Source Table

2. In the Ribbon Click Insert, then click Recommended Charts

Ribbon

 

3. Click on All Charts

 

4. Scroll down and click Sunburhst, Click Ok

Sunbrust

5. The following chart appears as a result

Sunburst Final

In addition to this, here are a few best practices: Use white spaces or lines to accentuate the slices, share the document as a PDF or JPEG.  Most people do not have Excel 2016.  The chart received rave reviews from key stakeholders.  It was something new fresh, yet it did not sacrifice the message.

I’ll keep you informed on the new experiences I’m having with Excel 2016.  I hope they help you when you make the leap.

The New Age of PDF Conversion – PDFConverter Elite 4

Sometime ago a colleague had asked me how to convert PDF documents (Portable Document Format) into Excel. At the time I mentioned a software that could get the job done before I learned about PDF Converter Elite 4. Every PDF related software can convert Excel, Word and PowerPoint to a PDF document but few can do the reverse well. When I tested PDF Converter Elite I specifically looked for this feature.  I found the steps and final result to be better than other tools. Below is a brief step by step on how to use this specific conversion feature in the tool.

PDF

PDFConverter Elite

  1. Open the PDF file to be converted
  2. Next click on the conversion type (i.e. PDF to Excel)
  3. Once the file is open you several options to select from.
    1. Select Area
    2. Select All Pages
    3. Page Range

The last two options are a tremendous time saver. Other software’s are restricted to the manually option. Personally, I always find myself scrolling up and down trying to pinpoint the exact spot to stop in other tools. This detail in PDFConverter is a refreshing option.

4. Next click on the Convert button

PDFConverter Elite Final Resutl

PDFConverter Elite Final Result

5. The final result is a listing in Excel of the data and descriptions which appeared in  the PDF document.

It is important to mention that some of the advanced options. Many of these options are unique to PDF Converter. One of the unique options is the ability to edit the table to be exported to Excel before it gets there. Users can edit header, footer, columns and rows before transporting to Excel.   These additional features can save time for Excel users later on.

Learn more about the other features of this tool on PDFConverter.com .

 

The Excel Lab – 10/24/15

The Excel Lab Final

 

 

EVENT DESCRIPTION

On Saturday, September 19th at the Brooklyn Public Library we will provide a forum to solve the real world Excel problems of attendees.  During this session attendees will share Excel problems and provide ideas/approaches to a solution.  Prior to the problem pitches we will share a short hands-on Excel topic with attendees.  Hear a Finance/Microsoft Excel expert (Othniel Denis, MBA) and work with hands-on exercises. Laptops and space is limited.  Attendees are encouraged to bring as much information as possible on specific Excel problem(s) (i.e. sample Excel files on USB flash drive etc.)

 

HOW YOU WILL BENEFIT

  • Find solution to pressing problems
  • Learn alternate ways to solve problems
  • Build confidence with this tool
  • Learn ways Excel is used in other businesses
  • Network with other professionals

WHAT YOU WILL COVER

  • Network with peers
  • Receive solutions to your Excel problems
  • Learn challenges of peers in industry and business
  • Shortcuts & Best practices

 

WHO SHOULD ATTEND

Small business owners, accountants, controllers, auditors, financial planners,  financial analysts, budget analysts, bookkeepers, cost estimators, new managers, students, jobseekers

 

AGENDA

  • 10:00am – 10:15am – Introduction and brief hands-on tutorial
  • 10:15am – 11:00am – Problem pitches & Solutions

LOCATION

Brooklyn Public Library, 10 Grand Army Plaza, Info Commons, Brooklyn, NY

Register Here: bit.ly/TheExcelLab1024

Cutting Audit Sampling Risk Down to Size: Automating the Sampling Process

Every Accountant professional at some point must take a sample. Sampling is defined as “the application of an audit procedure to less than 100 percent of the items within an account balance or class of transactions for the purpose of evaluating some characteristic of the balance or class” according to Audit Sampling guideline AU Section 350. Essentially sampling is the process of examining a small portion of a client’s records to make a judgment on the credibility of the whole. One of the risks associated with this process is selecting a sample which is not an accurate representative of the whole. Samples which are done manually may seem random but fail to meet this rigorous standard. Think about it, random means lack of pattern. As humans we tend to stick to patterns and routines. An auditor may pick all even number selections or every third in an effort at randomness. In this situation the likelihood that gross errors in a client’s books will be missed are exponentially increased. Sadly, many audits rely on manual selections from an Excel spreadsheet. For example, let’s say an audit is needed on accounts payable transactions. The typical audit would involve selecting a sample number of vouchers and back track to the supporting documentation such as invoices and purchase orders. Using the automated Excel technique highlighted below can minimize sampling risk and increase productivity.

 

Using Excel a tool can be developed to automatically apply red highlights random general entries. In order to have Excel automatically select random journal entries, we first need to organize our spreadsheet into two tables like the one below. The first table will contain our journal entries and the second will help us select random entries.

Master List

 

Add Conditional Formatting

 Journal List

1. Highlight the list of journal entry id numbers

Conditional Format Menu

  1. Under the Home menu click conditional formatting > Highlight Cells Rules > More Rules

 

Edit Format Rule Menu

 

  1. Select the formatting Rule type titled “Format only cells that contain”
  2. Change logical condition to “equal to”
  3. Enter J3 cell address in the last box
  4. Click Ok

 

Note: This will check to see if any of the journal ids in column B match journal ids in Column J

 

Edit Format Rules

  1. Click on Conditional Formatting the Manage Rules

 Edit Rule Manager

 

 

  1. Click on New Rule
  2. Follow steps 3 and 4 entering cells J4, J5, J6, J7 in the subsequent conditional format rules

Sample Generator 7

10. An inventory of all conditional format rules should look like the picture above

11. Click Ok

 

 

Add Random Calculation Formula

Sample Generator 812. Enter the following formula in cell A3 =RandBetween(1,10)

 

Note: The RandBetween formula is called a volatile function. This means the result constantly changes. This particular formula will give a random result between 1 and 10. Press F9 to see the result change.

Sample Generator 9

 

13. Copy the RandBetween function into the cells A4 through A12


Add Vlookup Formula

Sample Generator 10

14. In cell J2 type in =Vlookup(I3,$A:$B,2,false)

15. Copy this formula from J4 through J7

The Result

Sample Generator 12

16. Red highlights will be applied to the journal entries which appear with a selection number  between 1 – 5

17. Press F9 to select another random sample

Note: In some instances a cell in the Selected Journal Entry column may appear with #N/A. This means the selected sample number has not been found. Press F9 again to get another sample.

 

Bigger and Better

 

What if you’re working with more journal entries and need a larger sample. How can this approach be adjusted to accommodate? In order to apply this technique to a sample size of 25 we would need to make three adjustments to our spreadsheet:

1. Add more journal entries to the Journal Entry Master List

2. Revisit steps 2 – 8 for conditional formatting and enter cells J8 to J28. This will give the ability to select 20 more samples.

3. Change the RandBetween formula that appears in each cell in column A from RandBetween(1,10) to RandBetween(1,25).

4. Extend the helper table which contains the columns titled Selected Samples and Selected Journal Entry 20 more rows. The column titled Selected Samples should go from 1 to 25. Copy the vlookup formula down 20 rows.

 

This combination of tools is one of many techniques which can be used to simplify and reduce risks in the auditing process. To view a demonstration of this tool in action visit

youtu.be/zXqd-T_14fE.