Imagine that Kelly Daniels, the owner of Kay Day's Boutique, has asked you to give her some merchandising advice. Kay Day's is a boutique that sells clothing and accessories for young women. Kelly feels that she has a good understanding of the accessory part of her business. However, she thinks that she needs some help with the clothing part of her business. Knowing that you are an expert on apparel merchandising, she approaches you for some friendly advice. Kelly agrees to provide you with the following information about the clothing part of her business: Workroom Freight Cash Discounts. Stock turn Shortage Average Stock Markdown % IMU% June $165,000 July $160,000 RCS 460/560 Fall 2020 Actual LY March - 19% April 21% May 18% June 15% July - 12% 1.5% 3.5% 6.0% 2.5 5.0% $400,000 20.0% 50.0% Plan TY Last Year's Sales by Month were as follows: February $140,000. March $160,000 April - $200,000 May $175,000 March 2.5 April 3.0 - May-2.5 June 2.1 July 1.8 - 1.0% 3.1% 6.0% 4.0% The Percent of Sales by Month for TY are as follows: February -15% 51.0% Page 2 of 3 Financial Assignment The Stock-to-Sales ratio for each month last year was as follows (Kelly will continue to use these ratios for this year's plan): February-2.1 Last Year's Sales by Month were as follows: February $140,000 March $160,000 April $200,000 May $175,000 June $165,000 July $160,000 The Percent of Sales by Month for TY are as follows: February - 15% RCS 460/560 Fall 2020 March - 19% April - 21% May 18% June 15% July 12%- Page 2 of 3 Financial Assignment The Stock-to-Sales ratio for each month last year was as follows (Kelly will continue t use these ratios for this year's plan): February 2.1 March 2.5 April-3.0 May-2.5 June-2.1 July - 1.8 Here are the percentage markdowns by month for LY (please note LY's season MD% is 20% of LY's season total sales): February - 12% March - 12.5% April 14.5% May - 18% June - 20% July 23% Kelly will use LY's monthly markdown percent to sales for this year's plan. Kelly would like to have a 5% increase in sales for this year as compared to last year. She believes, that with your help, she can achieve her goal. She is planning to have an EOM on July 31 of $300,000. . To help Kelly, you must create a six-month merchandise plan with the figures she has given to you. You can use the Excel template provided (which is based on the one you created for Part 1). You have all of the figures you need to create the six-month merchandise plan. You can leave the Revised and Actual cells blank. All of the other cells can be filled in using the figures given and the formulas in Excel. Complete the assignment using your Excel spreadsheet. Submit your completed Six- Month Merchandise Plan with all of the cells completed (with the exception of the Revised and Actual cells for each section). Save your work as "Last Name Financial Assignment Part 2" so that I can tell whose assignment it is and which assignment it is. Submit your assignment on Canvas. Due date: 11/30/2020 (Monday) BEFORE MIDNIGHT Six Month Merchandise Plan Fall 2017 Sales Last Year Plan Percent Revised Actual Last Year Plan Retail Stock stock/Sales BOM Ratio Cost Purchases Revised Actual Last Year Plan LY Percent by month Percent to sales Markdowns are Revised Actual Last Year Retail Plan Purchases Revised Actual Last Year Plan IMU % Revised Actual Last Year Ending Plan Stock Revised Actual Comments: Merchandise Manager: Controller: Department Name: Workroom Cost Freight Cash Discount% Seasonal Stock Turnover Shortages Average Stock Markdown % IMU % February March April $140,000 $160,000 $200,000 $157,500 $199,500 $220,500 19.0% 15.0% 21.0% $24,000 $27.000 12.0% 17.1% $25,000 $31,172 12.5% 15.6% $294,000 $400,000 $600,000 $437,500 $346,500 $288,000 $2,366,000 $330,750 $498,750 $661,500 $472,500 $330,750 $226,800 $2,521,050 2.1 2.5 3 2.5 2.1 1.8 $270,000 $385,000 $352,500 $393,422 $29,000 $31,973 14.5% 14.5% $66,500 $63,473 Department No: $135,000 $192,500 $33,250 $172,725 $192,777 $31,102 51.0% 51.0% 51.0% Plan (This Year) 1.0% 3.1% 6.0% 2.6 4.0% $403,007 19.4% 51.0% $36,000 $38,880 Season May June July Total $175,000 $165,000 $160,000 $1,000,000 $189,000 $157,500 $126,000 $1,050,000 18.0% 15.0% 12.0% 100.0% 18.0% 20.6% $60,000 $42,204 51.0% Actual (Last Year) 1.5% 3.5% Buyer: 6.0% 2.5 5.0% 20.0% $400,000 20.0% 50.0% 24.2% $40,000 $46,000 $38,182 $36,225 23.0% 28.8% $200,000 $203,431 100.0% $120,000 $146,500 $352,000 $1,340,000 $86,130 $91,732 $235,425 $1,222,681 19.4% $73,250 $176,000 $670,000 $44,949 $115,358 $599,114 51.0 % 51.0% $400,000 $600,000 $437,500 $346,500 $288,000 $434,000 $2,506,000 $498,750 $661,500 $472,500 $330,750 $226,800 $300,000 $2,490,300