Please Use Excel to calculate the following

Mr. Milligan asks you to determine for each inventory item the:

• Cost of average inventory

• Annual sales

• Cost of goods sold

• Annual gross profit

• Gross margin ratio

• Markup percentage

• The determination of these values requires you to add columns to the Inventory Analysis worksheet. Mr. Milligan asks you to use the worksheet provided. 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.

FIND THE FOLLOWING:

  
 
 

Case background 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 this 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 on 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 (The wood of trees cut and prepared for use as building material), and the necessary hardware, anyone could assemble a storage unit. His idea proved popular, and he now stocks and sells 27 different items, including storage barn, gazebo (A small roofed building affording shade and rest), and landscaping decoration 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 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. 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 and asks you to prepare an Inventory Analysis worksheet. Design specification Mr. Milligan asks you to determine for each inventory item the: Cost of average inventory