Reference no: EM13326891

Part 1 - Purchase Order Summary/Pivot Analysis

Go to the Tab named PO Register. "Clean" the data according to the following instructions. Note: The concept of how data should be presented inorder for Active Data commands to work, applies all the time. This process of "cleaning" or preparing data for analysis is common in many data analysis packages.

a. Most of ActiveData's commands require that the column names be in row 1 and that row 2 and beyond contain the data. In addition to this, ActiveData (and often Excel) looks at the first couple of rows of each column to determine what type of data the column contains (text, numbers or dates).

Your csv file has 5 rows before the column names and an extra row after the column names with a title in it and then the raw data rows.

Remove the first 5 rows and row 7:

Remove Grand Total rows

a. As the CFO, you would like to see this report summarized by vendor, with amount totals, displayed in a pivot table that shows Status across the top, with Vendors down the left side, with amounts and grand totals. (Check your pivot totals to your data totals as a last step.)

b. Question: As CFO, verbally describe in a narrative whether there are any things Ramsey PO Clerks should be looking out for or doing better in their data entry.

c. Question: As CFO, verbally describe the results, and do you have any concerns in the summary that you see?

Part 2 - Vendor Duplicate Payment Analysis

Go to the Tab named Payments in the Excel workbook. Make sure your data is clean and prepped for analysis. Perform an analysis using ActiveData to extract a list of duplicate payment amounts from the raw data.

a. Question: Do any duplicate amounts appear?

b. Question: After investigate these amounts, what is your conclusion as to their status (are they indeed duplicate payments)?

c. Question: As CFO, verbally describe in a narrative whether there are any things Ramsey PO Clerks should be looking out for or doing better in their data entry.

Part 3 - Strata Analysis for Open Invoices

Go to the Tab named Open Invoices in the Excel workbook. Make sure your data is clean and prepped for analysis. Perform an analysis using ActiveData to stratify the data for the following two views:

a. The CFO would like to present the data as by amount, in strata of 2500 dollars

b. The CFO would like to present the data by due date, in strata of 90 days

c. Generate an associated chart for each, and modify using Excel Charting Tools to improve the presentation and make clear what the charts depict

d. Question: As CFO, verbally describe in a narrative what each of these strata charts show and how they can be used in financial management decision making.

Attachment:- Ramsey-Analysis-Data-File-Toni.xlsx