For the purpose of grading the project you are required to perform the following tasks:
|1||Open the file e12c1Inventory.xlsx and save it as a Macro-Enabled Template named e12c1Inventory_LastFirst.||0|
|2||Apply the Droplet theme. Apply the Title cell style to the cell B2. Apply the Yellow Orange theme colors.
Hint: To apply the themes, on the Page Layout tab, use the tools in the Themes group. To apply the cell style, on the Home tab, in the Styles group, click Cell Styles.
|3||Unlock the ranges B15:K17 and D4:D12.
Hint: Select the desired cell or range of cells to unlock. Click the Home tab, in the Cells group click Format, and click Lock Cell.
|4||Record a macro named Sort. Ensure the macro sorts the Inventory list in ascending order based on the dates listed in column F.
Hint: On the Developer tab, in the Code group, click Record Macro. To sort the data, on the Data tab, in the Sort & Filter group, click Sort.
|5||Create a form control button beginning in the top-left corner of cell F4 and ending in the top-left corner of cell G7. Assign the Sort macro. Edit the button text to display the word Sort.
Hint: On the Developer tab, in the Controls group, click Insert, and then click Button (Form Control).
|6||Use the VBA Editor to create a new module. Type the following VBA code to create a custom depreciation function (be sure to include the blank lines after the first line and before the last line). Save and exit the VBA Editor.
Function Depreciation (Value, Rate)
Depreciation = Value * Rate
|7||Insert two rows above row 12. Enter the Row heading DEPRECIATION RATE in cell B12, and DEPRECIATION in cell B13.
Hint: On the Home tab, click Insert in the Cells group, and select Insert Row.
|8||Use the Depreciation function in cell D13 to calculate depreciation based on the Total Estimated Value on All Items (cell D14) and the Depreciation Rate (D12).
Hint: Click cell D13 and type =DEPRECIATION(D12,D14).
|9||Clear the contents in the range B17:K19.||5|
|10||Create a new worksheet named Code to the right of the HOME CONTENTS INVENTORY LIST.
Hint: To create a new worksheet, click New sheet to the right of the sheet tabs.
|11||Open the VBA Editor and display the module containing the sort code. Copy the code beginning with the text Sub Sort () and ending with the first instance of the text End Sub. Paste the contents in cell B2 of the Code worksheet. Do not close the VBA Editor.||15|
|12||Open the VBA Editor and display the module containing the DEPRECIATION function. Copy the code beginning with the text Function Depreciation(Value, Rate) and ending with the first instance of the text End Function. Paste the contents in cell B23 of the Code worksheet. Close the VBA Editor.||15|
|13||Save the file as e12c1Inventory_LastFirst in the Excel file format. Close the file. Based on your instructor’s directions, submit e12c1Inventory_LastFirst.||0|