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		BOM Stocks		Markdowns
	February	$42,700		$97,800		$4,560
	March	$55,600		$124,590		$2,361
	April	$51,700		$115,410		$6,438
	May	$63,100		$103,830		$6,892
	June	$45,500		$102,280		$4,650
	July	$33,000		$91,730		$2,005
	August	-		$104,400		-
	Last year's departmental markup was 48%. Analyze the
	information below and answer the questions underneath
	the plan.
	Departmental Name:		No.
SIX-MONTH MERCHANDISING PLAN		Plan (This Year)	Actual (Last Year)
Workroom Cost	n/a	n/a
Cash Discount %	n/a	n/a
Shortage/Overage %	n/a	n/a
Average Stock	$118,439	$105,720
Departmental Markup %	Season Stock Turnover	2.76	2.76
Last Year	Plan	Actual	Overall Markdown%	9.23%	9.23%
48.00%	48.00%
Spring 20___	Feb.	Mar.	Apr.	May	June	July	Season Total
Fall 20___	Aug.	Sept.	Oct.	Nov.	Dec.	Jan.
Sales Percent Each Month	15%	19%	18%	22%	16%	11%	100%
Sales $	Last Year	42,700	55,600	51,700	63,100	45,500	33,000	291,600
Plan	47,824	62,272	57,904	70,672	50,960	36,960	326,592
Revised
Actual
Retail Stock (BOM) $	Last Year	97,800	124,590	115,410	103,830	102,280	91,730
Plan	109,517	139,489	129,126	116,609	114,660	102,749
Revised
Actual
Markdowns	Last Year	4,560	2,361	6,438	6,892	4,650	2,005	26,906
Plan (dollars)	5,108	2,647	7,209	7,717	5,208	2,247	30,136
Last Year Monthly %	10.68%	4.25%	12.45%	10.92%	10.22%	6.08%
Plan Monthly %	10.68%	4.25%	12.45%	10.92%	10.22%	6.08%
Actual Dollars
Retail Purchases	Last Year	74,050	48,781	46,558	68,442	39,600	47,675	325,106
Plan	82,904	54,556	52,596	76,440	44,257	53,378	364,131
Revised
Actual
Cost Purchases	52.00%	Last Year	38,506.00	25,366.12	24,210.16	35,589.84	20,592.00	24,791.00	169,055.12
Plan	43,110.08	28,369.12	27,349.92	39,748.80	23,013.64	27,756.56	189,348.12
Revised
Actual
EOM Stock July 31 & Jan 31 EOS Stock	35.80%	Last Year	124,590	115,410	103,830	102,280	91,730	104,400
Plan	139,489	129,126	116,609	114,660	102,749	116,920
Revised
Actual
Stock to Sales Ratio	Last Year	2.29	2.24	2.23	1.65	2.25	2.78
Plan	2.29	2.24	2.23	1.65	2.25	2.78
Department Buyer		Merchandise Manager
Controller

13. Explain how to calculate LY's Actual Average Stock.

14. Explain how to calculate LY's Actual Season Stock Turnover.

15. Explain how to calculate LY's Actual Overall Markdown %.