Is Case Study
BA2800 Assignment 2
Case 1: Milligan’s Backyard Storage Kits
Due: July 18, 2011
Complete the case with these additional instructions:
1. Add the following columns: 2. Part 1 a. Annual Sales b. Annual COGS c. Annual Gross Profit d. Average Inventory Costs e. Gross Margin Ratio f. Markup g. Inventory Turnover h. Days in Inventory 3. Part 2 a. Gross Margin per Unit b. Percent of Sales
4. Provide the Average, High & Low for each applicable column 5. Add conditional formatting to each applicable column to highlight the high amount in green and the low amount in red. 6. Provide sum (totals) for a. Annual sales b. Annual COGS c. Annual gross profit d. Annual cost of average inventory 7. Add a report title 8. The two workbooks to be submitted via gateway. Workbook 2 is the workbook with the additional 6 items added) 9. The recommendation – answer memo/letter to be submitted via hardcopy 10. Answer Questions 1-9 (page 4) based on original data 11. Answer Questions 3-6 (page 5) based on original data plus the additional 6 items.
The assignment will be graded as follows Workbook 1 i. Formulas/Functions for cost of annual inventory, annual sales, cost of goods sold, annual gross profit, gross margin ratio, markup percentage, inventory turnover (8 points) ii. The average, minimum and maximum for the above calculations (9 points) iii. Totals formulas/functions for average inventory, annual gross profit, cost of goods sold and annual sales (4 points) iv. Headings, fit on one page, and decimal places consistent (6 points) v. Worksheets for items with less than 30% markup, lowest 5 items with annual sales in dollars, top 5 items with annual sales in dollars, lowest 5 items in quantity sales, top 5 items in
Case 1: Milligan’s Backyard Storage Kits
Due: July 18, 2011
Complete the case with these additional instructions:
1. Add the following columns: 2. Part 1 a. Annual Sales b. Annual COGS c. Annual Gross Profit d. Average Inventory Costs e. Gross Margin Ratio f. Markup g. Inventory Turnover h. Days in Inventory 3. Part 2 a. Gross Margin per Unit b. Percent of Sales
4. Provide the Average, High & Low for each applicable column 5. Add conditional formatting to each applicable column to highlight the high amount in green and the low amount in red. 6. Provide sum (totals) for a. Annual sales b. Annual COGS c. Annual gross profit d. Annual cost of average inventory 7. Add a report title 8. The two workbooks to be submitted via gateway. Workbook 2 is the workbook with the additional 6 items added) 9. The recommendation – answer memo/letter to be submitted via hardcopy 10. Answer Questions 1-9 (page 4) based on original data 11. Answer Questions 3-6 (page 5) based on original data plus the additional 6 items.
The assignment will be graded as follows Workbook 1 i. Formulas/Functions for cost of annual inventory, annual sales, cost of goods sold, annual gross profit, gross margin ratio, markup percentage, inventory turnover (8 points) ii. The average, minimum and maximum for the above calculations (9 points) iii. Totals formulas/functions for average inventory, annual gross profit, cost of goods sold and annual sales (4 points) iv. Headings, fit on one page, and decimal places consistent (6 points) v. Worksheets for items with less than 30% markup, lowest 5 items with annual sales in dollars, top 5 items with annual sales in dollars, lowest 5 items in quantity sales, top 5 items in