MSFS 670 – ActiveData Graded Homework Assignment

MSFS 670 – ActiveData Graded Homework Assignment

The attached Excel workbook contains data that has been extracted from NetSuite (via the Export to CVS file function at the bottom right of all reports). Use the data and the workbook to complete the following homework questions. Perform all analyses in this Excel workbook and answer the narrative questions on the same page as the analysis (use Insert Text Box) so that you only submit ONE file to your Assignment Folder according to the date in the Syllabus. Remember to rename the workbook under your name so that it can be easily identified for grading.

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.

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

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

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.

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.

Question: Do any duplicate amounts appear?

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

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:

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

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

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

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