You are the operations manager for MLD Medical Sales. As part of your tasks, you prepare travel reports for the company’s executives. To save time, you have decided to create a template that can be used by employees that travel. To complete this task, you will create a worksheet based on an Office.com template; you will also use the Macro Recorder and Visual Basic for Application to automate sorting and calculations within the workbook. Your last steps will be to annotate the template to provide user direction and check the document for accessibility and compatibility issues.
Steps to Perform:
Step Instructions Points Possible
1 Start Excel. Download and open the file named EXP22_Excel_Ch12_Cumulative_Budget.xltx. Grader has automatically added your last name to the beginning of the filename. 0
2 Delete the sample values in the range B6:E15 and cell D2. 0
3 Clear all existing data validation in the range A1:K15. 0
4 Record a macro named Sort, be sure to use relative references. Ensure the macro sorts the data in descending order based on Amount (column F). Stop the Macro Recorder and Save the workbook as a Macro-Enabled Template. 0
5 Create a form control button that spans the cell J4:L5. Assign the Sort macro and edit the button text to Sort. 7
6 Use the VBA Editor to create a new module.
Type the following VBA code to create a custom Mileage function then save and exit the VBA Editor (be sure to leave a blank line between each line and before End Function):
Function Mileage (Miles, Rate)
‘This function will calculate mileage reimbursement
Mileage = Miles * Rate 0
