Recently, you took a position as the senior account manager at Wilhelmina’s Total Lawn Care (WTLC), a lawn care company located in Leland, Michigan. WTLC specializes in full service lawn care ranging from landscaping to weekly maintenance. The previous manager used a paper-based system to prepare expense reports, invoices, and payroll statements. However, this was a time-consuming process and required manual recalculation when any values changed. You want to start automating tasks using your extensive experience with Excel. You decide to start with the client invoice report form. Because each of the company’s billing representatives utilizes the same procedures, you will adapt an Excel template to use as a model for creating invoices. The template needs to be generic enough to accommodate a range of options, but it also needs to maintain a standard design to facilitate easy data entry. You will download an existing Excel invoice template, customize the template for your business needs, and inspect the worksheet for accessibility. Finally, you will create macros to perform a series of tasks, such as clearing the values to reset the form and finalizing the invoice for management approval. Start Excel. Download and open the file named . Grader has automatically added your last name to the beginning of the filename. The Invoice template provides the basic design you need, but you want to add your company’s contact information. You also want to delete the sample data and remove the developer’s data validation comments. Make the following edits to the template: Type in cell B2 Type in cell B3 Type in cell C5 Type in cell B27 Apply Italics format to cell B3 Delete the values in the range D6:D10 Continue editing the template by making the following structural changes. Insert a row before row 1 Insert a column before column A Set the column width of column A to 2 Add an outside border to the range B2:F29 Clear all data validation that was included in the original template Rename the worksheet Prior to distributing your template, you want to remove all personal information. You will use the Document Inspector to complete the task. Use the Document Inspector to remove all Document Properties and Personal Information. Mac users, on the Excel menu, click Preferences and then click the Security icon. In the Security dialog box, click Remove personal information from this file on save. The invoice will be sent electronically, and you want to make sure the Invoice template does not contain content that might cause difficulties for users. To ensure this document is compliant with accessibility standards, you will check accessibility. Use the Accessibility Checker to check the template for issues. Once completed, make the following changes to improve the template’s accessibility. Unmerge the following ranges C27:D27, C28:D28, C4:C5, C2:F2, D12:E12, D13:E13, D6:E6. Set the text in cell C2 to left alignment. Type in cell D13. You know that some of remote staff members are using Excel 2010. You want to make sure the workbook does not have critical features that might not display when opened with the older version of the software. Use the Compatibility Checker to check the template for compatibility with Excel 2010-2016 versions. Mac users, on the Tools menu (or the Review tab), click Check Accessibility. You want to add notes to the template to provide instruction to your staff. To do this you will annotate the worksheet using comments. Clear the contents of cell C13. Then add the following comments to the template. Cell C13 – Cell E3 – Cell E6 – Cell E13 – After inserting comments, you decide you will automate the insertion of the date in a later step. Therefore, you will remove the comment from cell E3. You also want to edit the comment in cell E13 to make a small grammatical change. Delete the comment in cell E3. Edit the comment in cell E13 to reflect the following changes For your last step, you want to hide the comments to reduce screen cl
utter. Once the comments are hidden, the comment indicators will still be visible. de all comments in the template. You do not want to assume the level of Excel expertise throughout your company; therefore, you want to craft a macro that will automate as much as possible. The macro you will create automatically clears existing values in the workbook. Although the template is empty to start, users might open the template, save a workbook, and then want to use that workbook to prepare more invoices. Therefore, you want the macro to clear cells even though the original template has no values. Once completed, save the workbook as a macro enabled template. Record a macro named Add the description: when creating the macro. When run, the macro should do the following: Delete the values in the cells E6, C13, E13, and the ranges C7:C11 and C15:E25. After deleting the values, the macro should enter the following default values. C7 = C8 = C9 = C10 = C11 = You want to make sure the ClearInvoice macro does what you want it to do. You will add some sample data and run the macro. Enter the following sample values, then run the ClearInvoice macro. C8 = C9 = C10 = C11 = Your sales reps may not be Excel experts and not know how to run a macro. To make it easier to clear values from the form, you want to assign the ClearInvoice macro to a button. The users can click the button to clear the form to use it for another customer. You will also add a button to be utilized later in the project. Insert a Button (Form Control) in the range H2:J2. Edit the button label to display the text and assign the ClearInvoice macro. Next insert a Button (Form Control) spanning the range H4:J5. Edit the button text to display the text . Type in cell C7 and then click the Clear Form button to run the macro. In order to use VBA to automatically insert and format the date, you will first create a new module. You will then use the range object to insert the current date and adjust the font property to bold the current date. Create a new module in the VBA Editor named . Enter the following code to create the desired sub procedure. After entering the code exit the VBA Editor and run the macro to test your work. After running the sub procedure, you decide that the inserted date should be italic instead of bold. You will make this change in the VBA Editor by changing the Italic property. You will also add a statement to the ClearInvoice Macro to unprotect the worksheet when clearing its data. Open the DateStamp module in the VBA Editor. Locate the code and replace the word with . Next open Module 1. Press enter twice at the end of the first line (Ln1, Col9) and type the following code. After entering the code save and exit the VBA Editor. Next assign the DateStamp macro to form control button 2. You decide to create a custom function to enable users to manually calculate the area of a yard being serviced. Open the VBA Editor and create a new module named then enter the following code: Save and exit the VBA Editor. Type in cell E6 to test your work. Insert a new worksheet named . Open the VBA Editor, open the ClearInvoice module, and copy the code. Paste the code in the Code worksheet starting in cell A1 then remove any rows containing blank cells. When complete, the code block should appear continuous with no blank cells between lines. Open the DateStamp module, and copy the code. Paste the code in the Code worksheet starting in cell A31 then remove any rows containing blank cells. When complete, the code block should appear continuous with no blank cells between lines. Open the Function module, and copy the code. Paste the code in the Code worksheet starting in cell A44 then remove any rows containing blank cells. When complete, the code block should appear continuous with no blank cells between lines. Close . Be sure to save the copy as a file, not a macro-enabled workbook. Exit Excel. Submit the file as directed.