BIS 155 week 7 Excel lab

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 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.

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 

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.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 ShapeDraw the shape onto the application, using your mouse or drawing tool.Draw the ShapeAdd a text description to the button: "Clear calculator values!"

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

Associate Button With Macro

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

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.

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-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. 

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
6. 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.
7. Once the &[Picture] is displayed in the Center section, the Format Picture icon on the far right will be available. Click it.
8. In the Format Picture pop-up window, select the Picture tab.
9. Under the Image control section, select the drop-down option by Color and select Washout, and then click OK to accept it. 
10. Click Print Preview on the Page Setup window to view the results!Print Preview

Transcript

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

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. 

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 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.