Lego Inventory Analysis

The attached Lego Inventory Worksheet has a list of all Lego kits in our inventory of toys. The Annual Cost of Goods Sold is the amount in dollars sold for an entire year (365 days) for each item. You will need to compute the Daily Cost of Goods Sold for each item. The Inventory Level $ is the dollar value currently in inventory for each item.

Compute the Days on Hand for each Lego kit. This is the number of days that we anticipate our inventory level will last, before we need another delivery from the factory.

Identify the kits with over 93 Days on Hand (over 3 months supply in inventory).

Our goal is to sale these kits, so we are not holding so much excess inventory.

Identify the kits with less than 2 Days on Hand (We will run out tomorrow). Use conditional formatting and highlight in Green

We do not want to run out of items that customers are buying.

Identify the kits with greater than 93 Days on Hand (we have too many). Use conditional formatting and highlight in Red

In a one page Executive Summary, identify the items we have too much of in stock and recommend what we should do with those kits. Also identify which items we are about to run out of and what we should do in the future with those kits.

Daily COGS Days on Hand Item Piraka Battle Vehicles Modular Buildings Turaga Bulk Bricks 2 Planet Series 1 Classic Castle 2 Raiders of the Lost Ark Universal Building Set Teenage Mutant Ninja Turtles Avengers Cars 2 Hospital Barraki Series 17 Minifigures Designer Sets 2 Space Police III Playhouse Airport 2 Dino Attack Unitron Extreme Team Blacktron 1 Classic Star Wars Episode 3 X-Pod Construction Ferries The Return of the King Forestmen Playsets Castle Launch Command Annual Cost of Goods Sold $ 3,257,625 $ 1,615,125 $ 711,750 $ 109,500 $ 219,000 $ 301,125 $ 410,625 $ 438,000 $ 958,125 $ 1,231,875 $ 2,641,725 $ 1,916,250 $ 54,750 $ 3,494,850 $ 13,725 $ 68,475 $ 130,050 $ 561,225 $ 294,300 $ 3,839,325 $ 143,700 $ 104,025 $ 290,175 $ 826,725 $ 903,375 $ 1,138,800 $ 1,445,400 $ 474,525 $ 907,950 $ 1,012,875 $ 132,300 $ 621,825 $ 1,689,450 Inventory Level $ $ 5,625 S 3,375 S 1,650 $ 300 600 825 $ 1,125 $ 1,200 S 2,625 S 3,375 $ 14,475 $ 10,500 450 28,725 $ 150 $ 750 $ 1,425 S 6,150 S 3,225 $ 42,075 $ 1,575 1,425 3,975 11,325 $ 12,375 $ 15,600 S 19,800 $ 7,800 $ 14,925 $ 16,650 $ 2,175 S 11,925 $ 32,400 is in Daily COGS Days on Hand Item Piraka Battle Vehicles Modular Buildings Turaga Bulk Bricks 2 Planet Series 1 Classic Castle 2 Raiders of the Lost Ark Universal Building Set Teenage Mutant Ninja Turtles Avengers Cars 2 Hospital Barraki Series 17 Minifigures Designer Sets 2 Space Police III Playhouse Airport 2 Dino Attack Unitron Extreme Team Blacktron 1 Classic Star Wars Episode 3 X-Pod Construction Ferries The Return of the King Forestmen Playsets Castle Launch Command Annual Cost of Goods Sold $ 3,257,625 $ 1,615,125 $ 711,750 $ 109,500 $ 219,000 $ 301,125 $ 410,625 $ 438,000 $ 958,125 $ 1,231,875 $ 2,641,725 $ 1,916,250 $ 54,750 $ 3,494,850 $ 13,725 $ 68,475 $ 130,050 $ 561,225 $ 294,300 $ 3,839,325 $ 143,700 $ 104,025 $ 290,175 $ 826,725 $ 903,375 $ 1,138,800 $ 1,445,400 $ 474,525 $ 907,950 $ 1,012,875 $ 132,300 $ 621,825 $ 1,689,450 Inventory Level $ $ 5,625 S 3,375 S 1,650 $ 300 600 825 $ 1,125 $ 1,200 S 2,625 S 3,375 $ 14,475 $ 10,500 450 28,725 $ 150 $ 750 $ 1,425 S 6,150 S 3,225 $ 42,075 $ 1,575 1,425 3,975 11,325 $ 12,375 $ 15,600 S 19,800 $ 7,800 $ 14,925 $ 16,650 $ 2,175 S 11,925 $ 32,400 is in