INVENTORY LIST SHEET 1

Assignment: Create an inventory workbook with all of the columns and calculations listed below. You can choose to make this inventory list of anything you want, but you need to fill in all the columns and do all the calculations listed. You are welcome to add more columns.

To hand it in: The spreadsheet must be first renamed to a file called your name.xls so that I can see from the filename that it is yours, and then upload it to moodle.

How to Start:

• If you need some things you could choose to inventory, click here
• If you prefer to dive right in: Read the requirements below and create your sheet.
• If you prefer a movie or written instructions guiding you along: use the step by step instructions. The first row has the written instructions and the rest contain movies.

Inventory Simple List Requirements:

· Heading: "INVENTORY LIST FOR " "<your name>" and the date

· Footer: page x of x

· Sheet tab label: simple list

· Column Headings: Item#, Item, Category, Quantity, Unit Value, Total Value, % of Total Value of All Items, More than 1, Sentence about item. (You can also add any other columns you want to add, such as a comment at the end.)

· Bold and italicize the words in the headings. Also, draw a thick border around each heading and make the heading cells gray.

· List at least 20 items, but use formulas to create the following columns: Item#, total value, , % of Total Value of All Items, More than 1, Sentence about item.

· Values: While you must assign a value to every item, you don’t need to be accurate – just guess

· Categories: You will need to organize your list into categories, and you will need at least 2 categories and at least 2 items in a category. For example, items in fridge could be categorized by shelf, with categories shelf1, shelf2, etc.

· Remember that the quantity can only contain numbers

· Include at least two categories, and have at least two items in the same category.

· Center the quantity column.

· Format the values as currency.

· Sort by Category and then Item

· Calculate the total value as quantity times unit value

· Total the quantity and total value columns and put a double bar above the totals.

· Below the total, calculate the average quantity, unit value and total value

· Below the average, calculate the minimum quantity, unit value and total value

· Under "More than 1", use an if statement to show "yes" if the quantity is more than one and "no" otherwise. (At least one item should have a quantity of 1 and at least one should have more than 1.)

· Use series fill to assign the item numbers.

· Calculate the % of the total value of all items by dividing the total value for the row by the total value for all items. Use one formula for all the rows by properly setting the $ in the formula.

· Under "Sentence about item", create a concatenation of "I have " + quantity + " of " + long description. (Or you could put “I want” if that makes more sense for your list)

· Autofit the rows so that the height accommodates the contents.

· Set the Margin to 7 inches from the bottom, so that only about 17 lines will print on a page, and you will get two pages. The column headings should print on the second page as well.

· Freeze the column headings so they stay put when you scroll.

· Make your columns wide enough so that your row wouldn't fit on one page. (This means you will see a dotted vertical line through a row of words.)

· Set the page to print only one page wide, so you don't have to tape two pages together. (If the print is too small to read, wrap some columns so that the print isn't squeezed too small to read.)

Your first sheet should look like this at the top (with your own items):

And like this at the bottom:

optional: If you want to add a macro for creating headings, see here