Milligan's Backyard Storage Kits, a mail order company, sells a variety of backyard storage unit kits and landscaping decorations to its customers. Although the company makes a profit, David Milligan, the company's owner, realizes that he can improve his company's operations if he better manages his inventory. Mr. Milligan requests your help in preparing an Inventory Analysis worksheet. The Inventory Analysis worksheet provides Mr. Milligan with information about his annual sales, cost of goods sold, gross profit, and markup on his products. Preparing the worksheet for Mr. Milligan requires you to insert columns, use several functions, and apply proper formatting to the worksheet and cells. CASE SCENARIO Ten years ago, David Milligan was short on storage space. After shopping around for a backyard storage unit and not finding one that met his specifications, Mr. Milligan built his own storage unit for his backyard. Realizing that many homeowners had similar storage needs, Mr. Milligan began selling a backyard storage unit kit via mail order. He felt that given good instructions, precut lumber, and the necessary hardware, just about anyone could assemble a storage unit. His idea proved popular, and he now stocks and sells 27 different items, including storage barn, gazebo, and landscaping decoration kits. CASE 1: Milligan's Backyard Storage Kits Mr. Milligan does not use a formal, consistent inventory tracking system. Periodically, Mr. Milligan or his staff visually checks to see which kits are in stock. Although he does try to keep a certain level of each kit in stock, the lack of a formal inventory tracking system has led to the overstocking of some items and understocking of still other items. In fact, on occasion, a customer will request a particular kit, and it is only then that Mr. Milligan realizes that the kit is out of stock. If a kit is not available, Mr. Milligan must tell the customer that he is currently out of stock, and then hope that the customer will wait for a kit to become available. Figure 1: Inventory Data Milligan's Backyard Storage Units Inventory Data Item No. Description Unit Cost Unit Sales Price Average on Hand Average Unit Sales/Year A00100 A00110 8' x 6' Aluminum Shed $148.14 $199.99 48 500 10' x 8' Aluminum Shed $185.17 $249.99 50 400 A00120 12' x 20' Aluminum Shed $1,393.56 $1,950.99 50 75 A00130 6' x 4' Aluminum Shed $346.36 $519.59 50 241 A00140 8' x 4' Aluminum Shed $424.58 $620.75 50 215 A00150 10' x 14' Aluminum Shed $650.99 $1,200.00 20 250 A00210 8' x 10' Aluminum Barn $840.47 $1,050.59 75 202 A00310 12' x 8' Double Door Aluminum Shed $1,043.57 $1,304.47 80 302 A00320 16' x 10' Double Door Aluminum Shed $1,141.84 $1,507.24 10 50 A00410 8' x 10' Wood Barn $541.33 $804.49 50 700 A00420 8' x 12' Wood Barn $751.87 $999.99 48 140 A00430 10' x 16' Wood Barn $808.26 $1,074.99 10 10 75 A00510 8' x 10' Picnic Table $269.28 $399.99 25 325 A00520 3' x 7' Picnic Table $207.42 $299.99 77 850 A00530 8' Child's Picnic Table $210.59 $350.99 44 157 A00610 10' Octagon Cedar Gazebo $2,499.99 $2,999.99 75 150 A00620 12' Cedar Octagon Gazebo $2,963.99 $3,430.99 15 39 A00710 4' x 6' Cedar Shed $333.33 $500.00 80 200 A00720 8' x 10' Cedar Shed $1,135.32 $1,702.99 72 75 A00730 6' x 10' Cedar Garden Hut $1,135.00 $1,350.00 80 175 A00740 8' x 10' Cedar Cabana Shed $1,148.56 $1,607.99 15 75 136 A00750 6' x 6' Cedar Garden Hut $879.99 $950.78 114 325 A00810 6' x 20' Covered Bridge $1,250.00 $1,400.99 42 75 A00820 8' x 40' Covered Bridge $2,509.72 $4,700.00 2 2 A00910 Wagon Planter $11.42 $15.99 250 900 A00920 Mailbox Planter $21.02 $27.99 230 845 A00930 4' Windmill $30.66 $45.99 275 201 A00940 6' Windmill $43.99 $65.99 300 278 A00950 6' Wishing Well $53.32 $79.99 300 780 A00960 12' Wishing Well $130.66 $195.99 25 25 147 CASE 1: Milligan's Backyard Storage Kits Lately, Mr. Milligan has become concerned with his inventory management methods. He now wants to better manage his inventory. As a starting point, he wants to examine his costs, sales, markup percentages, gross profits, and inventory levels. He asks you to review his inventory and make suggestions for improvement. He provides you with the data contained in Figure 1 and asks you to prepare an Inventory Analysis worksheet. Design Specifications Mr. Milligan asks you to determine the cost of average inventory, annual sales, cost of goods sold, annual gross profit, gross margin ratio, and markup percentage for each inventory item. The determination of these values requires you to add columns to the Inventory Analysis worksheet. Mr. Milligan asks you to use the formulas shown in Figure 2. As Mr. Milligan will use the Inventory Analysis worksheet during a presentation, he wants the As Mr. Milligan will use the Inventory Analysis worksheet during a presentation, he wants the worksheet to have a professional appearance. To enhance the worksheet's appearance, you include an appropriate header and format to the worksheet, column, and row labels. The header should display the name of the business, the name of the worksheet, and the current date. As you construct the worksheet, you use the currency format for all columns containing dollar values. Also, for any column that contains a percentage, you use the percentage format and format the data to two decimal places. Annual Gross Profit Annual Sales Figure 2: Inventory Analysis Worksheet Formulas Inventory Analysis Worksheet Formulas Cost of Average Inventory Annual Sales - Cost of Goods Sold Unit Sales Price* Average Unit Sales Per Year Unit Cost Average Units on Hand Cost of Goods Sold Gross Margin Ratio Markup Based on Cost Unit Cost Average Unit Sales Per Year Gross Profit Annual Sales Per Unit Unit Sales Price - Unit Cost Unit Cost Information Specifications For the cost of average inventory, annual sales, cost of goods sold, annual gross profit, gross margin ratio, and markup percentage values, Mr. Milligan wants the average, minimum, and maximum value for each. Mr. Milligan wants to know the maximum unit cost and sales price, as well as the minimum unit cost and sales price. He also wants to know the total cost of average inventory, total annual gross profit, total cost of goods sold, and the total annual sales. Mr. Milligan also needs answers to the following questions. Using your newly designed Inventory Analysis worksheet, provide Mr. Milligan with answers to his questions. 1. Mr. Milligan wants a markup of at least 30 percent on all items. Which items have markups less than 30 percent? 2. In terms of annual sales, which item has the lowest annual sales? 3. In terms of annual sales, what were Mr. Milligan's biggest selling items last year? Identify the top five. 4. What are the company's total annual sales? 5. What is the company's annual gross profit? 6. Based on average unit sales, which five items had the lowest sales? Based on average unit sales, which five items had the highest sales? 7. Mr. Milligan wants a column chart that compares the top five selling items. Based on annual sales, prepare the column chart for Mr. Milligan. Implementation Concerns Although you are free to work with the design of your worksheet, the worksheet should have a consistent, professional appearance. Also, you should use appropriate formatting for the cells and worksheet. For instance, dollar values should display a dollar sign and be formatted to two decimal places. Test Your Design After creating the Inventory Analysis worksheet, you should test your design. Perform the following steps. Keep in mind that you may need to insert additional rows and columns to provide Mr. Milligan with this information. 1. Add the following five items to the Inventory Analysis worksheet. Item No. Description Unit Cost Unit Sales Price Average on Hand Average Unit Sales/Year A01020 10' x 20' Aluminum Carport $999.99 $1,789.95 27 45 A01030 12' x 24' Aluminum Carport $1,199.99 $1,888.72 25 50 A01040 14' x 20' Aluminum Carport $1,307.84 $2,500.99 15 27 A02010 8' x 12' Arbor Roof $1,326.42 $1,550.42 4 10 A02020 12' x 20' Arbor Roof $2,787.96 $4,100.00 10 12 2. For each inventory item, Mr. Milligan wants to know what percentage of the company's total annual sales the item generated. 3. What is the gross margin per unit for each inventory item? Which inventory item(s) has (have) the largest gross margin per unit? Least? 4. Mr. Milligan wants to reduce his inventory by $125,000. Which items would you recommend that he remove from his inventory? Why?