IDS 200 DePauw Customer Sales & Product Import Access and Excel Project

User Generated

onbonbjh

Business Finance

IDS 200

DePauw University

IDS

Description

Basic HW

due in 4 days,

see attached "Assignment04-IDS 200-Spring 2020.docx" for instruction

Unformatted Attachment Preview

IDS 200 Spring 2020 Assignment 4 Due Friday, 4/17/2020, 11:59pm 60 points Instructions Deadline In order to receive full credit, this assignment must be submitted by Friday, April 17th at 11:59pm. Submitting your assignment early is recommended. Late submissions will be accepted (but penalized 15 pts) up to one week after the submission deadline. After that, assignments will not be accepted. Assignment You must import data into the provided Access Database, run a provided query, export the query results to Excel, and then perform the requested analysis/graphing in Excel. Two starting files are provided: 1. HW4.Customer.Sales – starting Access DB. 2. HW4.product.import – Excel file containing information to be imported into Access. The database is being used to track customers, orders, and products. The database has 4 tables – Customer, Product, Order, and Orderline. A customer can have multiple orders, but an order can only have one customer. An order can have multiple orderlines, but only one customer. An orderline has only one product and belongs to one order, but a product can be on multiple orderlines. The relationships are already established in the starting database (HW4.Customer.Sales) 1. The database user wishes to add an additional set of products into the database. The list of products is contained in a provided Excel Spreadsheet (HW4.product.import). Import the list of new products into the existing Product Table in Access. 2. Run the provided query “SalesPerCustomer” in the Access DB. Export the results to an Excel file named “SalesPerCustomer”. Make sure you check “Export data with formatting and layout”. 3. In the Excel File “SalesPerCustomer”, add a pie chart to the spreadsheet that shows the sales amount ($) per customer. The chart can be inserted on the same worksheet as the data. (pie charts can be found in the same area of Excel as the line chart we did in Lab 9). 4. In the same Excel File “SalesPerCustomer, add a bar chart that shows the OrderCount per Customer. The chart can be inserted on the same worksheet as the data (bar charts can be found in the same area of Excel as the line chart we did in Lab 9). 5. The database user is trying to analyze the sales of three specific products. A query (provided) has been written the generate sales data on those three products. Run the provided query “SpecificProductSales” in the Access DB and export the results to an Excel file named “SpecifcProductSales”. Make sure you check “Export data with formatting and layout”. 6. In the Excel File “SpecificProductSales”, create a combined pivot table/pivot chart in a new worksheet that shows Product Sales in the Values field, Product name in the Axis field, and Company Name in the Legend field. Position the chart in a legible way on the worksheet. write a query titled CustomerAnalysis that extracts the correct data and then export this data into Excel for further processing in order to complete the following two tasks: 1. Answer the following questions: Which customer generated the most sales for the company in 2010? Which customer generated the most profit (sales minus cost) for the company in 2010? 2. Create a bar chart that shows the sales for each customer that made a purchase in 2010. Create a pie chart that shows the profit generated by each customer that made a purchase in 2010. Submit all three completed files to Blackboard. 1. HW4.Customer.Sales – Access database after import (Note: if you complete the Extra Credit problem the database should also have the CustomerAnalysis query) 2. SalesPerCustomer – Excel worksheet after export and adding the charts. 3. SpecificProductSales – Excel worksheet after export and adding pivot Chart. Make sure you attach all three files to your submittal before you hit the submit button on Blackboard. also submit an Excel worksheet entitled CustomerAnalysis that contains the data you transferred from Access and clearly shows the answers to the questions in Item 1 above as well as the bar chart and pie chart described in Item 2. ProductName UnitPrice UnitCost UnitsInStock Jack's New England Clam Chowder $9.65 $4.25 85 Singaporean Hokkien Fried Mee Ipoh Coffee Gula Malacca Røgede sild Spegesild Zaanse koeken Chocolade Maxilaku Valkoinen suklaa Manjimup Dried Apples Filo Mix Perth Pasties Tourtière Pâté chinois Gnocchi di nonna Alice Ravioli Angelo Escargots de Bourgogne Raclette Courdavault Camembert Pierrot Sirop d'érable Tarte au sucre Vegie-spread Wimmers gute Semmelknödel Louisiana Fiery Hot Pepper Sauce Louisiana Hot Spiced Okra Laughing Lumberjack Lager Scottish Longbreads Gudbrandsdalsost Outback Lager Fløtemysost Mozzarella di Giovanni Röd Kaviar Longlife Tofu Rhönbräu Klosterbier Lakkalikööri Original Frankfurter grüne Soße $14.00 $46.00 $19.45 $9.50 $12.00 $9.50 $12.75 $20.00 $16.25 $53.00 $7.00 $32.80 $7.45 $24.00 $38.00 $19.50 $13.25 $55.00 $34.00 $28.50 $49.30 $43.90 $33.25 $21.05 $17.00 $14.00 $12.50 $36.00 $15.00 $21.50 $34.80 $15.00 $10.00 $7.75 $18.00 $13.00 $6.60 $20.95 $8.80 $4.75 $3.00 $4.35 $5.50 $8.80 $7.85 $23.00 $2.00 $11.00 $3.40 $11.75 $21.00 $12.80 $6.70 $28.95 $17.00 $12.75 $28.55 $23.30 $14.90 $9.00 $7.70 $6.65 $5.90 $16.98 $7.00 $11.70 $15.55 $6.90 $4.50 $3.85 $8.00 $7.75 26 17 27 5 95 36 15 10 65 20 38 0 21 115 21 36 62 79 19 113 17 24 22 76 4 52 6 26 15 26 14 101 4 125 57 32
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Company Name
The Big Cheese
Island Trading
Consolidated Holdings
Hungry Coyote Import Store
Island Trading
North/South
The Big Cheese
North/South
Lazy K Kountry Store
Lazy K Kountry Store
The Big Cheese

City
Portland
Cowes
St. Louis
Elgin
Cowes
London
Portland
London
Walla Walla
Walla Walla
Portland

OrderDate
13-Apr-10
08-Dec-09
25-Mar-11
20-Nov-08
22-Mar-10
09-Oct-08
23-Jul-09
14-Nov-08
24-Dec-09
30-Dec-09
09-Jul-09

Trail's Head Gourmet Provisioners

Kirkland

Lazy K Kountry Store
The Big Cheese
Island Trading

Walla Walla
Portland
Cowes

27-May-09
16-Mar-09
13-Nov-08

Trail's Head Gourmet Provisioners

Kirkland

07-May-10

Great Lakes Food Market
Island Trading
Island Trading
Island Trading
Great Lakes Food Market

Eugene
Cowes
Cowes
Cowes
Eugene

21-Dec-09
22-Mar-10
14-Oct-09
12-Apr-10
21-Jan-10

04-Jun-09

ProductName

Quantity

Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Chai
Uncle Bob's Organic Dried Pears
Uncle Bob's Organic Dried Pears

25
45
4
40
80
17
5
20
18
7
40

UnitPrice
$18,00
$18,00
$18,00
$18,00
$18,00
$18,00
$18,00
$18,00
$18,00
$30,00
$30,00

Uncle Bob's Organic Dried Pears

14

$30,00

Uncle Bob's Organic Dried Pears
Uncle Bob's Organic Dried Pears
Uncle Bob's Organic Dried Pears

66
69
45

$30,00
$30,00
$30,00

Uncle Bob's Organic Dried Pears

30

$30,00

Uncle Bob's Organic Dried Pears
Teatime Chocolate Biscuits
Teatime Chocolate Biscuits
Teatime Chocolate Biscuits
Teatime Chocolate Biscuits

10
12
12
38
18

$30,00
$9,20
$9,20
$9,20
$9,20

ProductSales
$450,00
$810,00
$72,00
$720,00
$1.440,00
$306,00
$90,00
$360,00
$324,00
$210,00
$1.200,00
$420,00
$1.980,00
$2.070,00
$1.350,00
$900,00
$300,00
$110,40
$110,40
$349,60
$165,60

Company Name

(All)

Row Labels
Chai
Teatime Chocolate Biscuits
Uncle Bob's Organic Dried Pears
Grand Total

Sum of ProductSales
4572
736
8430
13738

Company Name
Sum of ProductSales

10000
8000
6000
4000
2000
0

ProductName

mpany Name

um of ProductSales

oductName

Tota...


Anonymous
I was struggling with this subject, and this helped me a ton!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Content
Related Tags