Purchase Order Summary/Pivot Analysis
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 – 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 Folderaccording 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 as you progress through the assignment.
b. On the “2013_Sales” tab, to the right of the Invoice Status column, insert a Calculated Column to identify the Financial Quarter associated with each sales invoice. Name the new column, “Financial Quarter.” You may have to cut and paste the column to show it in the correct location in the worksheet.
c. To begin to analyze the percent sales discounts granted in 2013, use ActiveData’s Strata function. Select the Discount Percentage column as both the Column to Stratify and the Column to Total. Manually enter the Stratification Bands in 0.02 increments beginning at 0 and ending at 0.10. Include a sixth band from 0.10 – 1.0 to pick up all discounts granted at 10% and higher. Group this first stratification by Financial Quarter and only chart the “Percentage of Total Count” stratification results. Rename the resulting worksheet, “Stratified by Qtr”.
HINT: The chart output requested above is not the standard chart output. You may need to make modifications to the chart to reflect the final answer.
d. Analyze the stratification results and related charts for each of the financial quarters. What observations can you make regarding the increased sales discounts in 2013? Were any discounts granted to customers in violation of the company’s policy? Insert the textbox with your response to the right of the first chart on the “Stratified by Qtr” worksheet.
e. To further analyze the percent sales discounts granted in 2013, perform a second stratification. Again, select the Discount Percentage column as both the Column to Stratify and the Column to Total. Manually enter the Stratification Bands in 0.02 increments beginning at 0 and ending at 0.10. Include a sixth band from 0.10 – 1.0 to pick up all discounts granted at 10% and higher. However, group this stratification by Salesperson Number and, again, only chart the “Percentage of Total Count” stratification results. Rename the resulting worksheet, “Stratified by Salesperson”.
HINT: The chart output requested above is not the standard chart output.
f. Analyze the stratification results and related charts for the sales discounts granted by each of the salespeople. What observations can you make regarding the increased sales discounts in 2013? Considering the observations made at step d, above, why do you believe the sales discounts have nearly doubled in 2013? Are there any suggestions for how the Company could lower the total sales discounts for 2014? Insert the textbox with your response to the right of the chart on the “Stratified by Salesperson” worksheet.
