Given data table above. Mr. Milligan wants to reduce his inventory by $200,000. Which items would you recommend that he remove from his inventory? Why?
1.04 1.81 2.60 2.85 2.96 4.57 Milligan's Backyard Storage Kits Inventory Analysis Worksheet 27-Oct-21 Item No. Description Unit Cost Unit Sales Average On Hand Average Unit Sales/Year Cost of average inventor Annual sales Cost of goods sold Annual gross prof Gross margin ratic Markup percentage Inventory Turnove Days in Inventory Percentage of Total Annual Sales Gross Margin Per Unit A00930 4 Wmumitt-$- 30.66 $ 45.99 275 201 $ 8,431.50 $ 9,243.99 $ 6,162.66 $ 3,081.33 33.33% 50.00% 0.73 499.38 0.18% $ 15.33 A00940 6 Windmill $ 43.99 $ 65.99 300 278 $ 13,197.00 $ 18,345.22 $ 12,229.22 $ 6,116.00 33.34% 50.01% 0.93 393.88 0.37% $ 22.00 A00820 8' x 40' Covere $ 2,509.72 $ 4,700.00 2 2 $ 5,019.44 $ 9,400.00 $ 5,019.44 $ 4,380.56 46.60% 87.27% 1.00 365.00 0.19% $ 2,190.28 A00720 8 x 10' Cedar $ $ 1,135.32 $ 1,702.99 72 75 $ 81,743.04 $ 127,724.25 $ 85,149.00 $ 42,575.25 33.33% 50.00% 350.40 2.55% $ 567.67 A00810 6LY 20'Lavara_$1,250.00 $ 1,400.99 42 75 $ 52,500.00 $ 105,074.25 $ 93,750.00 $ 11,324.25 10.78% 12.08% 1.79 204.40 2.10% $ 150.99 A00740 8' x 10' Cedar ( $ 1,148.56 $ 1,607.99 75 136 $ 86,142.00 $ 218,686.64 $ 156,204.16 $ 62,482.48 28.57% 40.00% 201.29 4.36% $ 459.43 A00610 10' Octagon Ce $ 2,499.99 $ 2,999.99 71 144 $ 177,499.29 $ 431,998.56 $ 359,998.56 $ 72,000.00 16.67% 20.00% 2.03 179.97 8.62% $ 500.00 A00120 12' x 20' Alumir $ 1,393.56 $ 1,950.99 32 65 $ 44,593.92 $ 126,814.35 $ 90,581.40 $ 36,232.95 28.57% 40.00% 2.03 179.69 2.53% $ 557.43 A00730 6' x 10' Cedar ( $ 1,135.00 $ 1,350.00 80 175 $ 90,800.00 $ 236,250.00 $ 198,625.00 $ 37,625.00 15.93% 18.94% 2.19 166.86 4.71% $ 215.00 A00710 4' x 6' Cedar St $ 333.33 $ 500.00 80 200 $ 26,666.40 $ 100,000.00 $ 66,666.00 $ 33,334.00 33.33% 50.00% 2.50 146.00 1.99% $ 166.67 A01110 Majesty Coppel $ 379.29 $ 505.72 14 35 $ 5,310.06 $ 17,700.20 $ 13,275.15 $ 4,425.05 25.00% 33.33% 2.50 146.00 0.35% $ 126.43 A01100 Monroe Woode $ 202.30 $ 252.87 10 25 $ 2,023.00 $ 6,321.75 $ 5,057.50 $ 1,264.25 20.00% 25.00% 2.50 146.00 0.13% $ 50.57 A00620 12' Cedar Octa $ 2,963.99 $ 3,430.99 15 39 $ 44,459.85 $ 133,808.61 $ 115,595.61 $ 18,213.00 13.61% 15.76% 2.60 140.38 2.67% $ 467.00 A00950 6' Wishing Wel $ 53.32 $ 79.99 300 780 $ 15,996.00 $ 62,392.20 $ 41,589.60 $ 20,802.60 33.34% 50.02% 140.38 1.24% $ 26.67 A00210 8' x 10' Alumini $ 840.47 $ 1,050.59 75 202 $ 63,035.25 $ 212,219.18 $ 169,774.94 $ 42,444.24 20.00% 25.00% 2.69 135.52 4.23% $ 210.12 A00750 6' x 6' Cedar G: $ 879.99 $ 950.78 114 325 $ 100,318.86 $ 309,003.50 $ 285,996.75 $ 23,006.75 7.45% 8.04% 128.03 6.16% $ 70.79 A00420 8 x 12' Wood B $ 751.87 $ 999.99 48 140 $ 36,089.76 $ 139,998.60 $ 105,261.80 $ 34,736.80 24.81% 33.00% 2.92 125.14 2.79% $ 248.12 A00910 Wagon Planter $ 11.42 $ 15.99 237 702 $ 2,706.54 $ 11,224.98 $ 8,016.84 $ 3,208.14 28.58% 40.02% 123.23 0.22% $ A01120 Boston Folding $ 145.92 $ 207.56 12 42 $ 1,751.04 $ 8,717.52 $ 6,128.64 $ 2,588.88 29.70% 42.24% 3.50 104.29 0.17% $ 61.64 A00530 8 Child's Picni $ 210.59 $ 350.99 44 157 $ 9,265.96 $ 55,105.43 $ 33,062.63 $ 22,042.80 40.00% 66.67% 3.57 102.29 1.10% $ 140.40 A00920 Mailbox Planter $ 21.02 $ 27.99 230 845 $ 4,834.60 $ 23,651.55 $ 17,761.90 $ 5,889.65 24.90% 33.16% 3.67 99.35 0.47% $ 6.97 A00310 12' X 8' Double $ 1,043.57 $ 1,304.47 80 302 $ 83,485.60 $ 393,949.94 $ 315,158.14 $ 78,791.80 20.00% 25.00% 3.78 96.69 7.86% $ 260.90 A00320 16' x 10' Double $ 1,141.84 $ 1,507.24 12 50 $ 13,702.08 $ 75,362.00 $ 57,092.00 $ 18,270.00 24.24% 32.00% 4.17 87.60 1.50% $ 365.40 A00140 8' x 4' Aluminur $ 424.58 $ 620.75 50 215 $ 21,229.00 $ 133,461.25 $ 91,284.70 $ 42,176.55 31.60% 46.20% 4.30 84.88 2.66% $ 196.17 A00130 6' x 4' Aluminur $ 346.36 $ 519.59 50 241 $ 17,318.00 $ 125, 221.19 $ 83,472.76 $ 41,748.43 33.34% 50.01% 4.82 75.73 2.50% $ 173.23 A01130 Miniature Foldil $ 48.00 $ 54.87 20 100 $ 960.00 $ 5,487.00 $ 4,800.00 $ 687.00 12.52% 14.31% 5.00 73.00 0.11% $ 6.87 A00960 12' Wishing We $ 130.66 $ 195.99 25 147 $ 3,266.50 $ 28,810.53 $ 19,207.02 $ 9,603.51 33.33% 50.00% 5.88 62.07 0.57% $ 65.33 A00430 10' x 16' Wood $ 808.26 $ 1,074.99 10 67 $ 8,082.60 $ 72,024.33 $ 54,153.42 $ 17,870.91 24.81% 33.00% 6.70 54.48 1.44% $ 266.73 A01140 Milligan's Supe $ 764.81 $ 899.78 1 7 $ 764.81 $ 6,298.46 $ 5,353.67 $ 944.79 15.00% 17.65% 7.00 52.14 0.13% $ 134.97 A00110 10' x 8' Alumini $ 185.17 $ 249.99 50 400 $ 9,258.50 $ 99,996.00 $ 74,068.00 $ 25,928.00 25.93% 35.01% 8.00 45.63 1.99% $ 64.82 A00100 8' x 6' Aluminur $ 148.14 $ 199.99 48 475 $ 7,110.72 $ 94,995.25 $ 70,366.50 $ 24,628.75 25.93% 35.00% 9.90 36.88 1.89% $ 51.85 A00520 3' x 7' Picnic Te $ 207.42 $ 299.99 77 850 $ 15,971.34 $ 254,991.50 $ 176,307.00 $ 78,684.50 30.86% 44.63% 11.04 33.06 5.09% $ 92.57 A00510 8' x 10' Picnic 1 $ 269.28 $ 399.99 26 299 $ 7,001.28 $ 119,597.01 $ 80,514.72 $ 39,082.29 32.68% 48.54% 11.50 31.74 2.39% $ 130.71 A00150 10' x 14' Alumir $ 650.99 $ 1,200.00 20 250 $ 13,019.80 $ 300,000.00 $ 162,747.50 $ 137,252.50 45.75% 84.33% 12.50 29.20 5.98% $ 549.01 A00410 8 x 10' Woode $ 541.33 $ 804.49 50 700 $ 27,066.50 $ 563,143.00 $ 378,931.00 $ 184,212.00 32.71% 48.61% 14.00 26.07 11.23% $ 263.16 A01020 10' x 20' Alumir $ 999.99 $ 1,789.95 27 45 $ 26,999.73 $ 80,547.75 $ 44,999.55 $ 35,548.20 44.13% 79.00% 1.67 219.00 1.61% $ 789.96 A01030 12' x 24' Alumir $ 1,199.99 $ 1,888.72 25 50 $ 29,999.75 $ 94,436.00 $ 59,999.50 $ 34,436.50 36.47% 57.39% 182.50 1.88% $ 688.73 A01040 14' x 20' Alumir $ 1,307.84 $ 2,500.99 15 27 $ 19,617.60 $ 67,526.73 $ 35,311.68 $ 32,215.05 47.71% 91.23% 1.80 202.78 1.35% $ 1,193.15 A02010 8' x 12 Arbor R $ 1,326.42 $ 1,550.42 4 10 $ 5,305.68 $ 15,504.20 $ 13,264.20 $ 2,240.00 14.45% 16.89% 2.50 146.00 0.31% $ 224.00 A02020 12' x 20' Arbor | $ 2,787.96 $ 4,100.00 10 12 $ 27,879.60 $ 49,200.00 $ 33,455.52 $ 15,744.48 32.00% 47.06% 1.20 304.17 0.98% $ 1,312.04 A00160 6' x 6' Aluminur 370.11 $ 458.93 75 150 $ 27,758.25 $ 68,839.50 $ 55,516.50 $ 13,323.00 19.35% 24.00% 2.00 182.50 1.37% $ 88.82 2.00 1.04 1.81 2.60 2.85 2.96 4.57 Milligan's Backyard Storage Kits Inventory Analysis Worksheet 27-Oct-21 Item No. Description Unit Cost Unit Sales Average On Hand Average Unit Sales/Year Cost of average inventor Annual sales Cost of goods sold Annual gross prof Gross margin ratic Markup percentage Inventory Turnove Days in Inventory Percentage of Total Annual Sales Gross Margin Per Unit A00930 4 Wmumitt-$- 30.66 $ 45.99 275 201 $ 8,431.50 $ 9,243.99 $ 6,162.66 $ 3,081.33 33.33% 50.00% 0.73 499.38 0.18% $ 15.33 A00940 6 Windmill $ 43.99 $ 65.99 300 278 $ 13,197.00 $ 18,345.22 $ 12,229.22 $ 6,116.00 33.34% 50.01% 0.93 393.88 0.37% $ 22.00 A00820 8' x 40' Covere $ 2,509.72 $ 4,700.00 2 2 $ 5,019.44 $ 9,400.00 $ 5,019.44 $ 4,380.56 46.60% 87.27% 1.00 365.00 0.19% $ 2,190.28 A00720 8 x 10' Cedar $ $ 1,135.32 $ 1,702.99 72 75 $ 81,743.04 $ 127,724.25 $ 85,149.00 $ 42,575.25 33.33% 50.00% 350.40 2.55% $ 567.67 A00810 6LY 20'Lavara_$1,250.00 $ 1,400.99 42 75 $ 52,500.00 $ 105,074.25 $ 93,750.00 $ 11,324.25 10.78% 12.08% 1.79 204.40 2.10% $ 150.99 A00740 8' x 10' Cedar ( $ 1,148.56 $ 1,607.99 75 136 $ 86,142.00 $ 218,686.64 $ 156,204.16 $ 62,482.48 28.57% 40.00% 201.29 4.36% $ 459.43 A00610 10' Octagon Ce $ 2,499.99 $ 2,999.99 71 144 $ 177,499.29 $ 431,998.56 $ 359,998.56 $ 72,000.00 16.67% 20.00% 2.03 179.97 8.62% $ 500.00 A00120 12' x 20' Alumir $ 1,393.56 $ 1,950.99 32 65 $ 44,593.92 $ 126,814.35 $ 90,581.40 $ 36,232.95 28.57% 40.00% 2.03 179.69 2.53% $ 557.43 A00730 6' x 10' Cedar ( $ 1,135.00 $ 1,350.00 80 175 $ 90,800.00 $ 236,250.00 $ 198,625.00 $ 37,625.00 15.93% 18.94% 2.19 166.86 4.71% $ 215.00 A00710 4' x 6' Cedar St $ 333.33 $ 500.00 80 200 $ 26,666.40 $ 100,000.00 $ 66,666.00 $ 33,334.00 33.33% 50.00% 2.50 146.00 1.99% $ 166.67 A01110 Majesty Coppel $ 379.29 $ 505.72 14 35 $ 5,310.06 $ 17,700.20 $ 13,275.15 $ 4,425.05 25.00% 33.33% 2.50 146.00 0.35% $ 126.43 A01100 Monroe Woode $ 202.30 $ 252.87 10 25 $ 2,023.00 $ 6,321.75 $ 5,057.50 $ 1,264.25 20.00% 25.00% 2.50 146.00 0.13% $ 50.57 A00620 12' Cedar Octa $ 2,963.99 $ 3,430.99 15 39 $ 44,459.85 $ 133,808.61 $ 115,595.61 $ 18,213.00 13.61% 15.76% 2.60 140.38 2.67% $ 467.00 A00950 6' Wishing Wel $ 53.32 $ 79.99 300 780 $ 15,996.00 $ 62,392.20 $ 41,589.60 $ 20,802.60 33.34% 50.02% 140.38 1.24% $ 26.67 A00210 8' x 10' Alumini $ 840.47 $ 1,050.59 75 202 $ 63,035.25 $ 212,219.18 $ 169,774.94 $ 42,444.24 20.00% 25.00% 2.69 135.52 4.23% $ 210.12 A00750 6' x 6' Cedar G: $ 879.99 $ 950.78 114 325 $ 100,318.86 $ 309,003.50 $ 285,996.75 $ 23,006.75 7.45% 8.04% 128.03 6.16% $ 70.79 A00420 8 x 12' Wood B $ 751.87 $ 999.99 48 140 $ 36,089.76 $ 139,998.60 $ 105,261.80 $ 34,736.80 24.81% 33.00% 2.92 125.14 2.79% $ 248.12 A00910 Wagon Planter $ 11.42 $ 15.99 237 702 $ 2,706.54 $ 11,224.98 $ 8,016.84 $ 3,208.14 28.58% 40.02% 123.23 0.22% $ A01120 Boston Folding $ 145.92 $ 207.56 12 42 $ 1,751.04 $ 8,717.52 $ 6,128.64 $ 2,588.88 29.70% 42.24% 3.50 104.29 0.17% $ 61.64 A00530 8 Child's Picni $ 210.59 $ 350.99 44 157 $ 9,265.96 $ 55,105.43 $ 33,062.63 $ 22,042.80 40.00% 66.67% 3.57 102.29 1.10% $ 140.40 A00920 Mailbox Planter $ 21.02 $ 27.99 230 845 $ 4,834.60 $ 23,651.55 $ 17,761.90 $ 5,889.65 24.90% 33.16% 3.67 99.35 0.47% $ 6.97 A00310 12' X 8' Double $ 1,043.57 $ 1,304.47 80 302 $ 83,485.60 $ 393,949.94 $ 315,158.14 $ 78,791.80 20.00% 25.00% 3.78 96.69 7.86% $ 260.90 A00320 16' x 10' Double $ 1,141.84 $ 1,507.24 12 50 $ 13,702.08 $ 75,362.00 $ 57,092.00 $ 18,270.00 24.24% 32.00% 4.17 87.60 1.50% $ 365.40 A00140 8' x 4' Aluminur $ 424.58 $ 620.75 50 215 $ 21,229.00 $ 133,461.25 $ 91,284.70 $ 42,176.55 31.60% 46.20% 4.30 84.88 2.66% $ 196.17 A00130 6' x 4' Aluminur $ 346.36 $ 519.59 50 241 $ 17,318.00 $ 125, 221.19 $ 83,472.76 $ 41,748.43 33.34% 50.01% 4.82 75.73 2.50% $ 173.23 A01130 Miniature Foldil $ 48.00 $ 54.87 20 100 $ 960.00 $ 5,487.00 $ 4,800.00 $ 687.00 12.52% 14.31% 5.00 73.00 0.11% $ 6.87 A00960 12' Wishing We $ 130.66 $ 195.99 25 147 $ 3,266.50 $ 28,810.53 $ 19,207.02 $ 9,603.51 33.33% 50.00% 5.88 62.07 0.57% $ 65.33 A00430 10' x 16' Wood $ 808.26 $ 1,074.99 10 67 $ 8,082.60 $ 72,024.33 $ 54,153.42 $ 17,870.91 24.81% 33.00% 6.70 54.48 1.44% $ 266.73 A01140 Milligan's Supe $ 764.81 $ 899.78 1 7 $ 764.81 $ 6,298.46 $ 5,353.67 $ 944.79 15.00% 17.65% 7.00 52.14 0.13% $ 134.97 A00110 10' x 8' Alumini $ 185.17 $ 249.99 50 400 $ 9,258.50 $ 99,996.00 $ 74,068.00 $ 25,928.00 25.93% 35.01% 8.00 45.63 1.99% $ 64.82 A00100 8' x 6' Aluminur $ 148.14 $ 199.99 48 475 $ 7,110.72 $ 94,995.25 $ 70,366.50 $ 24,628.75 25.93% 35.00% 9.90 36.88 1.89% $ 51.85 A00520 3' x 7' Picnic Te $ 207.42 $ 299.99 77 850 $ 15,971.34 $ 254,991.50 $ 176,307.00 $ 78,684.50 30.86% 44.63% 11.04 33.06 5.09% $ 92.57 A00510 8' x 10' Picnic 1 $ 269.28 $ 399.99 26 299 $ 7,001.28 $ 119,597.01 $ 80,514.72 $ 39,082.29 32.68% 48.54% 11.50 31.74 2.39% $ 130.71 A00150 10' x 14' Alumir $ 650.99 $ 1,200.00 20 250 $ 13,019.80 $ 300,000.00 $ 162,747.50 $ 137,252.50 45.75% 84.33% 12.50 29.20 5.98% $ 549.01 A00410 8 x 10' Woode $ 541.33 $ 804.49 50 700 $ 27,066.50 $ 563,143.00 $ 378,931.00 $ 184,212.00 32.71% 48.61% 14.00 26.07 11.23% $ 263.16 A01020 10' x 20' Alumir $ 999.99 $ 1,789.95 27 45 $ 26,999.73 $ 80,547.75 $ 44,999.55 $ 35,548.20 44.13% 79.00% 1.67 219.00 1.61% $ 789.96 A01030 12' x 24' Alumir $ 1,199.99 $ 1,888.72 25 50 $ 29,999.75 $ 94,436.00 $ 59,999.50 $ 34,436.50 36.47% 57.39% 182.50 1.88% $ 688.73 A01040 14' x 20' Alumir $ 1,307.84 $ 2,500.99 15 27 $ 19,617.60 $ 67,526.73 $ 35,311.68 $ 32,215.05 47.71% 91.23% 1.80 202.78 1.35% $ 1,193.15 A02010 8' x 12 Arbor R $ 1,326.42 $ 1,550.42 4 10 $ 5,305.68 $ 15,504.20 $ 13,264.20 $ 2,240.00 14.45% 16.89% 2.50 146.00 0.31% $ 224.00 A02020 12' x 20' Arbor | $ 2,787.96 $ 4,100.00 10 12 $ 27,879.60 $ 49,200.00 $ 33,455.52 $ 15,744.48 32.00% 47.06% 1.20 304.17 0.98% $ 1,312.04 A00160 6' x 6' Aluminur 370.11 $ 458.93 75 150 $ 27,758.25 $ 68,839.50 $ 55,516.50 $ 13,323.00 19.35% 24.00% 2.00 182.50 1.37% $ 88.82 2.00
