Activedata analysis

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 – I simply am saving you this step). 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.

You should have experience with all of these Active Data functions from the Session 8 and you may refer to the demo videos at http://www.informationactive.com/iaplayer.cgi?x=play&v=&p=ad/videos&i=adVideos.csv.

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 and rename the resulting worksheet, “PO Analysis”.

a. As the CFO/Controller, 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/Controller, 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/Controller, 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. Rename the resulting worksheet, “Dup Pymt Analysis”.

a. Question: Do any duplicate amounts appear?

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

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

Part 3 – Analysis of Sales Discounts Granted

Several years ago, in an effort targeted at maintaining customer satisfaction, the Sales Manager granted his salespeople the ability to offer customer discounts in order to match competitor pricing. Although Ramsey prices their air products competitively, at times a competitor will offer special volume promotions or other sales that the Company is willing to match. Also, the salespeople are authorized to grant a preferred customer discount to their top customers. Generally, sales discounts range from 2% to 5%, although the salespeople are authorized to grant a discount up to 10% in exceptional conditions in order to close a sale. Historically, the annual sales discounts granted by Ramsey have averaged at just below 2% of gross sales revenue. This year, however, the controller has brought to the attention of the Sales Manager that the sales discounts granted have increased dramatically, to nearly 4% of gross sales revenue. The Sales Manager has been tasked with providing an explanation for this increase.

Impressed with the functionality of ActiveData in generating the requested compensation reports, the Sales Manager has requested that you use ActiveData to analyze the unexpectedly high sales discounts granted in 2013.

a. Begin on the worksheet titled “2013_Sales”. Recognizing that it will be more helpful to analyze sales discounts as a percentage of the Invoice Gross Amount, insert a Calculated Column to the right of the Discount column. Name the new column, “Discount Percentage”. Determine the appropriate formula to be entered to complete this calculation. Ensure that the discount percentages are shown as positive amounts and are formatted as Percent Style.

NOTE: After completing this step, you may or may not show a large value in cell G58? (depending on your worksheet layout. Simply delete this number (if it is in your worksheet) – it is not a figure you will need