QUESTION: Explain how to calculate the retail purchases for each month.

QUESTION: Explain how to calculate the retail purchases for each month.

QUESTION: Explain how to calculate the retail purchases for each month.

QUESTION: Explain how to calculate the retail purchases for each month.

QUESTION: Explain how to calculate the retail purchases for each month.

QUESTION: Explain how to calculate the retail purchases for each month.

Case Study Team Project Spreadsheet 01 (25 pts.) Background Information: Using last year's figures for a hardware department, plan a 12% increase for next year, maintaining the same monthly sales percentages, the same stock-to-stock ratios, and the same markdown percents as last year. A six-month plan is completed below. Last year's spring season figures were as follows: Sales Volume February $42,700 March $55,600 April $51,700 May $63,100 June $45,500 July $33,000 August BOM Stocks $97,800 $124,590 $115,410 $103,830 $102,280 $91,730 $104,400 Markdowns $4,560 $2,361 $6,438 $6,892 $4,650 $2,005 Last year's departmental markup was 48%. Analyze the information below and answer the questions underneath the plan. Departmental Name: No. Actual na SIX-MONTH MERCHANDISING PLAN Workroom Cost Cash Discount % Shortage/Overage % Average Stock Season Stock Turnover Plan n/a n/a n/a $118,439 2.76 na na $105,720 2.76 Overall Markdown% 9.23% 9.23% Season Total Mar. Sept 19% 55,600 62,272 Apr. Oct. 18% 51,700 57,904 May Nov 22% 63, 100 70,672 June Dec 16% 45,500 50,960 July Jan. 11% 33,000 36,960 100% 291,600 326,592 124,590 139,489 115,410 129, 126 103,830 116,609 102, 280 114,660 91,730 102,749 Departmental Markup % Last Year Plan Actual 48.0% 48.0% Spring 20 Feb. Fall 20 Aug. Sales Percent Each Month 15% Last Year 42,700 Sales Plan 47,824 $ Revised Actual Retail Last Year 97,800 Stock Plan 109,517 (BOM) Revised $ Actual Last Year . 4,560 Plan (dollars) 5, 108 Markdowns Last Year Monthly % 10.68% Plan Monthly % 10.68% Actual Dollars Last Year 74,050 Retail Plan 82,904 Purchases Revised Actual 52.0% Last Year 38,506.00 Tool Cost Plan 43, 110.08 Purchases Revised Actual EOM Stock 35.8% Last Year 124,590 July 31 & Plan 139,489 Jan 31 Revised EOS Stock Actual STOCK TO Last Year 2.29 Sales Plan Datia 2.29 Department Buyer Controller 26,906 30,136 2,361 2,647 4.25% 4.25% 6,438 7,209 12.45% 12.45% 6,892 7,717 10.92% 10.92% 4,650 5,208 10.22% 10.22% 2,005 2,247 6.08% 6.08% 48, 781 54,556 46,558 52,596 68, 442 76,440 39,600 44,257 47,675 53,378 325, 106 364, 131 25,366.12 28,369.12 24,210.16 27,349.92 35,589.84 39,748.80 20,592.00 23,013.64 24,791.00 27,756.56 169,055.12 189,348. 12 115,410 129, 126 103,830 116,609 102,280 114,660 91,730 102,749 104,400 116,920 2.78 2.24 2.24 2.23 2.23 1.65 1.65 Merchandise Manager 2.25 2.25 2.78