Internal Reporting Assignment
Overview
• The purpose of this assignment is to:
o Teach you how to connect to an external data source (i.e., MS SQL Server) and execute a query to get data. Although we will be connection to only one external data source, there are a variety of external data connectors that can be used to access external data.
o To work with large amounts of data (you will be downloading 121,317 records)
- Sorting
- Subtotals
- Filters
- Performing calculations
- Creating Pivot Tables
Requirements:
• Follow the instructions "How to Access External Data" (the majority of this document starting on pg. 3 after the questions) to get to the 121,317 records of data. Due to the large size of the file, make sure to complete the last step which involves deleting columns that you will not need.
• Once you have downloaded the data above, copy the data from the "Download" sheet to the "PivotTable" sheet.
• For each of the following questions listed on the next page, perform the required work to find the solution. Then record your answer on the "Answers" sheet.
o Take a screen shot of your spreadsheet to support/document your recorded solution and paste it in a cell next to your answer.
o You may need to format and resize cell widths/heights to make your "Answer" sheet readable and understandable. Remember that this will what we will be grading off of so make sure to show your work.
o Provide a short description of key steps/functions/etc. that helped you find your solution.
• Perform all of your work in the "Download"sheet (be careful to not change the base data since you will be doing multiple solutions here) except for the last requirement which will have you work in the "PivotTable" sheet. The Pivot Table that you create will serve as your answer to this requirement.
Questions:
Your boss has learned that you have some sales data (i.e., on the "Download" sheet). He wants to know answers to the following questions. Please help him (note that the field "LineTotal" contains the $$ amount per line)!!!
1) What are total number of records and sales $ in the dataset?
2) How many order line items from territories 1, 2 and 3 had only a quantity of 1 (Hint: use filters on these two columns and then the Count function)?
3) What customer (ID) has made the most $ purchases?
4) What product (ID) has the most number of units sold?
5) What is the Average Order $$ amount?
Now your boss is under pressure from the Auditors. They feel that Revenue has not been recorded properly from 2005-2008. Your boss gives you the following information regarding Revenue Recognition and wants your recommendation as to what revenue amounts should be recorded for the years 2005-2008:
FASB Statement of Financial Accounting Concepts No. 6 "Elements of Financial Statements" defines revenue as Inflows or other enhancements of assets of an entity or settlements of its liabilities (or a combination of both) from delivery or producing goods, rendering series, or other activities that constitute the entity's major or central operations.
The SEC in SAB No. 101 provides the following criteria for revenue recognition:
• Persuasive evidence of an arrangement exists
• Delivery has occurred or services have been rendered
• The seller's price to the buyer is fixed or determinable
• Collectability is reasonably assured
6) What is your recommendation to record the Breakdown of the $$ Amount of Revenue by Year for (and make sure to describe how and why you came up with this breakdown/solution):
a. 2005
b. 2006
c. 2007
d. 2008
And finally, your boss wants one last thing:
7) In the "PivotTable" sheet, create a multi-level pivot table where you can calculate Sales $$ or Quantities by Customer, Territory, Product and/or Salesperson.
Instructions: How to Access External Data
1. Once you have Excel open, open up the "Lastname_Firstname_SectionXXXXX-ACC350-ExcelExercise3.xlsx" file that you downloaded from BB.
2. Make sure that you have the "Download" sheet opened and cursor located in cell A1. From the ribbon, select the "Data" tab, then "Get External Data" (this may be open as a panel already), then "From Other Sources", then "From Microsoft Query".
3. "Choose Data Source" will pop up within Microsoft Query. Select and then click "OK"
4. A "Create New Data Source" dialogue box will appear. Name your data source "AdventureWorks2012" (can be any name but we'll name it for the Database that we'll be connecting to), then from the "Select a driver..." drop-down box select "SQL Server" (towards the very bottom of the list). Once these two selections have been made, click "Connect..."
5. A "SQL Server Login" dialogue box will appear. Type in (1) "wpcacc350db.wpcarey.asu.edu"(do not type the quotes) for the Server,(2) UNCHECK "Use Trusted Connection",(3) "sp17acc350user" for the Login ID (no quotes), (4) "p@$w0rd123!" for the Password (no quotes), and then (5) click "Options>>".
6. After clicking "Options>>", select the "AdventureWorks2012" for Database and either leave the language to "Default" or Select "English". Click OK.
7. Once you click OK you will be returned to the following screen. Click/check the "Save my user ID and password in the data source definition" checkbox.
8. The following will pop-up and you should click "Yes".
9. This bring you back to this screen. Click OK (do not select a default table).
10. Now back in Microsoft Query choose the AdventureWorks2012 data source that we just created and click "OK" (make sure the "Use the Query Wizard to create/edit queries" is selected; it should default to being checked).
11. Then from the Query Wizard screen scroll down and highlight "SalesOrderDetail" then click the ">" button to move the columns from this table to the "Columns in your query" area. DO THIS AGAIN FOR the "SalesOrderHeader" table (select this table then click the ">" button).
12. After you have completed moving columns from both the "SalesOrderDetail" and "SalesOrderHeader" tables into your query, select "Next".
13. You may receive the following screen:
14. In the next screen (you may want to move/expand the tables as shown below), link the SalesOrderID from the SalesOrderDetail table to the SalesOrderID from the SalesOrderHeader table (Hint: click the SalesOrderID field in the SalesOrderDetail table and "drag" onto the SalesOrderID field in the SalesOrderHeader table to create the line as shown)
15. Click on the "Save" icon () found just below the Edit & View menu items. You will then save the "Query" that you have developed using the Query Wizard (accept the name it defaults to). Next, click the "Return Data" icon ( ) found just below the "View" menu choice.
16. You will be prompted with the following screen below. Make sure that (a) "Table" button, (b) "Existing Worksheet" button, and (c) cell "=$A$1" are selected. Then click "OK".
17. There will be a message while the query is returning data to Excel. Shortly you should see something like the following screenshot. Make sure to check that there are a total of 121,317 rows of data (plus one Column header row). MAKE SURE TO SAVE THE FILE!!! This will be your base data (you don't want to have to go through all of the above again). You actually may want to create a backup of this file right now in case something happens for future use.
18. To reduce the size of the spreadsheet, DELETE ALL OTHER COLUMNS EXCEPT FOR the following ten columns listed below (a thru j). Once you are down to these ten columns, rearrange the order of these eight columns so that they appear as follows:
a. OrderDate,
b. ShipDate,
c. DueDate
d. SalesOrderNumber,
e. CustomerID,
f. SalesPersonID,
g. TerritoryID
h. OrderQty,
i. ProductID,
j. LineTotal = totals sales amount for the given quantity (OrderQty) of product (ProductID) on a particular line of a sales order (SalesOrderNumber). Note that there can be one or more lines on each sales order.