Exp22_Excel_Ch12_HOE – Invoice 1.0
Exp22 Excel Ch12 HOE Invoice 1.0
Excel Chapter 12 Hands-On Exercise – Invoice
Project Description:
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 Exp22_Excel_Ch12_HOE_Invoice.xlsx. 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 Wilhelmina’s Total Lawn Care in cell B2 Type Every Lawn Needs TLC in cell B3 Type Lot Area in cell C5 Type 1234 Caesar Parkway, Leland, MI 49654 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 Customer Invoice
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 Due on: 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 – Enter Business or Residential Cell E3 – Insert Date Cell E6 – Use the custom area function to calculate lot area Cell E13 – Business jobs are due in 30 days, all others due upon receipt
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 Business jobs are due in 30 days. All others due upon receipt
For your last step, you want to hide the comments to reduce screen clutter. Once the comments are hidden, the comment indicators will still be visible.
Hide 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 ClearInvoice. Add the description: This macro clears existing values in the current invoice. 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 = Name C8 = Company Name C9 = Street Address C10 = City, ST Zip Code C11 = Phone
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 = John Doe Inc. C9 = 123 Sample Street C10 = Leland, MI 49654 C11 = (231) 555-5555
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 Clear Form and assign the ClearInvoice macro. Next insert a Button (Form Control) spanning the range H4:J5. Edit the button text to display the text Insert Date.
Type Customer Name 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 DateStamp. Enter the following code to create the desired sub procedure.
Sub DateStamp ()
‘Unprotect worksheet for editingWorksheets(“Customer Invoice”).Unprotect Password:=”Expl0r!ng”
‘Insert current date in cell E3Range(“E3”)=Date
‘Adds bold format to cell E3Range(“E3”).Font.Bold=True
‘Protects worksheet using the password Expl0r!ngWorksheets(“Customer Invoice”).Protect Password:=”Expl0r!ng”
End Sub
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 Range(“E3”).Font.Bold=True and replace the word Bold with italic.
Next open Module 1. Press enter twice at the end of the first line (Ln1, Col9) and type the following code.
‘Unprotect worksheet for editing.Worksheets(“Customer Invoice”).Unprotect password:=”Expl0r!ng”
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 CustomFunction then enter the following code:
FunctionArea(Length,Width)
‘This function will calculate the area of the lawn being servicedArea = Length * Width
End Function
Save and exit the VBA Editor. Type =area(20,45) in cell E6 to test your work.
Insert a new worksheet named Code.
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 EXP22_Excel_Ch12_HOE_Invoice.xlsx. Be sure to save the copy as a .xlsx file, not a macro-enabled workbook. Exit Excel. Submit the .xlsx file as directed.
QUALITY: 100% ORIGINAL – NO PLAGIARISM
(USA, AUS, UK & CA PhD. Writers)
CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS
About Our Service
We are an online academic writing company that connects talented freelance writers with students in need of their services. Unlike other writing companies, our team is made up of native English speakers from countries such as the USA, UK, Canada, Australia, Ireland, and New Zealand.
Qualified Writers
- At ClassicWritersBay.com, most of our writers are degree-holding native speakers of English who are familiar with various writing styles. Our writers are proficient in many fields, including Economics, Business, Accounting, Finance, Medicine, Chemistry, Literature, Mathematics, Statistics, and many others.
- Making our customers happy is an important part of our service. So do not be surprised if you get your paper well before the deadline!
- We pay a lot of attention to ensuring that you get excellent customer service. You can contact our Customer Support Representatives 24/7. When you order from us, you can even track the progress of your paper as it is being written!
- We are attentive to the needs of our customers. Therefore, we follow all your instructions carefully so that you can get the best paper possible.
- It matters to us who writes for you, and we are serious about selecting the best candidates.
- Our writers are always learning something new, so they are familiar with the latest developments in the scientific world and can write papers with updated information and the latest findings.
Our Guarantees:
- Quality original papers that follow your instructions carefully.
- On time delivery – you get the paper before the specified deadline.
- Attentive Customer Support Representatives available 24/7.
- Complete confidentiality – we do not share you details or papers with anybody else.
