New perspectices excel sam 2013, tutorial 11: sam project 1a

Get Custom Essay on New perspectices excel sam 2013, tutorial 11: sam project 1a

Let Our Team of Pofessional Writers Take Care of Your Paper.

This paper is good, please rate after you have been graded.

Question

 

New PerspectivesExcel 2013

Tutorial11: SAM Project 1a

SafeClean Products

USING data from external files

 

Project Goal

M Project Name

Project Goal

 

           

 

                                                                                                                            

 

PROJECT DESCRIPTION

Fiona Walsh works forSafeClean Products, a publically-traded cleaning products company in New York.The company maintains numerous files and databases to trackcustomer orders,sales commissions, employee data, and market data. Fiona has asked you to create a comprehensive workbook that combines all of the data into a single file. You will need to import andformat data from numerous types of data files, including Access databases, text files, and XML files.

 

GETTING STARTED

·         Download the following file from the SAM website:

o    NP_Excel2013_T11_P1a_FirstLastName_1.xlsx

·         Open the file you just downloaded and save it with the name:

o    NP_Excel2013_T11_P1a_FirstLastName_2.xlsx

o    Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

·         To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

 

o    support_NP_E13_T11_P1a_10Year_Stock.txt

o    support_NP_E13_T11_P1a_Employees.xml

o    support_NP_E13_T11_P1a_Employees.xsd

o    support_NP_E13_T11_P1a_SafeClean.accdb

o    support_NP_E13_T11_P1a_Sales.txt

o    support_NP_E13_T11_P1a_SCOrders.html

o    support_NP_E13_T11_P1a_Stock.txt

·         With the file NP_Excel2013_T11_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

 

·         If a security warning appears at the top of the Excel window, click the Enable Content button in the message bar to enable the macros contained in the file, see Figure 1.

Figure 1: Security Warning Message Bar


Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

·         To complete this project you will need to display the Developer tab. To add this tab to the Excel ribbon, click on the File tab to open Backstage view and then click the Options button. In the Excel Options dialog box, click on the Customize Ribbon option and click the Developer check box, see Figure 2. Click the OK button to close the Excel Options dialog box and confirm the Developer tab appears in the Excel Ribbon.

 

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Figure 2: Customize Ribbon Section of Excel Options Dialog Box


 

PROJECT STEPS

1.    Go to the Overview worksheet. Import data from the text file support_NP_E13_T11_P1a_Sales.txt into the worksheet using the following parameters:

a.    The data in the text file has headers and is delimited with a comma.

b.    Apply the Text column data format to the first column and the General format to the remaining columns.

c.    Do not import the second columnof data (with the column heading 2012).

d.    Import the data into the Overview worksheet, starting in cell B4.

2.    Import data from the text file support_NP_E13_T11_P1a_10Year_Stock.txtinto the Overview worksheet using the following parameters:

a.    The data has fixed width columns with no headers.

b.    Begin importing the data at the 6 row.

c.    Place column breaks at the following marks on the ruler: 4, 9, and 14 (as shown in Figure 3).

 

Figure 3: Column Breaks for Support_NP_E13_T11_P1a_10Year_Stock.txt

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

d.    Apply the Date column data format to the first column and the General format to the remaining columns.

e.    Import the data into the existing worksheet (the Overview worksheet), starting in cell B12.

 

3.    Format the Overview worksheet as follows:

a.    Adjust the width of column B to 4.5 characters.

b.    Change the width of columns C:G to 12 characters.

c.    For range C4:G4, Center cell contents and apply Bold formatting.

d.    Apply the Accounting number format with no decimal placesto range C5:G8.

4.    Insert a High-Low-Close chartinto the Overview worksheet, based on range P3:S286. (Hint: In most cases, selecting cell P4prior to creating the chart will result in Excel automatically selecting the necessary data range for your chart.) Format the chart as described below:

a.    Change the chart title to SafeClean Products – Stock Trends 2017.

b.    Remove the chart legend.

c.    For the vertical axis, modify the Minimum bounds to be 35 and the Major units to be 5.

d.    Enter the text Stock Price as the Primary Vertical axis title.

e.    Enter the text Dates as the Primary Horizontal axis title.

f.     Resize and reposition the chart so the upper-left corner is in cell F10 and the lower-right corner is in cell O29.

5.    Select cell P5 and edit the External Data Rangeproperties as follows (Hint: Remember to edit the External Data Range properties, not the Connection properties):

a.    Rename the support_NP_E13_T11_P1a_Stock external data range to be SafeClean 2017 Stock Performance.

b.    Modify the Refresh control property to Prompt for File name on refresh.

c.    Modify the Data Formatting and Layout properties to Adjust column width.

6.    Go to the FY18 Earningsworksheet. Import data from the support_NP_E13_T11_P1a_SafeClean.accdb database, using the Microsoft Query Wizard as follows(Hint: When importing this data into the workbook, use the From Other Sources button to access the Query Wizard):

a.    In the Microsoft Query wizard, choose the MS Access Database* option as the data source, and then navigate to and select the support_NP_E13_T11_P1a_SafeClean.accdb database.

b.    When adding columns to your query, select the CustomerName and Contact columns (in that order) from the Customer table.

c.    Then select the EmployeeLastName column from the Employees table.

d.    Then select the Product and Amount fields from the Orders table (Hint: The columns in your query should be in the following order: CustomerName, Contact, EmployeeLastName, Product, and Amount).

e.    Do not filter the data.

f.     Sort the data by the Product field in Ascendingorder and then by the Amount field in Ascending order.

g.    Return the data to Microsoft Excel as a Table in the existing worksheet (the FY18 Earnings worksheet), starting in cell B4.

h.    Rename the table FY18_Q1Earnings.

i.      Change the number format of the range F5:F13 to the Accounting number format with 0 decimal places.

j.     Confirm the table matches Figure 4.

Figure 4: FY18_Q1Earnings

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

 

7.    Modify the properties of the connection (created in step 6) in the FY18 Earningsworksheet as follows:

a.    In the Workbook Connections dialog box, select the connection created in step 6. The connection should be named Query from MS Access Database (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value FY18 Earnings).

b.    Change the name of the connection to SafeCleanFY18 Q1 Earnings.

c.    Change the connection description to SafeClean Products FY18 Q1 Earnings imported from the SafeClean database. (include the period).

d.    For the Refresh Control options in the Usage tab, uncheck all checkboxes associated with the Last Refreshed property.

8.    Make a copy of the FY18 Earningsworksheet and update the worksheet as follows:

a.    If necessary, move the new worksheet so that it appears between the FY18 Earningsworksheet and the FY18 Ordersworksheet.

b.    Update the worksheet tab to read FY18 Commissions.

c.    Update cell A1 to read SafeClean Products – Fiscal Year 2018 – Commissions.

d.    Change the name of the table in the worksheet to FY18_Q1Commissions.

9.    Modify the connection associated with the FY18 Commissions worksheet (which is a copy of the connection from the FY18 Earningsworksheet) as follows:

a.    Open the Workbook Connections Dialog box and select the connection associated with the FY18 Commissions worksheet created in step 8. The connection should be named SafeCleanFY18 Q1 Earnings1 (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value FY18 Commissions).

b.    Change the name of the connection to SafeCleanFY18 Q1 Commissions.

c.    Change the connection description to SafeClean Products FY18 Q1 Commissions imported from the SafeClean database. (include the period).

d.    Do not close the Connection Properties Dialog box.

10.With the SafeClean FY18 Q1 Commissions connection still open in the Connection Properties dialog box, select the Definition tab and edit the connection query as follows:

a.    Remove the Contact column from the query.

b.    From the Employeestable, add the EmployeeFirstName column to the query. Move the EmployeeFirstNamecolumn up in the query, so that it appears between the CustomerName and EmployeeLastName columns.

c.    From the Orderstable, add the Commission columnto the query (Hint: The columns in your query should be in the following order: CustomerName, EmployeeFirstName, EmployeeLastName, Product, Amount, andCommission).

d.    Add a filter to the query, so that only data with a Commission column value greater than 1250.0000 are displayed in the table.

e.    Change the sort order of the query to sort only by the Commission column values in Ascending order.

f.     Return the data to Microsoft Excel (Hint: You will need to close the Connections Properties Dialog box and the Workbook Connections Dialog box before returning to the FY18 Commissions worksheet).

g.    If necessary, change the number format of the rangesE5:E9and G5:G9 to the Accounting number format with 0 decimal places. Be certain to confirm that the number format uses 0 decimal places, rather than the default2 decimal places. (Hint: Depending on how you complete this substep, the number format may appear as Custom instead of Accounting.)

h.    Confirm the table matches Figure 5.

 

Figure 5: FY18_Q1Commissions

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

11.Go to the FY18Orders worksheet. Create a PivotTable report based on the tables in the support_NP_E13_T11_P1a_SafeClean.accdb database as described below:

a.    From the support_NP_E13_T11_P1a_SafeClean.accdb database, import data from the Customers, Employees, and Orders table for the PivotTablereport. (Hint: Use the From Access option in the Get External Data section of the Data tab when importing the tables from the support file.)

b.    Add the PivotTable report to cell B4 in the existing (FY18 Orders)worksheet.

c.    From the Employees table, add the EmployeeLastName field to the Columns area of the PivotTable Fields Pane.

d.    From the Orders table, drag the Product fieldto the Rows area of the PivotTable Fields Pane.

e.    From the Customers table, drag the CustomerName field to the Rows area below the Product field.

f.     From the Orders table, drag the Quantity field to the Values areaof the PivotTable Fields Pane (Hint: The field name will be updated to Sum of Quantity).

g.    Change the width of column B to 33 characters.

h.    Change the label in cell B4 to Products Sold by Sales Team.

i.      Change the label in cell B5 to Product and Customer Name.

j.     Change the label in cell C4 to Employee.

k.    Confirm the PivotTable matches Figure 6.

Figure 6: FY18 Orders PivotTable

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

12.Go to the Order Detailsworksheet. Import data from the support_NP_E13_T11_P1a_SCOrders.htmlsupport file as follows:

a.    Get the external data from the support_NP_E13_T11_P1a_SCOrders.htmlsupport file using the From Web option.

b.    In the new Web Query dialog box, enter the path to the support_NP_E13_T11_P1a_SCOrders.htmlsupport file into the address box (Hint: The path is the location on your computer that the support file is stored).

c.    In the New Query Dialog box, select the second arrow (as shown in Figure 7) so that only the SafeClean table is highlighted.

Figure 7: Portion of New Web Query Dialog Box

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

 

d.    Import the data into cell B4 of the Order Details worksheet.

e.    Open the Workbook Connections Dialog box and select the connection associated with the Order Detailsworksheet. The connection should be named Connection. (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value Order Details.)

f.     Change the name of the connection to SafeCleanOrder Status.

g.    Confirm your worksheet matches Figure 8.

 

 

 

 


 

 

Figure 8: Portion of New Web Query Dialog Box

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

13.Go to the Payroll worksheet and complete the following actions to import data from the support_NP_E13_T11_P1a_Employees.xml support file. (Hint: The support_NP_E13_T11_P1a_Employees.xsd file needs to be in the same directory as the support_NP_E13_T11_P1a_Employees.xmlfile when you add the .xml map to the workbook.)    

a.    In the XML Source pane, add the Employees data map from thesupport_NP_E13_T11_P1a_Employees.xmlsupport file to the workbook.

b.    Rename the XML Map from dataroot_Map to Payroll Information.

c.    Bind the XML element (under the Employees data map) to the Payroll worksheet as shown in Table 1.

 

Table 1: XML Mapping for the Payroll Worksheet

Element

Cell

ID

B4

Last Name

C4

First Name

D4

Title

E4

Start Date

F4

Status

G4

Compensation Type

H4

Total Compensation

I4

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

 

d.    Select cell B5 and only refresh the data associated with the XML data source (Hint: The Refreshoption is available in a drop-down menu accessed by clicking on the Refresh Alltext. If you click on the Refresh All icon in the Connections section of the Data tab, Excel will not provide you with the option to only update the data associated with this XML data source.

Your workbook should look like the Final Figure on the following page. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Overview Worksheet

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.


 

 


 

Final Figure 2: FY18 Earnings Worksheet


Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.


 

Final Figure 3:  FY18 Commissions

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

 

 


 

Final Figure 4: FY18 Orders Worksheet

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.


 

Final Figure 5:FY18 Order Details Worksheet

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

 

 

Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 6: Payroll Worksheet

 

 

 

Calculate the price for this paper
Pages (550 words)
Approximate price: -

Try it now!

Calculate the price for this paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Best Quality Essays has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.

Essays

Essay Writing Services

At Best Quality Essays, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.

Admissions

Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.

Editing

Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.

Coursework

Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.