AutoSave O off - Cotton_B_final project_sixmonthbudgetplan Search Brianna Cotton File Home Insert Draw Page Layout Formulas Data Review View Help Acrobat Share Comments & Cut Calibri A" A = al Wrap Text Accounting AY O Paste CE Copy 12 AutoSum IUV LAV CE E Merge & Center Conditional Format as Cell Insert Delete Format Fill Clear Sort & Find & Ideas Sensitivity Format Painter Formatting v Table Styles v Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas Sensitivity E35 fx B C D E F G Department Name: No. Last Year Plan Actual Your name: Brianna Cotton Six-month Merchandise Budget Plan Cost Cash Discount % Shortage/Overage % Departmental Markup % Average Stock S 763,285.71 Last Year Plan Actua Season Stock Turnover 0.46 38.009 32.00% Overall Markdown % 17.00% 7.00% FW 2020 AUG SEP OCT NOV DEC JAN Season Total Sales Percent Each Month 16.00% 14.00% 5.00% 17.00% 20.00% 8.00% 100.00% Last Year 55,920.00 $ 18,930.00 5 52,425.00 $ 59,415.00 $ 69,900.00 S 62,910.00 S 349,500.00 Sales $ (Net Sales) Plan 65,426.40 $ 57 , 248.10 $ 61,337.25 |$ 59,515.55 |$ 81,783.00 $ 73,604.70 $ 408,915.00 Revised Actua Last Year 120,000.00 $ 105,000.00 $ 110,000.00 $ 125,000.00 $ 151,000.00 $ 135,000.00 18 |BOM Stock Plan Revised Actua Last Year $ 5,941.50 $ 2,970.75 $ 2,970.75 $ 8,912.25 $ 17,824.50 $ 20,795.25 $ 59,415.00 Plan (dollars) Markdowns Last Year Monthly % 10.00% 5.00% 5.00% 15.00% 30.00% 35.00% 100.00% 24 |Plan Monthly % 10.009 5.00% 5.00% 15.00% 30.00% $5.00% 100.00% Actual Dollars Last Year 46,861.50 |$ 56,900.75 $ 70,395.75 $ 94,327.25 $ 71,724.50 $ 69,705.25 | $ 409,915.00 Retail Purchases plan Revised Actua 30 Last Year 29,054.13 $ 35,278.47 $ 43, 645.37 $ 58,482.90 $ 44,469.19 $ 43,217.26 $ 254,147.30 31 Cost Purchases Plan 32 Revised 33 Actua 34 Last Year $ 105,000.00 $ 110,000.00 $ 125,000.00 $ 151,000.00 $ 135,000.00 $ 121,000.00 35 Plan 89,000.00 36 EOM Stock Revised Reminder: 37 Actua 1) DO NOT just type the numbers - You MUST have formulas for a 38 |Monthly Stock-to-Sales Ratio Last Year 2.15 2.15 2.10 2.10 2.16 2.15 2) Round up all answers to 2 decimal places. 20 |Plan 2.15 2.15 2.10 2.10 2.16 2.15 40 Department Buyer: Merchandise Manager Six-Month Merchandise Budget PI + Ready - - + 80% w O 9:15 PM Type here to search O X 5/1/2020FDM Fashion F X (2) FDM30262 X FDM30262_six X FDM30262_SP X Formula Sheet X M Inbox (20) - bc x (2) FDM30262 X Bb WEEK 10: Six-r X Course Hero X + X learn.kent.edu/bbcswebdav/pid-11408603-dt-content-rid-151559867_1/courses/13694-13695.202010/FDM30262_SP20_final%20project_instruction%2... Apps Panties Apple Emoji List... GIPHY | Search All t... CV Online video downl... a Cape Robbin Aves Convert PDF to Po... AVERY Avery Labels - Crea... B Block Posters Free Greeting Cards... FDM30262_SP20_final project_instruction(1).pdf 1/2 FDM 30262 Fashion Merchandise Planning and Buying Final Project - Six-month Merchandise Budget Plan Spring 2020 Prepare a six-month merchandise plan using the Excel Spreadsheet based on the information presented below. Fill-in all the highlighted cells in blue . Due to the coronavirus and its huge economic impact, your department is expecting a 32 percent DECREASE in sales for the upcoming FW2020 season. Sales for the season last year (FW2019) were $349,500. . FW2019 monthly sales & BOM stock data are provided below: Month LY Monthly Sales LY BOM Stock August 55,920 120,000 September 48,930 105,000 October 52,42 110,000 November 59,415 125,000 December 69,900 151,000 January 52,910 135,000 Both this year (planned) and last year's overall markdowns for the season are 17 percent of the net sales. Last year's departmental Mark-up percentage was 38% and the planned mark-up percentage for this year is 32%. . Last year's end-of-season stock was $121,000 and end-of-season stock for this year's season is planned at $89,000. Last year's monthly markdowns ($) and markdown percentages are presented below: Month LY Monthly Markdown $ LY Monthly Markdown % August 5,941.50 10% September 2,970.75 5% October 2,970.75 5% November 8,912.25 15% December 17,824.50 + 30% January 20,795.25 35% Planned monthly STS ratio & markdown % for this season is expected to be the same as last year. Type here to search O W X 9:15 PM 5/1/2020