Dec 12, 2021 Uncategorized

Case study analysis Assignment at an Affordable Cost

This Case Analysis 3 is based on material discussed in the following units:
• Data Integrity Management (keys, validation, and error correction)
• Data Import and Aggregation (import, web queries, and Pivot tables)
• Data Manipulation and Printing (Text, Dates, and Time Manipulations)
You should complete your report using the “CA3_Template” Word document
that is available in the Case Analysis 3 folder on D2L, and submit your report
formatted as a PDF document using the appropriate assignment link on D2L.
Business Communication Assessment
Business Communication: Since your case analysis report is a formal business
document. Therefore, your responses should be written in well-organized
paragraphs, using the rules of English grammar, punctuation, and, sentence
structure. A word count is provided to guide you on the length that is appropriate
for each question.
As a reminder: all answers to narrative questions must be your original work in
your own words. Copying the work of another student (either in the current or
prior semester or reusing your own work from this class or another class) or
copying information from the web or any other source will be considered as
plagiarism. As a reminder, all assignments are checked using TurnItIn.com.
Check the Course Schedule document for the CA 3 due date.
IMPORTANT NOTE
The figures and tables shown as examples in this document are taken from
prior assignments and should ONLY be used as a guideline for the graphical
style expected for the task. The content of any figure or table should be based
on the information provided in the guidelines for this assignment.
Case Analysis 3 Guidelines
MIS 112 – Fall 2021 Page 2 of 8
Important Note for Mac Users
As a reminder, you are expected to use Microsoft Excel 2016 for Windows or later versions when
completing assignments in MIS 112. For those who use Office for Mac OSX, Microsoft Office 2011
for OSX does not have support for “slicers.” Since you will need to use slicers to complete this
assignment, if you have a Mac with an older version of Excel, you will need to run the Windows
version of Office (e.g., through VirtualBox), or use a Windows computer at an on-campus lab.
Excel 2016 for Mac and newer versions (365 and 2019), however, allow the access to slicers.
Task 1
Examine the image below which shows a validation error in cells A6 and A10 of a spreadsheet.
Task 1(a): In your own words, explain (range: 25-50 words), what kind of data validation rule
you think is in place for cells A5-A14 (focus on the validation criteria), and how you reached
that conclusion.
Task 1(b): In your own words, describe (range: 50-75 words) an example (e.g., taken from
your personal experience or from the financial data used for Question 2) that you have
encountered that used/uses data validation rules when collecting data, and explain the
importance of using validation rules in this case.
Case Analysis 3 Guidelines
MIS 112 – Fall 2021 Page 3 of 8
Task 2 – Preparing your data for analysis
You are working for a hypothetical company, Financial Services of Southern Arizona (FSSA). FSSA
is analyzing stock market transactions placed by its customers. They have requested you to help
with the data analysis.
• Begin by downloading the attached Excel workbook called: CA3_Datafile.xlsx
The data (contained in the CA3_Datafile.xlsx workbook file under the RawData worksheet)
contains the following columns:
• TransactionID (each transaction has its own unique identifier),
• CustomerID (the identifier of the customer making the transaction),
• State (where the customer lives),
• Ticker (stock ticker symbol),
• Sector (of the stock, e.g., AAPL belongs to the sector Information Technology)
• Qty (number of units of the stock bought or sold),
• UnitPrice (unit price for the stock market transaction),
• Total Dollar Value of the transaction (i.e., Qty * UnitPrice).
Select the Analysis worksheet, and complete the following steps:
• Enter your 8-digit studentID (do not use your CatCard number) in Cell B1.
• Enter your name in cell B2 (using the format: LastName, FirstName).
Note: After you have entered the data in cells B1 and B2, you will see values you need to use for
the Pivot Table row-label filter, and locations to use in the slicer. There will be 5 sectors listed in
cell B3 (for the row label filter), and 5 locations / states in cell B4 (for the slicer).
Case Analysis 3 Guidelines
MIS 112 – Fall 2021 Page 4 of 8
Task 2(a): Copy and paste from your Excel
spreadsheet, the cells (A1 through B4), which
contain your student ID, name, row-label filter,
Slicer values to document the sectors and
locations you will use for your analysis.
Please paste the cells into your report template
document in the space provided. The data should
be pasted as a Word table (not as an image, nor
an embedded Excel object/file). The simplest way
to do this is to select your table in Excel, copy it,
and paste it on your Word report template.
Make sure all parts of your answer are easily
readable (adjust column widths and row heights
as necessary), as shown in the example on the
right; however, do not redact cells B3 and B4 (i.e., be sure your list of row label filters and slider
locations are visible in your report)
Next, in the RawData worksheet: enter formulas to complete the two missing column entries
(note: you must complete this step before proceeding to the creation of the pivot table).
• In the State column (column C), enter a VLOOKUP() function that will find the state each
customer lives in (based on the CustomerID (column B) in the RawData worksheet)
using the Customers worksheet, which contains each customer’s data (including IDs,
names and states).
• In the Sector column (column E), enter a VLOOKUP() function to find the sector for each
stock (based on the Ticker symbol (column D) in the RawData worksheet) using the
Stocks worksheet, which contains each stock’s data (including its ticker symbol, stock
name and sector).
Task 2(b): Copy and paste into your CA 3 report template document the following VLOOKUP
formulas from the first cell in each column of the RawData worksheet for the following items:
• 2(b) State [RawData worksheet, Cell C2] (From the State column)
• 2(b) Sector [RawData worksheet, Cell E2] (From the Sector column)
Note: row label filter
and slicer locations redacted
Case Analysis 3 Guidelines
MIS 112 – Fall 2021 Page 5 of 8
Task 3 – Pivot table
Follow the directions shown below to prepare a PivotTable.
Step 1: Insert a new Pivot table in the Analysis worksheet using the “Pivot Table” function on
the “Insert” ribbon, as follows:
• Select cell A7 in the Analysis worksheet
• Insert a PivotTable based on the (entire) table of data in the RawData worksheet
• The new Pivot table should appear on the Analysis worksheet.
Step 2: Populate the Pivot table, as follows:
• In the PivotTables Fields pane, drag-and-drop the Sector field into the ROWS area.
• On the Pivot table, change the column heading from Row Labels to Sector.
• On the Pivot table, use the drop-down menu to filter the column, so that only the 5
sector types shown in cell B3 are selected (i.e., corresponding to the Sector filter).
• In the PivotTables Fields pane, create the following four items in the VALUES area (by
dragging-and-dropping the appropriate fields into the VALUES area). They will later be
used to answer questions Q1-Q4.
o Number of unique transactions (using the COUNT function).
Format the column with a comma separating the thousands value (i.e, “7654”
would be displayed as “7,654”), and do not include any decimal places (i.e.,
“4567” would be displayed as “4,567” and not “4567.00”)
o Average of quantity purchased per transaction.
Format the column with a “,000” separator (i.e., a comma separating the
thousands value so that “7654” would be displayed as “7,654”) and show two (2)
decimal places (i.e., “4567” would be displayed as “4,567.00”).
o Average of unit price.
Format the column with a currency format (i.e., a value of 1234 should be shown
as $1,234.00), and show two (2) decimal places.
o Total dollar value of transactions for the sector (using the SUM function).
Format the column with a currency format (i.e., 1234 should be show as
$1,234.00), and show two (2) decimal places.
• Insert a slicer to the pivot table to filter your data based on the state where the
transaction was made. For the slicer values, select only the 5 states shown in cell B4.
Case Analysis 3 Guidelines
MIS 112 – Fall 2021 Page 6 of 8
Step 3: Answer the questions Q1 – Q4 shown on the Analysis worksheet in cells D2 – D5.
• Enter answers to questions Q1 – Q4 in the Analysis worksheet in cells F2 – F5.
To receive credit, the values for your answers MUST be calculated after applying the
filtering criteria for both sectors (as a column filter in the Pivot table) and locations (as a
slicer value).
o Note: Q2 (cell E3) asks for the average transaction quantity across all displayed
sectors; not for just a single sector, as filtered for your specific locations.
• Be sure you professionally format your Pivot table, slicer, and the answers fields for
questions Q1 – Q4 by adjusting the row heights and column widths so that the data and
questions are easily readable and the formatting is consistent with the Pivot table
formatting

Want a similar task completed for you? Worry no more! Simply place your order at myessaydoer.com by clicking on the ORDER NOW option

Myessaydoer’s team of experts is available 24/7 to assist you in completing such tasks. We assure you of a well written and plagiarism free paper. Place your order at myessaydoer.com by clicking on the ORDER NOW option and get a 20% discount on your first assignment.