Create a Template for it

in /by

Create a Template for it

Grader

You are graduating from college, and moving into your first apartment. After moving into your apartment, your insurance agent has advised you to create an inventory of the contents of your home and their value. 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 organization and calculations within the workbook.

Create a Template

You want to download an inventory template. In addition, you will apply cell styles to format the template.` “““`g6`

Bvq aaaaaa555555555555555r55555uytuytreq Open Excel and search the template gallery for Inventory of home contents.

  • Create a new document based on the template and save it as a Macro-Enabled Template named e12c1Inventory_LastFirst.
  • Apply the Droplet theme.
  • Apply the Title cell style to the cell B2.
  • Apply the Yellow Orange theme colors.
  • Unlock the range B15:K17 and range D4:D12.
  • Save the template.

Create the Sort Macro

You will sort the information in the template chronologically based on date purchased. To automate this task, you use the Macro Recorder and Developer Tools.

  • Record a macro named Sort.
  • Ensure the macro sorts the Inventory list in ascending order based on the dates listed in column F.
  • Create a form control button that spans the range F5:G6.
  • Assign the Sort macro and edit the button text to Sort.

Create a Custom Function

Along with calculating the total estimated value of your items, you also would like to calculate the depreciated value based on 5% depreciations. To complete this task, you will create a custom function using VBA and then use the function in the template.

  • Use the VBA Editor to create a new module.
  • Type the following VBA code to create a custom depreciation function:
    • Function Depreciation (Value, Rate)
    • Depreciation = Value * Rate
  • Save and exit the VBA Editor.
  • Insert two rows above row 12.
  • Enter the Row heading DEPRECIATION RATE in cell B12 and DEPRECIATION in cell B13.
  • 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).

Finalize the Template

To finalize your inventory template, you will clear sample data and lock the workbook.

  • Clear the contents in the range B17:K19.
  • Protect the workbook using the password eXploring.
  • Save the file as e12c1Inventory_LastFirst in the Excel Macro-Enabled Template file format. Close the template. Based on your instructor’s directions, submit e12c1Inventory_LastFirst.

"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.


Discount Code: CIPD30



Click ORDER NOW..

order custom paper