3-2 Milestone One MIS 300
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
300
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
3
Uploaded by SuperCheetah4209
MIS-300-J6494
Arpit Sharma
Southern New Hampshire University
July 17th, 2022
3-2 Milestone One: Status Report
Analysis
For this assignment, I will use the six Excel spreadsheets provided by the prompt to
answer any potential questions the management may have about sales. The data provided
represents Iowa’s liquor sales for the year 2015. However, before I use the data, I need to
analyze the Excel spreadsheets, clean, and import it to Microsoft access. By using this data, I can
create tables in Microsoft access and use queries to answer any questions about sales. After
analyzing the data, these are the areas I found that were not aligned properly with the data:
The date on the Iowa_ Liquor _Sales_52772 spreadsheet is different from the other Excel
spreadsheets D/M/YYYY.
The category number on Iowa_Liquor_Sales_52747 spreadsheet for BLENDED WHISKIES is wrong. It’s 1011200, but the correct category number for BLENDED WHISKIES is 1011100.
The Iowa_ Liquor _Sales_52358 spreadsheet is missing the Sale (Dollars) field and the total.
The Iowa_ Liquor _Sales_52337 spreadsheet has a field name County Number. Also is missing data for the month of December.
The Iowa_ Liquor _Sales_52216 spreadsheet has the wrong formula for the Sale (Dollars) field. It’s State Bottle cost * Bottles Sold but was supposed to be State Bottle Retail * Bottles Sold.
To be sure the data works and reports what I need, I will correct the missing fields and align the data. After doing this, I can import it into Access, where I can create accurate tables, put the data together, and run queries to get the information I need to answer the questions the management may have. For example, answer potential questions like:
What were the total sales by category?
What were the total sales by brand?
What were the total sales by the vendor?
Compiling and Cleaning the Data
Before Importing the data into access, I changed the date format on the Iowa_ Liquor
_Sales_52772 to M/D/YYYY, matching the other spreadsheets. Following, in the
Iowa_Liquor_Sales_52747, I used a tool to find and replace the BLENDED WHISKIES
category number from 1011200 to 1011100. Next, I added the missing Sale (Dollars) field on
The Iowa_ Liquor _Sales_52358 data. In the Iowa_ Liquor _Sales_52337 spreadsheet, I delete
the field name County Number since it's unnecessary. And last, in the Iowa_ Liquor
_Sales_52216 spreadsheet, I created a new Sale (Dollars) field with the right formula, State
Bottle Retail * Bottles Sold. After cleaning and correcting the data, I imported it into access,
where I created tables and a Union query compiling all the data in one. With the data clean and
compiled, I can run queries and effectively answer question, for example, about sales by brand,
vendor, or category.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help