bis 155 week 7 lab 7 latest devry n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
BIS 155 WEEK 7 LAB 7 LATEST-DEVRY PowerPoint Presentation
Download Presentation
BIS 155 WEEK 7 LAB 7 LATEST-DEVRY

Loading in 2 Seconds...

play fullscreen
1 / 10

BIS 155 WEEK 7 LAB 7 LATEST-DEVRY - PowerPoint PPT Presentation


  • 0 Views
  • Uploaded on

\nVisit Below Link, To Download This Course:\n\nhttps://www.tutorialsservice.net/product/bis-155-week-7-lab-7-latest-devry/\n\nOr \nEmail us on\nSUPPORT@TUTORIALSSERVICE.NET\n\nBIS 155 Week 7 Lab 7 Latest-DeVry\nBIS155\nBIS 155 Week 7 Lab 7 Latest-DeVry\nLab Overview\nListen\nScenario/Summary\nYour manager could not be happier with the help you’ve provided to all of those departments requesting Excel assistance within the resort. He is now very confident handing over complex tasks to your capable hands. This week he is requesting that you exercise some expert skills. It seems he has some data that needs to be imported and cleaned up so that it is useful to the resort. He has quite a list of tasks he needs done with this data. Once the data are presentable, the analysis needs to be summarized in a short memo and placed on the last sheet in the workbook.\n

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'BIS 155 WEEK 7 LAB 7 LATEST-DEVRY' - tutorialsservicesnet


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
bis 155 week 7 lab 7 latest devry

BIS 155 WEEK 7 LAB 7 LATEST-DEVRY

Visit Below Link, To Download This Course:

https://www.tutorialsservice.net/product/bis-155-week-7-lab-7-latest-devry/

Or

Email us on

SUPPORT@TUTORIALSSERVICE.NET

BIS 155 Week 7 Lab 7 Latest-DeVry

BIS155

BIS 155 Week 7 Lab 7 Latest-DeVry

Lab Overview

Listen

Scenario/Summary

Your manager could not be happier with the help you’ve provided to all of those departments requesting Excel assistance within the resort. He is

now very confident handing over complex tasks to your capable hands. This week he is requesting that you exercise some expert skills. It seems

he has some data that needs to be imported and cleaned up so that it is useful to the resort. He has quite a list of tasks he needs done with this

data. Once the data are presentable, the analysis needs to be summarized in a short memo and placed on the last sheet in the workbook.

additionally your manager has also heard that

Additionally, your manager has also heard that macros can be added to a worksheet. He wants to make the X Games calculator even more

efficient and foolproof for all those using it.

Just like traversing a double black diamond slope, you have the Excel knowledge to maneuver all of these tasks with excellence. This lab is for

experts only.

Listen

Deliverables

NOTE

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step

instructions.

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

After completing the steps below, turn in one Excel 2016 workbook. Rename the workbook with your lastname_first initial_Week7_Lab. xlsx.

Example: If your name were Jane Doe, your workbook would be Doe_J_Week7_Lab.xlsx.

LAB 6 – What-If-Analysis

Step

Task

Points Possible

Points Received

Comments

1

Import and Organize Data

1a – b

Import Data

5

1c -i

Professional Clean up & Formatting

5

2

Create Macro for X Games Calculator

2a – l

Create & Record Macro

6

3

Assign Macro

slide3

3a – f

Draw Macro button

5

3g – i

Associate Macro with button

5

4

Save Macro enabled file & Protect Calculator

4a, b

Save as .xlsm file type

3

4c

unlock user input cells

4

4d

Associate Macro with button

4

5

Create a Documentation Sheet – Apply Watermark

5a-c

Insert and create documentation sheet

4

5d-j

Create watermark

4

6

Summarize and Prepare Memo

6a – d

Summarize and write memo on last worksheet

10

Comment in Dropbox: What you learned from completing this Lab

5

TOTAL POINTS

60

0

Week 7 Grading Rubric

Listen

Lab Resources

Microsoft Office: Excel 2016

Options for Accessing Microsoft Excel 2016

1. Use a personal copy on your PC. You can request a copy of Microsoft Office 2016 via the Student Software Store icon on the Course

Resources Page.

2. If you are a MAC user, click to read the MAC User Information.

3 if you do not have excel 2016 installed locally

3. If you do not have Excel 2016 installed locally, then access the software by going to the Course Resources page, Lab Resources section,

and click the Virtual Lab Citrix icon.

Listen

Lab Steps

Listen

Preparation

You will be using Microsoft Excel 2016 for this lab.

Be sure you have read the required chapter materials and reviewed the hands-on exercise videos located on the Lesson page before you begin

the lab.

Please do not rely solely on the hands-on exercise videos to complete this week’s lab. The videos provide detailed examples walking you through

the hands-on exercises. Applying the hands-on exercise examples will provide both practice and instruction of what to complete.

Listen

Begin: Open and Save

Download the spreadsheet Week 7 Lab – Summit Ridge Mountain Resort Student.xslx. You will be prompted to save the file. Click yes.

Download the spreadsheet Week 7 Lab – Import.txt. You will be prompted to save the file. Click yes.

Note: If you are using the Remote Lab environment, you will need to follow the instructions for uploading the file. These instructions can be found

on the Lab page when you click on the Lab icon on Course Resources.

To save the spreadsheet with a new file name,

open the workbook in Excel 2016; and

slide5

in Excel, click File and then Save As and rename it as lastname_first initial_Week7_Lab.xlsx (Jane Doe would save the file as

Doe_J_Week7_Lab.xlsx).

Listen

Step 1: Import and Clean Up Text File

1. Download starting student Excel file and the text file. Open the starting Excel file and navigate to Sheet1.

2. Import the text file by using the Get External Data function on the Data Ribbon. When the Text Import Wizard displays, select My Data has

headers and use a comma delimiter to organize the data.

3. Insert a new column in between A and B and use the Text to Columns command to separate first and last name. Be sure to revise or add

appropriate column names.

4. Insert a column to the right of the HOME ADDRESS column and use a function to capitalize just the first letter in the address names. Then

hide the original Address column.

5. Insert two new columns in between City State Zip and the Unit column, and then use Text to Columns to separate these fields. Adjust

column widths appropriately.

6. On the right of the Agent column, add a column and use the Substitute function to replace “JG” with “Jesse Gonzales,” and then hide the

original Agent column and display the full name.

7. Add statistics to add up all sales, and then apply a 7% commission to calculate the Gonzales commission.

8. Change the sheet name to Gonzales.

9. Under Page Layout, select Themes and choose a business-like theme and possibly a color combination to apply professional formatting

and column widths to all of this data using the image below as a guide.

Cleaned Up Import

Listen

Step 2: Create the Macro in the X Games Ticket Calculator

In this section, we will actually build a macro. A macro is simply a recording of every keystroke you perform while the Macro Recorder is turned on.

You are quite simply automating keystrokes and building efficiency into your Excel file.

1. Select the X Game Calc sheet.

2. In order to have the Macro Tools available to you, you must have the Developer Tools. If you do not see the Developer Tab, click the File

and select Options.

3 locate the developer checkbox option

3. Locate the Developer checkbox option in the Custom Ribbon tab in the Options menu, make sure it is checked, and then click OK.

Developer

1. You will need to set the Macro Security level in order to develop Macro code. From the Developer Tab, click on Macro Security in the

Code group. Check the Enable all macros and then click OK.

Enable All Macros

1. Remember, a macro records every keystroke you perform while the Macro Recorder is turned on. For this Macro, we will clear the values

from the ticket type and ticket quantity cells in our X Game Calculator sheet. We will turn on the Macro Recorder, delete the data in Cells

E4 and E6, and then turn the Macro Recorder off.

2. Click the Record Macro button from the Developer Tab, Code group. The Macro Recorder dialog window will open.

3. Macro Name: Provide a user-friendly name for this recording such as CalcWipe. Note: Do not use spaces in the name.

4. Shortcut key: Designate a shortcut key the user can press to start the macro. Try using the letter w as your shortcut key. Note: Avoid using

a shortcut commonly used in Windows applications, such as CTRL-C or CTRL-P. The macro shortcut will overwrite the Windows shortcut

and your users will not understand why the key combination is not copying or pasting.

5. Store: Store the macro in this Workbook. Note: It can be stored in the Personal Macro Workbook so it can be used by other applications.

Macros for common actions such as printing a workbook can be stored in the personal workbook, but they will only be available on the

computer on which they are stored.

6. Description: Provide an explanation of the functionality of the macro. Use something such as Clear input cells for X Game Calculator. See

image below.

7. When you click OK, the Macro Recording begins. Select Cell E4, press the Delete key, and then select Cell E6 and press the Delete key.

8. Then turn the Macro Recorder off by clicking the Stop Recording button from the Macro group.

Record Macro

Listen

Step 3: View the Macro and Assign a Macro Button in the X Games Calculator Sheet

Behind the scenes, Excel has written a little Visual Basic for Applications (VBA) program, which you may access by clicking the Macros command.

1 select the macro you just created and then

1. Select the Macro you just created, and then click the Edit button.

Macro Name

1. VBA will open with your code.

VBA Code

Transcript

1. In later courses, you may learn more about programming; however, you can follow the code and see how it instructs Excel to select a

range and clear its contents. Click the X in the top-right corner of this MS VBA screen to close macro editing and return to the Excel file.

2. Begin to add a macro button to make it easy for users to run this code. Add a Button Shape to your application using the Insert Tab,

Shapes.

Add a Button Shape

3. Draw the shape onto the application, using your mouse or drawing tool.

4. Add a text description to the button: “Clear calculator values!”

5. Associate this button with the macro. Right-click on the shape and select Assign Macro from the menu.

Draw the Shape

Associate Button With Macro

1. Select CalcWipe and click OK to assign the macro to the button.

2. To prepare to test the macro, provide input into cells E4 and E6 by selecting a ticket type in cell E4 and enter a quantity in cell E6 for

starting values. Then test your macro by simply clicking the button. Did it clear the contents of your input cells? If yes, congratulations on

creating your first macro. If no, please return to Step 2, Part E to build the macro again.

Listen

Step 4: Save the Macro-Enabled File and Turn on Protection in the X Games Calculator Sheet

1 you will need to save this file as a macro

1. You will need to save this file as a macro-enabled workbook. Click the File tab and select Save As.

2. Select Excel Macro-Enabled Workbook, which is also the .xlsm file type.

3. By default, all cells in a worksheet are locked. This means that when the worksheet is protected, the users will not be able to enter or

change data in those cells. We will protect the worksheet, but first we must unlock the user input cells. Select cells E4 and E6. Right-click

and select Format Cells from the menu. In the Protection tab, uncheck the box labeled Locked.

4. Protect the worksheet. From the Home Menu Cells group, select Format, Protection, Protect sheet. DO NOT add a password.

Protect Sheet

5. Remember to save the file as an .xlsm (Macro-enabled) file.

Listen

Step 5: Create a Documentation Sheet and Apply a Footer With Watermark Ski Image

Clean up the formatting of your Excel workbook, taking into account professional appearance.

The Minimum Requirement (per the Grading Rubric)

1. Insert a new sheet into the workbook, name it Documentation, and move it to be the first sheet.

2. As always, properly document all contents of the workbook on this sheet including names and brief descriptions of each sheet.

3. Apply a business such as formatting to all of these cells.

4. Create a watermark footer containing an experts-only ski image by first searching the Internet for an appropriate free image and save it.

Use something such as the following.

Experts Only

5. Select Page Layout and then open the Page Setup window.

Page Layout

1. Select Custom Footer and then use the icon on the right side to insert the image saved above into the Center section of the Footer.

2. Once the &[Picture] is displayed in the Center section, the Format Picture icon on the far right will be available. Click it.

3. In the Format Picture pop-up window, select the Picture tab.

4. Under the Image control section, select the drop-down option by Color and select Washout, and then click OK to accept it.

5. Click Print Preview on the Page Setup window to view the results!

print preview

Print Preview

Transcript

1. IMPORTANT: Remember to save this file as a Macro-enabled workbook.

Listen

Step 6: Summarize and Write Memo

Return to the worksheet named Gonzales. The analysis on this worksheet needs to be summarized in a short memo. Here are some details and

ideas about the memo to be written.

1. The memo should be written to Jesse Gonzales. Your name should also be in the From portion of the memo.

2. Include a summary of the analysis completed.

3. Possible thoughts to include: target sales for new fiscal period, sales for new developments, sharing expertise with others in sales, and a

thank you for good work.

4. The memo needs to be written professionally and placed on the last sheet in the workbook. Name the last worksheet Memo.

Listen

Finish and Submit

Save your Excel file as a Macro-enabled workbook!! Your Excel file should contain four worksheets.

Documentation

Gonzales

X Games Calc

Memo

submit one macro enabled excel file

Submit one Macro-enabled Excel file to the Dropbox. When submitting the file, provide a comment in the Dropbox comments area explaining what

you learned from completing this lab activity. File naming convention: If your name is Jane Doe, then your file should be named very similar to

Doe_J_Week7Lab.xlsm.

Download Now