How can this be done using this Excel format?

Grade Breakdown by %, 100% total, 100 points total.e 6-month merchandise plan: 50% (50 possible points) Excel spreadsheete Assortment plan: 25% (25 possible points) Excel spreadsheete Written component (answers to questions): 25% (25 possible points) Word document.e The Six-Month Merchandise Plan/Budget (50 points) is a guide that is used by the buyer in order to attempt to operate a profitable business. The plan relates the sales volume to the stock levels, provides a guideline for markdowns, and denotes the planned purchases needed for the plan to work.e IMPORTANT: WHEN YOU ENTER NUMBERS INTO YOUR EXCEL SPREADSHEET: For the Six-Month Merchandise Plan all figures in the actual plan are rounded according to financial notations. For example, the decimal point is always between the thousand- and hundred-dollar amounts; therefore $203,856.45 is written on the excel six-month merchandise plan as $203.86.e Below is Spring 6-month merchandise plan.e Year: Previous Year Actual Fi ures FEBRUARY MARCH Based on the previous analysis of the actual plan, the Spring or Easter Holidays were at the end of March and that the month of May had less sales than February, which is atypical. The Easter Holiday for the upcoming season is also in March. Further, note that you, the buyer, is attempting to increase sales in May and June, and more than likely planning to reduce markdowns in July. In other words, the timing and the amount of the markdown are the key to controlling markdowns and more than likely increasing sales volume and profit.e After consulting with management, the buyer has decided to Ian for an increase in sales of 15.00% overthe revious ar same season. After investigating the LY problem areas for the department and reviewing the environmental scan, the buyer has decided to attempt to reduce the seasonal markdowns (reductions) bv 3 % this year over LY .e Based on LY numbers and to meet TY sales projections, has been planned. for the season will need to be calculated and inserted into (H15).e Monthly sales distribution plan: Inventory Sales % sales $ purchase % Red Red ...(EOM) Ratio $14985 $33.75 21.05% 2.64% $5.94 $164.25 4.44 $164.25 $45.00 21.84% 56.12 3.08% $6.93 $168.44 APRIL $158.44 $42.75 12.93% 33.23 2.64% $5.94 $152.98 MAY $152.98 $29.25 8.89% 3.96% $8.91 $137.66 5.23 JUNE $137.66 14% $31.50 14.95% 4.40% $9.90 $134.66 4.37 JULY $134.66 $42.74 20.34% .26 5.28% $11.88 5132.30 SEASON $148.59 $225.00 256.94 22.00% $49.50 1.51 AUGUST $132.30 $42.00 3.15 February: March: April: May: June: July: 13.16960 17.23960 15.77960 17.05%0 18.95960 17.84960 average inventory is sold and then replenished during a given period oftime. Stock/sales ratio is usually calculated for a monthly period, while the turnover is calculated for a season or annually. Calculate and insert turnover rate for TY into (H11).e In the current economic climate, retailers are attempting to control the amount or volume of inventory and to increase net sales with less inventory. In other words, retailers are striving to find an ideal balance between the amount of stock available from which consumers may make their selections and the amount of sales realized from that stock during a specific period of time, usually for a month.e Therefore, the buyer must develop sound vendor partnerships in order to receive merchandise in complete shipments with on-time delivery, to purchase reorders in a timely manner, and to buy smaller quantities per SKU of merchandise without added costs of goods or the elimination of other vendor allowances and discounts. Ideally the retailer would like to sell and replace the average stock 4.0-5.0 times per year. However, few retailers attain the ideal 5.0 yearly turnover. Many times, during the first six months a 1.5 to 2.0 turnover is achieved, while a 2.0 to 3.0 may be realized during the fall or second six months. Reminder: The Six-Month Merchandise Plan is used to relate sales to inventory levels to purchases and to reductions. When the plan becomes the working document for monitoring and adjusting the monthly merchandising activities in the store or department, the buyer has a merchandising tool in order to operate a more profitable business. The plan not only drives sales and the purchases and markdowns of merch ise but also impacts other budgets in the store, such as the promotional and visual mercha Ing budgets as well as operational Monthly reductions distribution: February: March: April: May: June: July: 2.200/00 2.000/00 2.100/00 3.900/00 5.000/00 3.800/00 When analyzing sales for the Spring merchandise budget, the buyer will look at LY and for the following information:e monthly distribution of sales (i.e., percent of sales per month as compared to total sales) and the months that create peaks and valleys in the sales patterne atypical happenings, such as special promotions, community happenings, or one-of-a kind circumstances or sales of the previous yeare special circumstances such as weather conditions, changing market conditions or economic downturns, or the competition's events that impact the sales volumee timing of holidays and how they impacted the sales of the department, the sales of particular product classifications, or how important they were to the target consume beginning inventory status as to the amount of older, markdown merchandise Monthly stock-to-sales ratio distribution:e February: March: April: May: June: July: August: 5.430 4.170 5.190 4.030 4.830 4.750 ctivities. SALES to toy ED REOUtrK*S EO RJRCHasss RETIL mm"ATcosT -EWE" uG1t uB12 Hut 12 Hu0'2 21 NOTE: Both stock/sales ratio and turnover describe a relationship between sales and stock or inventory. Stock/sales ratio relates stock to sales, and turnover indicates how quickly the