ME azt BI U v A B D E F G 3 ACTIVITY 1 : MERCHANDISING PLAN PROBLEM - 40% 4 5 Using the following six-momth merchandising Plan, fill in last year's figures and then analyze the given information: 6 7 Monthly sales percent each month (Round to the nearest tenth) 8 Monthly stock-to-sales ratio 9 Monthly markdown percent 10 Monthly planned purchases at retail and cost 11 Average stock 12 Stock turnover 13 Total Markdown Percent 14 15 Last year's spring season figures were as follows: 16 17 Sales Voume BOM Stock $ Markdown $ 18 19 February $ 20,000.0 $ 62,000.00 $ 2,002.00 20 21 March $ 24,000.0 $ 67,200.00 $ 1,218.00 22 23 April $ 46,000.0 92,000.00 $ 2,610.00 24 25 May $ 36,000.0 64,800.00 $ 3,218.00 26 27 June $ 24,000.0 43,200.00 $ 3,846.00 28 29 July $ 24,000.0 85,200.00 $ ,506.00 30 31 August 74,000.00 32 33 Average Departmental markup was 54% 34 35 Using last year total sales plan a 12% Increase in sales 36 Use the same monthly sales percent as last year 37 Use the same LY stock to sales ration to plan BOM stocks 38 August BOM stock $ will be $85,000 39 Use the same total markdown % and then use the LY monthly markdown percent 40 Plan retail and cost purchases by month 41 Use the same Markup % 42 Calculate Average stock 43 Calculate turnover 44 45 COMPLETE THE SPREADSHEET BELOW USING THE ABOVE DATA. 46 NOTE THAT THE CELLS ARE NOT FORMATTED AND THE FORMULAS ARE NOT ACTIVE 47 YOU ARE TO KEY IN THE FORMULAS AND PROPERLY FORMAT THE CELLS 48 You will be only using LY and Plan rows 49 Department name and LY LAST YEAR TY PLAN ACTUAL Tuna ovew down April May June Season Total 100% 52 58 59 60 Six-Month Merchandising Plan 61 62 Using Last Year's Performance to plan This Year She Month Merchandising Plan 63 LY-LAST YEAR TY-PLAN ACTUAL 64 Markup 65 Cost % 66 67 Spring 200 February March 68 69 70 Monthly of Sales 71 72 SALES Last Yes 73 Plan 74 75 BOM Stock Last Year 76 Plan 77 78 EOM STOCK Last Year 79 50 COM Plan BO 81 Markdows Last Year 82 Monthly B3 Plan 84 85 Retail Purchases Last Year 86 Plan 87 88 Cost Purchase Last Year 89 Plan 90 91 Stock to Sales Radio La Year 92 93 94 NAME AND SAVE YOUR FILE 95 96 92 98 99 100 ME azt BI U v A B D E F G 3 ACTIVITY 1 : MERCHANDISING PLAN PROBLEM - 40% 4 5 Using the following six-momth merchandising Plan, fill in last year's figures and then analyze the given information: 6 7 Monthly sales percent each month (Round to the nearest tenth) 8 Monthly stock-to-sales ratio 9 Monthly markdown percent 10 Monthly planned purchases at retail and cost 11 Average stock 12 Stock turnover 13 Total Markdown Percent 14 15 Last year's spring season figures were as follows: 16 17 Sales Voume BOM Stock $ Markdown $ 18 19 February $ 20,000.0 $ 62,000.00 $ 2,002.00 20 21 March $ 24,000.0 $ 67,200.00 $ 1,218.00 22 23 April $ 46,000.0 92,000.00 $ 2,610.00 24 25 May $ 36,000.0 64,800.00 $ 3,218.00 26 27 June $ 24,000.0 43,200.00 $ 3,846.00 28 29 July $ 24,000.0 85,200.00 $ ,506.00 30 31 August 74,000.00 32 33 Average Departmental markup was 54% 34 35 Using last year total sales plan a 12% Increase in sales 36 Use the same monthly sales percent as last year 37 Use the same LY stock to sales ration to plan BOM stocks 38 August BOM stock $ will be $85,000 39 Use the same total markdown % and then use the LY monthly markdown percent 40 Plan retail and cost purchases by month 41 Use the same Markup % 42 Calculate Average stock 43 Calculate turnover 44 45 COMPLETE THE SPREADSHEET BELOW USING THE ABOVE DATA. 46 NOTE THAT THE CELLS ARE NOT FORMATTED AND THE FORMULAS ARE NOT ACTIVE 47 YOU ARE TO KEY IN THE FORMULAS AND PROPERLY FORMAT THE CELLS 48 You will be only using LY and Plan rows 49 Department name and LY LAST YEAR TY PLAN ACTUAL Tuna ovew down April May June Season Total 100% 52 58 59 60 Six-Month Merchandising Plan 61 62 Using Last Year's Performance to plan This Year She Month Merchandising Plan 63 LY-LAST YEAR TY-PLAN ACTUAL 64 Markup 65 Cost % 66 67 Spring 200 February March 68 69 70 Monthly of Sales 71 72 SALES Last Yes 73 Plan 74 75 BOM Stock Last Year 76 Plan 77 78 EOM STOCK Last Year 79 50 COM Plan BO 81 Markdows Last Year 82 Monthly B3 Plan 84 85 Retail Purchases Last Year 86 Plan 87 88 Cost Purchase Last Year 89 Plan 90 91 Stock to Sales Radio La Year 92 93 94 NAME AND SAVE YOUR FILE 95 96 92 98 99 100
