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 | |||||||||||||||||||||