BUS 319

Exercise 42

Markdown

Read Manzo Chapter 4 prior to attempting this exercise.

This exercise assumes you know how to create the charts discussed in the chapter.

In this and all exercises, add appropriate formatting to charts and tables.

Open a new Excel workbook. Include your name and save it as your name42.xlsx. Rename Sheet1 Markdown and delete any other worksheets. Remember to save your work frequently.

This exercise continues the fashion industry theme presented in Exercise 41. In this exercise, we focus on the retail side of the fashion industry. The price of an item is the cost plus a markup. Fashion items are frequently introduced at the start of a season with a high markup. As the season nears its end, items remaining in stock are marked down from their original price with the expectation that the lower price will increase the number of units sold. The objective is to sell all of the old merchandise and make room, and working capital, available for the next season merchandise.

You are a Product Analyst and you currently have about 150,000 units in inventory, with average sales of 5000 per week. Wow! That is a 30 week supply; well into the next season! You need to do something, and do it quickly. You decide on a four-week clearance sale and you ask the Product Manager for help. How much of a markdown should you take? She gives you a table and explains it is the historical increase in sales resulting from a given markdown.

Copy this table to your Excel worksheet.

Markdown	Sales
0%	100%
5%	103%
10%	105%
15%	115%
20%	140%
25%	200%
30%	280%
35%	380%
40%	500%
45%	730%
50%	900%
55%	975%
60%	1000%
65%	1020%
70%	1030%

Remove any cell borders and format the table with colors, fonts and borders of your choice. Add an appropriate title to your table.

Insert a scatter chart (with line and markers ) three or four columns to the right of the table. This will allow room to expand the table, which we will do shortly. Format your chart with colors and fonts that match your table. Add the same title to your chart that you used for your title. Hint: Copy and paste works well here.

Copy the following questions to a location below your table and then answer them. Use Excel to calculate and 8

About how many units do you have in inventory? (Type your answer)

What is your average sales per week? (Type your answer)

What weekly sales rate would you need to sell all your inventory in 4 weeks? (Calculate)

What percent increase is that over your currently weekly rate? (Calculate)

Based on the table, what markdown should you take? (Type your answer)

The regular price of your product, before any markdown, is $80. Add a third column to your table showing the price that corresponds with each markdown percent. Hint: Multiply $80 times 1 minus the markdown percent. A markdown of 5% will yield a price of $76.00. Now add a fourth column that shows the revenue (sales percent multiplied by selling price) for each markdown percent. Hint: A 5% markdown will have revenue of $78.28. Extend your table formatting to include the new columns.

Add a 6^thquestion to the above list. Based on revenue, what markdown percent should you use and what would be the resulting sale price? Explain your decision and the impact on the quantity sold in your clearance sale.

Retailers know that more units are sold immediately after a price reduction than later on when customers perceive the markdown price as the new normal price. Your Product Manager suggests you consider another table.

Copy this table to your Markdown worksheet and paste it several rows below the questions

Day	Percent		Day	Percent		Day	Percent		Day	Percent
1	12.34143%		8	6.33452%		15	1.00702%		22	0.03911%
2	11.97119%		9	5.25765%		16	0.69484%		23	0.02151%
3	11.37263%		10	4.25870%		17	0.46554%		24	0.01140%
4	10.57655%		11	3.36437%		18	0.30260%		25	0.00581%
5	9.62466%		12	2.59056%		19	0.19064%		26	0.00285%
6	8.56595%		13	1.94292%		20	0.11629%		27	0.00134%
7	7.45237%		14	1.41833%		21	0.06861%		28	0.00060%

This table shows the historical sales percent of markdown products during the first 28 days following a markdown. Does the data appear reasonable? How did you test it? Write your answer as question 7, below the others.

Rearrange the table into two columns and add a third column. Label it Forecast. You hope to sell 144,000 units (six thousand cases of 24 each) during the 28 day closeout sale. Enter a formula in the Forecast column to convert the percents to units (percents of 144,000). Be sure to round units to the nearest integer. Add a title to the table and format the table with a different color scheme than you used on the first table.

Insert a scatter chart (with line and markers ) showing the daily sales forecast a few columns to the right of your table. Title the chart Forecast and format the chart with similar colors and fonts used for your table.

Time passes. You are now one week into your clearance sale. Add a fourth column to the table with the header Actual. Wow! Sales for the first week are: 21306, 21436, 19313, 17922, 16847, 15168, and 14339.

Create a third scatter chart, titled Actual showing the Forecast (days 1- 28) and Actual (days 1 7). Format this chart to match the previous chart. Be sure the data series are correctly labeled Forecast and Actual.

Congratulations! Save your work, close your file, and upload your file to Moodle.