Week 7: Lab OverviewLab OverviewScenario/SummaryYour 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.DeliverablesAfter 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.Week 7 Grading RubricLAB 7 – Expert ToolsStepTaskPoints PossiblePoints ReceivedComments1Import and Organize Data1a - bImport Data5 1c -iProfessional Clean up & Formatting5 2Create Macro for X Games Calculator2a - lCreate & Record Macro6 3Assign Macro3a - fDraw Macro button5 3g - iAssociate Macro with button5 4Save Macro enabled file & Protect Calculator4a, bSave as .xlsm file type3 4cunlock user input cells4 4dAssociate Macro with button4 5Create a Documentation Sheet - Apply Watermark5a-cInsert and create documentation sheet4 5d-jCreate watermark4 6Summarize and Prepare Memo6a - dSummarize and write memo on last worksheet10 Comment: What you learned from completing this Lab5 TOTAL POINTS600 Lab ResourcesMicrosoft Office: Excel 2016Lab StepsPreparationYou 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.Begin: Open and SaveDownload the spreadsheet Copy of Week_7_Lab_SRMR_StudentStart-1.xlsxDownload the spreadsheet Week 7 Lab - Import.txt. (Links to an external site.) 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; andin 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).Step 1: Import and Clean Up Text FileDownload starting student Excel file and the text file. Open the starting Excel file and navigate to Sheet1.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.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.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.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.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.Add statistics to add up all sales, and then apply a 7% commission to calculate the Gonzales commission.Change the sheet name to Gonzales.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 ImportStep 2: Create the Macro in the X Games Ticket CalculatorIn 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.Select the X Game Calc sheet.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.Locate the Developer checkbox option in the Custom Ribbon tab in the Options menu, make sure it is checked, and then click OK.DeveloperYou 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 MacrosRemember, 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.Click the Record Macro button from the Developer Tab, Code group. The Macro Recorder dialog window will open.Macro Name: Provide a user-friendly name for this recording such as CalcWipe. Note: Do not use spaces in the name.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.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.Description: Provide an explanation of the functionality of the macro. Use something such as Clear input cells for X Game Calculator. See image below.Record MacroWhen you click OK, the Macro Recording begins. Select Cell E4, press the Delete key, and then select Cell E6 and press the Delete key.Then turn the Macro Recorder off by clicking the Stop Recording button from the Macro group.Step 3: View the Macro and Assign a Macro Button in the X Games Calculator SheetBehind the scenes, Excel has written a little Visual Basic for Applications (VBA) program, which you may access by clicking the Macros command. Select the Macro you just created, and then click the Edit button.Macro NameVBA will open with your code.VBA CodeSub CalcWipe()'' CalcWipe Macro' Clear input cells for X Game Calculator.'' Keyboard Shortcut: Ctrl+w' Range ("E4").Select Selection.ClearContents Range ("E6").Select Selection.ClearContentsEnd SubIn 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 MacroSelect 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 SheetYou will need to save this file as a macro-enabled workbook. Click the File tab and select Save As.Select Excel Macro-Enabled Workbook, which is also the .xlsm file type.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.Protect the worksheet. From the Home Menu Cells group, select Format, Protection, Protect sheet. DO NOT add a password. Protect SheetRemember to save the file as an .xlsm (Macro-enabled) file. Step 5: Create a Documentation Sheet and Apply a Footer With Watermark Ski ImageClean up the formatting of your Excel workbook, taking into account professional appearance.The Minimum Requirement (per the Grading Rubric)Insert a new sheet into the workbook, name it Documentation, and move it to be the first sheet.As always, properly document all contents of the workbook on this sheet including names and brief descriptions of each sheet.Apply a business such as formatting to all of these cells.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 OnlySelect Page Layout and then open the Page Setup window.Page LayoutSelect Custom Footer and then use the icon on the right side to insert the image saved above into the Center section of the Footer.Once the &[Picture] is displayed in the Center section, the Format Picture icon on the far right will be available. Click it.In the Format Picture pop-up window, select the Picture tab.Under the Image control section, select the drop-down option by Color and select Washout, and then click OK to accept it.Click Print Preview on the Page Setup window to view the results!Print Preview Summit Ridge Mountain ResortText Import and X Game Calculator MacroAuthor: Student NameDate: 8/16/2018Purpose: Utilize some of the "expert" features in Excel such as text importing, cell protection and macro generation.Contents:Gonzales: Import results of the outstanding condo sales results for Jesse GonzalesX Game Calc: The X Game Calculator sheet with a macro to automate the clearing on input cellsEXPERTS ONLYIMPORTANT: Remember to save this file as a Macro-enabled workbook.Step 6: Summarize and Write MemoReturn 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.The memo should be written to Jesse Gonzales. Your name should also be in the From portion of the memo. Include a summary of the analysis completed.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. The memo needs to be written professionally and placed on the last sheet in the workbook. Name the last worksheet Memo.

Week 5 - Final ProjectWhat Would You Do?Below are links to videos of lessons for each of the five levels of education. Please choose the topic that corresponds with the level of education you are interested in teaching, and select one video from that category.Level of EducationVideo of Lessons Pre- KHypothesizing about Bugshttps://vimeo.com/123250403 (Links to an external site.)Repeated Interactive Read-aloud in Kindergartenhttps://youtu.be/ZoaqX_-J-fs (Links to an external site.)ElementaryUnderstanding Fractionshttps://www.youtube.com/watch?v=lizNJthlpr8 (Links to an external site.)Higher-level questions: Thinking about magichttps://youtu.be/Wael8gtX4eo (Links to an external site.)Middle SchoolMeeting the Needs of Diverse Learnershttps://youtu.be/R8UGaFy-NMU (Links to an external site.)Whole Brain Teaching 5 Step Lessonhttps://youtu.be/g6NmegdUK-I (Links to an external site.)High SchoolEngaging Students in Direct Instructionhttps://youtu.be/snXOWLnOxyk (Links to an external site.)Whole Brain Teaching: High School Mathhttps://youtu.be/h6WJdsb0dfM (Links to an external site.)Check for Understandinghttps://vimeo.com/87230232 (Links to an external site.)AdultTeaching Grammar in Real-Life Contextshttps://youtu.be/TYmiR6JbF3s (Links to an external site.)Differentiation in the Adult Basic Education classroomhttps://youtu.be/jWnQNSfd2ec (Links to an external site.)View the video of the lesson you have chosen and respond to the following questions.Identify the instructional model(s) exhibited by the teacher during instruction and provide examples that validates this assumption. Refer to the “Evidence-Based Models of Teaching (Links to an external site.)” document you used in Week Five Discussion One to refresh your memory of the four types of instructional models we learned and cite it as a source to support your response. (APA citation is shown in the required resources section for this week). From your vantage point, determine the teacher’s ability to engage students throughout the lesson. Were the students engaged, attentive, and having fun learning or were there areas that the teacher could have improved upon to make the lesson more engaging?Describe up to three things you liked about the lesson.Recommend one thing you would have done differently than the teacher in the video and why. If you wouldn’t change anything, justify why you think the lesson should remain as it is.Compile the responses to the questions above in a way that it will be easy for you to transfer them to a PowerPoint presentation (for example….bullet points would work best).Create a PowerPoint with a slide for each of the questions above (see below for instructions on how to create each slide).Use the Lesson Analysis PowerPoint Template (Links to an external site.) provided to create a visual of your lesson analysis.You will use the 7x7 rule to create your presentation. The 7x7 rule states that you use no more than seven bullet points per slide and no more than seven words per bullet point. This way your visual presentation will only show the main points on each slide without overwhelming your viewers without too many words. You still need to make your slides attractive by adding images and colors to make it attractive.Add your voice to fill in the gaps between the main points on your slides. Limit your narration to five minutes or less. Use your narration to explain each of your answers. More importantly, use it as an opportunity to share your passion about what you liked in the lesson and how you might modify the lesson to better engage students and make the learning experience fun. (View this link for instructions on how insert voice narration into a PowerPoint… https://www.youtube.com/watch?v=MpBgn2whkrI (Links to an external site.))If you need help with creating an effective PowerPoint presentation, please review the How to Make a PowerPoint Presentation (Links to an external site.) document from the Ashford Writing Center.Must use at least 1 scholarly source to complete this assignment. The “Evidence-Based Models of Teaching (Links to an external site.)” document you accessed in Week Five Discussion One will meet this requirement.Must document all sources in APA style as outlined in the Ashford Writing Center (Links to an external site.).Must include a separate reference slide that is formatted according to APA style as outlined in the Ashford Writing Center. Use the APA References List page (Links to an external site.) to help you with this task. Here is a link to the APA Reference List template (Links to an external site.) located on that page.For help setting up your paper in APA style, please use the APA paper template (Links to an external site.) provided by the Ashford Writing Center.To assist you in completing the library research required for this assignment, view this Quick ‘n’ Dirty tutorial (Links to an external site.), which introduces the Ashford Library, the research process, and provides some library search tips. (Ashford University Library. (n.d). Quick ‘n’ Dirty [Video file]. Retrieved from http://library.ashford.edu/ (Links to an external site.))*NOTE: We realize this is something that you may not have ever done before. Relax… There is no right or wrong as long as you tap into the teacher within you. The point of this exercise is for you to show yourself that you already have ingenuity and insight to create a powerful and engaging lesson….. So show us the great ideas that are within you!*

In Milestone Four, you will submit a financial analysis and funding plan, which includes your analysis of the projected costs, revenue streams, and net present value for the concept from launch until two years after the breakeven point. Be sure to include a budget, an assessment of assets and liabilities, your anticipated sources of funding, and the associated costs of attaining that capital as part of your analysis. Support your analysis with relevant primary and secondary data in an appendix, specifying any relevant assumptions and limitations. You should include, among other support, sales forecasts, cash flow statements, income projections, and any other relevant calculations or financial reports. Critical Elements:  Analyze the projected costs, revenue streams, and net present value for the concept from launch until two years after the breakeven point.  Include the following as part of your analysis: o Budget o Assessment of assets and liabilities o Anticipated sources of funding o Associated costs of attaining that capital  Include relevant proforma financial reports: o Sales forecasts o Cash flow statements o Income projections o All other relevant reports specific to your concept or idea Guidelines for Submission: Your draft must contain all of the elements listed above. It should be 5 to 8 pages in length (excluding the title page, references, and appendices) using 12-point Times New Roman font, with one-inch margins. You may include summary pictures, charts, graphs, or other explanatory diagrams as needed to successfully explain the concept and implementation, but should use appendices for detailed supporting documentation. Your paper should follow APA guidelines. You must include at least 5 scholarly sources. Cite your sources within the text of your paper and on the reference page. Please see attached for the milestone four rubric and for reference pages. Please make sure to cite 5 scholarly sources and make sure each critical element is a bolded header within the paper. Let me know if you need anything else. Thanks!