1. Use a Date Function to always display the current date under the spreadsheet title when you open the spreadsheet. Put your name under the spreadsheet title as well.

2. Freeze the column headings so they still appear as you scroll down to the next page.

3. Use remove duplicates to remove any duplicate items.

4. Mr. Milligan asks you to determine the cost of annual sales, inventory cost, cost of goods sold, gross profit, gross margin ratio, and markup percentage for each inventory item. Add a column for each to the right of the existing data. Formulas: Annual Sales = Unit Sales Price times Unit Sales Per Year Inventory Cost = Unit Cost times Units On Hand Cost of Goods Sold = Unit Cost times Unit Sales Per Year Gross Profit = Annual Sales minus Cost of Goods Sold Gross Margin Ratio (%) = Gross Profit Annual Sales Markup % Based on Cost = Unit Sales Price - Unit Cost/ Unit Cost

5. Insert a column after the Unit Sales Per Year column. Label it % Unit Sales and calculate each items % of total unit sales for the year. Do not hard code any values in your formula.

6.Use the currency format for all columns containing dollar values. For any column that contains a percentage, use the percentage format and format the data to two decimal places.

7.Mr. Milligan wants to know the total, average, minimum and maximum value for annual sales, inventory cost, cost of goods sold and gross profit. He also wants to know the average, minimum and maximum value for the gross margin ratio and markup percentage columns, Use functions to determine all of these values and put the results under the respective columns.

8. Create a Data Table with all the item data (do not include totals) to help you analyze the information for Mr. Milligan. Use this to answer his first 3 questions in the Memo below. Use the drop-down arrows on the columns to find the correct answers.

9. Apply a Conditional Formatting Rule in the Unit Sales Price Column to display in light red fill and dark red text any values that are under $75.

10. Sort the Data Table in Item Number sequence.

11. Mr. Milligan is interested in how each of his Categories performed. Create a Pivot Table in a new worksheet labeled Category. The Pivot Table should have a row for each category. Your Pivot Table should contain the following columns containing summary data for each Category: Category Name, Number of Items in the Category, Total Annual Sales, Total Cost of Goods Sold, and Average Markup. Use the following headings for the columns: Category, Items, Sales, CGS, and Average Markup. Make the Category worksheet the second worksheet. Use the Pivot Table to answer the last 2 questions in the Memo. Use the currency format for all columns containing dollar values. For any column that contains a percentage, use the percentage format and format the data to two decimal places.

FreemanJames_Project3 Share Comments AutoSave OFF HE Svo Home Insert Draw Page Layout A - 9 colors v HC Themes Aa Fonts Margins Orientation Formulas Data Review View AR Size PrintBreaks Background print Page Titles Setup E width: Automaticu | Gridlines Headings view view Print Print II Height: Automatic Area - vfs I K L M N 4 Milligan's Backyard Storage Units Prior Year Inventory Data Unit Sales Per Year Unit Cost 3000 Units On Hand 100 25 10 45 43 150 50 50 80 10 25 100 50 950 25 50 10 6 Item No. 7 A00630 8 A00530 9 A00620 10 A00610 11 A00310 12 A00320 13 A00150 14 A00520 15 A00720 16 A00120 17 A00810 18 A00320 19 A00430 20 A00740 21 A00140 22 A00420 23 A00960 24 A00610 25 A00215 26 A00730 27 A00210 28 A00710 29 A00115 30 A00130 31 A00930 32 A00940 33 A00110 34 A00750 35 A00510 36 A00105 37 A00955 38 A00410 39 A00950 40 A00160 41 A00920 42 A00910 43 A00520 50 Description 20 Cedar Octagon Gazebo Cape Cod Chair 12' Cedar Octagon Gazebo 10 Octagon Cedar Gazebo 12' x 8 Double Door Shed 16' x 10 Double Door Shed Adirondack Chair 3' x 7' Picnic Table 8' x 10' Cedar Shed 12' x 20' Aluminum Shed 6' x 20' Covered Bridge 16' x 10 Double Door Shed 10' x 16' Wood Barn 8' x 10' Cedar Cabana Shed 8' x 4' Aluminum Shed 8' x 12' Wood Barn 12' Wishing Well 10 Octagon Cedar Gazebo 10' x 20' Aluminum Barn 6 x 10 Cedar Garden Hut 8' x 10' Aluminum Barn 4' x 6' Cedar Shed 15' x 10 Aluminum Shed 6' x 4' Aluminum Shed 4' Windmill 6' Windmill 10' X 8' Aluminum Shed 6' x 6' Cedar Garden Hut 8 x 10 Picnic Table 8' x 6' Aluminum Shed 10' Wishing Well 8' x 10' Wood Barn 6' Wishing Well Deck Chair Mailbox Planter Wagon Planter 3' x 7 Picnic Table Category Gazebo Chair Gazebo Gazebo Shed Shed Chair Misc Shed Shed Misc Shed Barn Shed Shed Barn Misc Gazebo Barn Misc Barn Shed Shed Shed Misc Misc Shed Misc Misc Shed Misc Barn Misc Chair Misc Misc Misc --------------------------------------------------------- Unit Sales Price 3999.99 55 3430.99 2999.99 1500 1507.24 80 299.99 1702.99 1950.99 1500 1507.24 1074.99 1607.99 620.75 999.99 195.99 2999.99 1300 1350 1100 500 349.99 519.59 45.99 65.99 249.99 650 399.99 199.99 90 804.49 79.99 2500 2499.99 1043.57 1100 58.36 207.42 1135.32 1393.56 1100 1100 808.26 1148.56 424.58 751.87 130.66 2499.99 956 1000 800 333.33 256.85 346.36 30.66 43.99 185.17 456.56 269.28 148.14 65.85 541.33 53.32 55.9 19.88 11.42 207.42 100 50 50 275 100 100 140 147 150 150 175 199 200 200 241 250 278 300 325 350 400 600 700 780 800 845 900 950 300 -------- 100 50 75 27.99 15.99 299.99 300 200 250 250 100 44 Data + Ready @ - + 100%