working on a class excel sheet
Formulas
| UNIT III THE RELATIONSHIP OF MARKUP TO PROFIT | |||||
| I TYPES OF MARKUP | |||||
| A. Initial Markup Concepts | |||||
| dollars | % | ||||
| original or first retail price | |||||
| billed cost of merchandise | |||||
| initial markup | 0 | ERROR:#DIV/0! | |||
| estimated expenses | |||||
| price reductions | |||||
| profit | |||||
| initial $ markup | 0 | ||||
| planned sales | |||||
| price reductions | |||||
| original retail price | 0 | ||||
| markup | |||||
| original retail price | |||||
| initial markup % | ERROR:#DIV/0! | ||||
| B. Calculating Initial Markup | |||||
| 1. finding initial MU% when gross margin % and retail reduction % are known | |||||
| gross margin % | |||||
| retail reductions % | |||||
| sales (100%) | 100.00% | ||||
| initial markup % | 0.00% | ||||
| 2. Finding initial MU % when gross margin and retail reductions in $ are known | |||||
| gross margin $ | |||||
| retail reductions $ | |||||
| sales $ | |||||
| initial markup $ | ERROR:#DIV/0! | ||||
| 3. Finding initial MU% when cash discounts and alteration costs are known | |||||
| gross margin % | |||||
| alteration costs % | |||||
| cost discount earned % | |||||
| retail reductions% | |||||
| sales % | 100.00% | ||||
| initial markup % | 0.00% | ||||
| C. Cumulative Markup | |||||
| cumulative markup $ | |||||
| cumulative retail $ | |||||
| cumulative markup % | ERROR:#DIV/0! | ||||
| cost | retail | markup % | |||
| Opening inventory | 0 | $0.00 | |||
| Purchases STD | 0 | $0.00 | |||
| Total Merchandise Handled | 0 | $0.00 | |||
| cumulative markup | 0 | ||||
| cumulative markup% | ERROR:#DIV/0! | ||||
| D. Maintained Markup | |||||
| GROSS MARGIN CALCUATION | cost | retail | MAINTAINED MARGIN CALCUATION | ||
| NET SALES | $0.00 | NET SALES | |||
| cost of goods sold | cost of goods sold | ||||
| new purchases | new purchases | ||||
| inward freight | inward freight | ||||
| Total merchandise handled | 0 | Total merchandise handled | |||
| closing inventory | closing inventory | ||||
| Gross Cost of Merchandise | 0 | Gross Cost of Merchandise | |||
| cash discounts earned | |||||
| Net Cost of Merchandise Sold | 0 | ||||
| alteration/workman costs | |||||
| Total Cost of Merchandise Sold | 0 | $0.00 | |||
| GROSS MARGIN | $0.00 | MAINTAINED MARGIN | 0 | ||
| GROSS MARGIN % | ERROR:#DIV/0! | MAINTAINED MARGIN % | ERROR:#DIV/0! | ||
| 1. Finding Maintained MU when Initial MU and Retail Reductions are known | |||||
| Initial Markup % | |||||
| Retail Reduction % | |||||
| Maintained Markup% | 0.00% | ||||
| 2. Finding Retail Reduction when Initial MU and Maintained MU are known | |||||
| Initial Markup % | |||||
| Maintained Markup % | |||||
| Retail Reduction % | 0.00% |
Problems
| Module Five Basic Markup Equations Used for Merchandising Decisions | |||||||||||||||||||||||
| Correct formula and answer | |||||||||||||||||||||||
| Initial Markup Concept | One or more formula errors | ||||||||||||||||||||||
| Wrong formula or no formula | |||||||||||||||||||||||
| 1. A men’s swim buyer determines that the department has net sales of $875,000, expenses of $345,000, and total reductions of $95,000. This buyer also wants to attain a net profit of 4.5%. Find the initial markup percentage. | |||||||||||||||||||||||
| Net sales | $875,000 | ||||||||||||||||||||||
| Expenses | $345,000 | ||||||||||||||||||||||
| Reductions | $95,000 | ||||||||||||||||||||||
| Net profit-4.5% | |||||||||||||||||||||||
| IMU % | |||||||||||||||||||||||
| Calculating Initial Markup | |||||||||||||||||||||||
| 2. A retailer in a boutique jewelry store has estimated expenses of 49%, markdowns at 15%, and stock shortage at 6.3%. A profit of 4% is desired. Calculate the initial markup percentage required. | |||||||||||||||||||||||
| Markdowns | 15.0% | ||||||||||||||||||||||
| Expenses | 49.0% | ||||||||||||||||||||||
| Shortage | 6.3% | ||||||||||||||||||||||
| Profit | 4.0% | ||||||||||||||||||||||
| IMU % | |||||||||||||||||||||||
| Cumulative Markup | |||||||||||||||||||||||
| 3. A sleepwear buyer has an opening stock figure of $170,000 at retail, which carries a 61% markup. On March 31, new purchases since the start of the period were $990,000 at retail, carrying a 63% markup. Find the cumulative markup percentage on merchandise handled in this department to date. | |||||||||||||||||||||||
| Cost | Retail | MU % | MU $ | ||||||||||||||||||||
| Opening inventory | $70,200 | $170,000 | 61.0% | ||||||||||||||||||||
| + New Purchases | $346,500 | $990,000 | 65.0% | ||||||||||||||||||||
| TMH | |||||||||||||||||||||||
| 4. A belt department had an opening inventory of $86,000 at retail, with a 56.8% markup. Purchases during November were $63,000 at cost and $142,000 at retail. Determine: |
|||||||||||||||||||||||
| a. The cumulative markup percentage | Cost | Retail | MU % | MU $ | |||||||||||||||||||
| Opening inventory | $37,152 | $86,000 | 56.8% | ||||||||||||||||||||
| New purchases | $63,000 | $142,000 | |||||||||||||||||||||
| b. The markup percentage on new purchases | |||||||||||||||||||||||
| Cost | Retail | MU $ | MU % | ||||||||||||||||||||
| $64,000 | $142,000 | ||||||||||||||||||||||
| Maintained Markup | |||||||||||||||||||||||
| 5. A sporting goods store has an initial markup of 54.5%. The expenses are 34%. Markdowns are 12%. The cost of assembling bicycles and so on (e.g., workroom costs) is 6%, and shortages are 0.8%. What was the maintained markup percentage? | |||||||||||||||||||||||
| Initial markup | 54.5% | ||||||||||||||||||||||
| Expenses | 34.0% | ||||||||||||||||||||||
| Markdowns | 12.0% | ||||||||||||||||||||||
| Workroom costs | 6.0% | ||||||||||||||||||||||
| Shortages | 0.8% | ||||||||||||||||||||||
| Total Reductions | |||||||||||||||||||||||
| MMU % | |||||||||||||||||||||||
| 6. The men’s shorts department buyer determined that the department’s initial markup should be 45.5%. The buyer also wanted to attain a maintained markup of 39%. Under this plan, what retail reduction (in percentage) would be allowed? | |||||||||||||||||||||||
| IMU % | 45.5% | ||||||||||||||||||||||
| MMU % | 39.0% | ||||||||||||||||||||||
| Net Sales % | 100.0% | ||||||||||||||||||||||
| Reduction % | |||||||||||||||||||||||
| Average Cost | |||||||||||||||||||||||
| 7. A buyer plans to purchase 8,600 pairs of socks for a pre-Christmas sale. The unit retail price is planned at $7.50, and the markup goal for the purchase is 60%. The buyer purchases 4,400 pairs at the Sock Company showroom at a cost of $3.25 each. | |||||||||||||||||||||||
| a. What is the maximum total cost the buyer can pay for the balance of the total purchase? | |||||||||||||||||||||||
| Units | Retail | Total Retail | MU % | Cost Purchases Planned | |||||||||||||||||||
| 8,600 | $7.50 | 60.0% | |||||||||||||||||||||
| Units | Cost Placed | Total Cost | |||||||||||||||||||||
| 4,400 | $3.25 | ||||||||||||||||||||||
| Cost Balance | Cost Balance | ||||||||||||||||||||||
| b. What will be the average cost per pair for the socks (4,200 socks) yet to be purchased? | Unit Balance | Cost Balance | Avg. Cost | ||||||||||||||||||||
| 4,200 | $0 | ||||||||||||||||||||||
| 8. A buyer who needs $10,000 worth of merchandise at retail for a housewares department has written orders for $2,875.50 at cost. The planned departmental markup percentage is 43.5%. How much (in dollars) is left to spend at cost? | Cost Planned | Retail Planned | MU % | ||||||||||||||||||||
| $10,000.00 | 43.5% | ||||||||||||||||||||||
| Cost Placed | |||||||||||||||||||||||
| $2,875.50 | |||||||||||||||||||||||
| Cost Balance | |||||||||||||||||||||||
| Average Retail Practice Problems | |||||||||||||||||||||||
| 9. An dress buyer confirms an order reading as follows: a. 165 maxi dresses costing $39 each b. 85 tunics costing $28 each If a retail price of $85 is placed on the maxi dresses, and a markup average of 55.5% is sought, what retail price must the tunics carry? |
Units | Cost | Total Cost | MU % | Total Retail Planned | ||||||||||||||||||
| Maxi | 155 | $39.00 | |||||||||||||||||||||
| Tuni | 85 | $28.00 | |||||||||||||||||||||
| 55.5% | |||||||||||||||||||||||
| Retail Placed | Total Retail | ||||||||||||||||||||||
| Maxi | 155 | $85.00 | |||||||||||||||||||||
| Retail Balance | Retail | Various Pricing Strategies (list at least two) | |||||||||||||||||||||
| Tunic | 85 | ||||||||||||||||||||||
| Average Markup | |||||||||||||||||||||||
| 10. A suit buyer who plans sales of $95,000 at retail during April has an average markup goal of 54%. An order is placed with the B&C Sportswear Company for April delivery in the amount of $5,975 at cost and $12,500 at retail. What markup percentage must be made on the balance of the April purchases to achieve the planned markup? | Retail Planned | MU % | Cost Planned | MU $ | MU % | ||||||||||||||||||
| $95,000 | 54.0% | ||||||||||||||||||||||
| B & C order placed | $12,500 | $5,975 | |||||||||||||||||||||
