project
Fullborehemiworking on a class excel sheet.
2 years ago
50
files (2)module4.docx
ModuleFourBasicMarkdownEquations.xlsx
module4.docx
You will use the Module Four Basic Markdown Equations Spreadsheet Excel spreadsheet to complete this assignment.
Using the Excel spreadsheet, enter the correct formula for each question. Each question may require more than one response. The yellow boxes indicate a response is required.
Submit your assignment here. Make sure you’ve included all the required elements by reviewing the guidelines and rubric.
All calculations should be rounded to the nearest tenth.
Be sure to review the video tutorials within the module Reading and Resources area for support with this assignment. Microsoft Excel is offered for free through SNHU. Please reference the free Excel for Windows Training offered by Microsoft Office if you need assistance with using Excel.
ModuleFourBasicMarkdownEquations.xlsx
Formulas
| RETAIL PRICING AND REPRICING OF MERCHANDISE (MARKDOWN) | ||||||
| A Basic Markdown Percentage Equations | ||||||
| 1. Calculating Markdown % on Retail | ||||||
| Using the Retail Method of Inventory | ||||||
| Markdown % | ||||||
| $Markdown | ||||||
| retail | ||||||
| Markdown % | ERROR:#DIV/0! | |||||
| 2. Calculating Markdown % on Cost | ||||||
| Markdown % | ||||||
| $ Markdown | ||||||
| cost | ||||||
| Markdown % | ERROR:#DIV/0! | |||||
| 3. Calculating Markdown % when cost and retail are known | ||||||
| a. when individual cost and individual retail are known | ||||||
| individual retail | ||||||
| - individual cost | ||||||
| $ Markdown | 0 | |||||
| Markdown % | ERROR:#DIV/0! | |||||
| b. when total purchase cost and total purchase retail are known | ||||||
| total retail | ||||||
| - total cost | ||||||
| $ Markdown on entire purchase | 0 | |||||
| Markdown % | ERROR:#DIV/0! | |||||
| 4. Calculating Markdown % on a group of items with varying costs or retail prices i.e. order writing | ||||||
| $ cost | $ retail | $Markdown | ||||
| Item A | ||||||
| Item B | ||||||
| Item C | ||||||
| Item D | ||||||
| Total | $0.00 | $0.00 | $0.00 | |||
| Markdown % | ERROR:#DIV/0! | |||||
| 5. Calculating Markdown % with varying retail prices that have the same cost | ||||||
| Merchandise | no. pieces | unit cost | total cost | unit retail | total retail | Markdown |
| portion A | same | ERROR:#VALUE! | $0.00 | |||
| portion B | same | ERROR:#VALUE! | $0.00 | |||
| portion C | same | ERROR:#VALUE! | $0.00 | |||
| total | 0 | same | ERROR:#VALUE! | $0.00 | ERROR:#VALUE! | |
| Markdown% on entire purchase | ERROR:#VALUE! | |||||
| 6. Calculating Markdown % on merchandise with varying costs at the same retail price | ||||||
| no.pieces | unit cost | total cost | unit retail | total retail | Markdown | |
| Merchandise A | $0.00 | same | ERROR:#VALUE! | |||
| Merchandise B | $0.00 | same | ERROR:#VALUE! | |||
| Merchandise C | $0.00 | same | ERROR:#VALUE! | |||
| total | $0.00 | same | ERROR:#VALUE! | ERROR:#VALUE! | ||
| Markdown % on enitre purchase | ERROR:#VALUE! | |||||
| B Basic $ Markdown Calculations Used in Buying Decisions | ||||||
| 1. Finding Retail when Cost and Markdown % are Known | ||||||
| retail | ||||||
| cost | ||||||
| Markdown % | ||||||
| 0 | ||||||
| 2. Finding Cost when Retail and Markdown % are known | ||||||
| cost | ||||||
| retail | ||||||
| Markdown % | ||||||
| cost | ERROR:#REF! | |||||
Problems
| REPRICING OF MERCHANDISE MARKDOWN | |||||||||||||||||||||||
| Correct formula and answer | |||||||||||||||||||||||
| 1. The men’s shirts buyer for a department store is taking a markdown on short sleeve denim shirts for Father’s Day. All $89.00 shirts will be markdown to $59.99. Calculate the markdown dollars for the shirts. | Regular Price - | Markdown Price = | MD $ | One or more formula errors | |||||||||||||||||||
| $89.00 | $59.99 | Wrong formula or no formula | |||||||||||||||||||||
| 2. The bedding buyer is running a promotion on sheet sets for a back-to-school sale. All twin-sized sheet sets will be promoted at 25% off. The sheet sets regularly retail at $75.00. What are the markdown dollars and the new retail price for the sheet sets? | Regular Price x | % off = | MD $ | New Retail Price | |||||||||||||||||||
| $75.00 | 25.0% | ||||||||||||||||||||||
| 3. Fill in the blanks for parts (a)–(d) in the following table: | Regular Price - | Markdown Price = | MD $ x | Units Sold = | Total MD $ | MD % | |||||||||||||||||
| a. | $40.00 | $19.99 | 1,275 | ||||||||||||||||||||
| b. | $149.00 | $109.99 | 525 | ||||||||||||||||||||
| c. | $299.00 | $249.98 | 375 | ||||||||||||||||||||
| d. | $74.99 | $29.95 | 722 | ||||||||||||||||||||
| Planned and Actual Markdown | |||||||||||||||||||||||
| 4. For November, a suit buyer took markdowns totaling $24,700. During this month, the net sales for the department amounted to $50,000. What was the markdown percentage for November? | |||||||||||||||||||||||
| MD $ | Net Sales $ | MD % | |||||||||||||||||||||
| $24,700 | $50,000 | ||||||||||||||||||||||
| 5. If the girls’ department's actual spring season sales were $69,000, and the actual markdown percentage was 29.5%, calculate: | |||||||||||||||||||||||
| a. The actual markdown dollars for the fall season in the girls' department | Net Sales $ | MD % | MD $ | ||||||||||||||||||||
| $69,000 | 29.5% | ||||||||||||||||||||||
| b. If the planned markdown dollars for fall in the girls’ department was $19,000, is the buyer over or under the markdown plan? | Planned | Actual | Under/Over spent | ||||||||||||||||||||
| $19,000 | $17,405 | ||||||||||||||||||||||
| Compounding of Markdown | |||||||||||||||||||||||
| 6. The entire stock of $99.00 activewear bottoms will be promoted during Thanksgiving weekend as a POS promotion at 40% off. There will be a coupon in the paper for an additional 10% off. If the customer uses the coupon, calculate: | |||||||||||||||||||||||
| a. The final selling price for the activewear. | Regular Price x | % off = | MD $ | New Retail Price x | Additional discount % | Additional discount $ | Final selling price | ||||||||||||||||
| $99.00 | 40.0% | 10% | |||||||||||||||||||||
| b. The actual markdown % to the customer. | Original Retail Price | New Retail Price | MD $ | MD % to customer | |||||||||||||||||||
| $99.00 | $0.00 | ||||||||||||||||||||||
| c.The markdown journalized by the retailer. | Original Retail Price | New Retail Price | MD $ | MD % to journal | |||||||||||||||||||
| $99.00 | $0.00 | ||||||||||||||||||||||
| Point-of-Sale (POS) Markdown | |||||||||||||||||||||||
| 7. The men’s tie buyer planned a Father’s Day promotion on ties. The POS price point promotion was $19.99. Here is what sold during the promotion: 40 ties with an original retail of $35.00; 50 ties with an original retail of $40.00. Calculate the following: | |||||||||||||||||||||||
| a. Total markdown in dollars for the promotion | Original Retail Price | New Retail Price | MD $ | Units Sold | Total MD $ | ||||||||||||||||||
| $35.00 | $19.99 | 40 | |||||||||||||||||||||
| $40.00 | $19.99 | 50 | |||||||||||||||||||||
| Total MD $ = | |||||||||||||||||||||||
| b. Total sales dollars generated for the promotion | New Retail Price | Units Sold | Total Sales | OR | New Retail Price | Units Sold | Total Sales | ||||||||||||||||
| $19.99 | 40 | $19.99 | 90 | ||||||||||||||||||||
| $19.99 | 50 | ||||||||||||||||||||||
| Total Sales $ = | |||||||||||||||||||||||
| c. Total markdown percentage for the tie promotion | MD $ | Retail Sales | MD % | ||||||||||||||||||||
| $0.00 | $0.00 | ||||||||||||||||||||||
| 8. Permanent Markdown | |||||||||||||||||||||||
| 8.The men's sweater buyer bought V-neck linen sweaters to retail at $49.99. The buyer purchased 560 sweaters. At the full price, 140 pieces sold. A permanent markdown was taken to $39.99 and 120 pieces sold. Another permanent markdown was taken to $29.99 and 150 pieces were sold. Calculate the following: | |||||||||||||||||||||||
| First set up a Sell Down Chart: | |||||||||||||||||||||||
| Units OH Beginning | 560 | ||||||||||||||||||||||
| Units Sold at Regular Price $49.99 | 140 | ||||||||||||||||||||||
| Units OH to Perm to $39.99 | |||||||||||||||||||||||
| Units Sold at $39.99 | 120 | ||||||||||||||||||||||
| Units OH to Perm to $29.99 | |||||||||||||||||||||||
| Units Sold at $29.99 | 150 | ||||||||||||||||||||||
| Units OH balance | |||||||||||||||||||||||
| a. Total markdown $ for this sweater sale | Retail Price | New Retail Price | MD $ | Units OH | Total MD $ | ||||||||||||||||||
| $49.99 | $39.99 | $10.00 | 0 | ||||||||||||||||||||
| $39.99 | $29.99 | $10.00 | 0 | ||||||||||||||||||||
| Total MD $= | |||||||||||||||||||||||
| b. Total sales generated for the sweater sale | New Retail Price | Units Sold | Total Sales | ||||||||||||||||||||
| $49.99 | 140 | ||||||||||||||||||||||
| $39.99 | 120 | ||||||||||||||||||||||
| $29.99 | 150 | ||||||||||||||||||||||
| Total Sales = | |||||||||||||||||||||||
| c. Total markdown % for the sweater sales | MD $ | Retail Sales | MD % | ||||||||||||||||||||
| $0.00 | $0.00 | ||||||||||||||||||||||
