Please enter in excel file for questions needed and enter formulas please. Thank you.

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 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. Information specifications Mr. Milligan wants the average, minimum and maximum, value of each for the: - Cost of average inventory - Annual sales - Cost of goods sold - Annual gross profit - Gross margin ratio - Markup percentage values. 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. 1 The number of times inventory is sold or used in a time period such as year 2 The average number of days the company holds its inventory before selling it Mr. Milligan additionally 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. Which items have an inventory turnover greater than 12 ? 8. Which items have days in inventory values greater than 150 days? 9. Mr. Milligan wants a lookup page in which when he selects an item no a summary description will display. The following is an example for A00930 "The annual gross profit for 4 Windmill is $3,081.33. Items stay in inventory about 499.38 days. There are 275 items available." 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 tow 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 six items to the Inventory Analysis worksheet. 2. For each inventory item, Mr. Milligan want 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. Based on inventory turnover, which five items are held the longest in inventory? 5. Mr. Milligan wants to reduce his inventory by $200,000. Which items would you recommend that he remove from his inventory? Why? 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 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. Information specifications Mr. Milligan wants the average, minimum and maximum, value of each for the: - Cost of average inventory - Annual sales - Cost of goods sold - Annual gross profit - Gross margin ratio - Markup percentage values. 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. 1 The number of times inventory is sold or used in a time period such as year 2 The average number of days the company holds its inventory before selling it Mr. Milligan additionally 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. Which items have an inventory turnover greater than 12 ? 8. Which items have days in inventory values greater than 150 days? 9. Mr. Milligan wants a lookup page in which when he selects an item no a summary description will display. The following is an example for A00930 "The annual gross profit for 4 Windmill is $3,081.33. Items stay in inventory about 499.38 days. There are 275 items available." 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 tow 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 six items to the Inventory Analysis worksheet. 2. For each inventory item, Mr. Milligan want 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. Based on inventory turnover, which five items are held the longest in inventory? 5. Mr. Milligan wants to reduce his inventory by $200,000. Which items would you recommend that he remove from his inventory? Why