qrb 501 week 3 learning team case studies

Case 8-3 TemplateINSTRUCTIONS: Read the case in the textbook. As a team, answer the questions in this spreadsheet, then save and submit the assignment as one Microsoft® Excel®Excel attachment. Also, submit a 1-paragraph Microsoft® Word document explaining any issues or successes you had in answering these questions.

Refer to and use the following abbreviations for the problems below: • PP = Parma minimum supply purchase • S1 = Supplier 1 • S2 = Supplier 2 • CDF = Chain Discount Factor • NP = Net Price • NDE = Net Decimal Equivalent • TD = Trade Discount

1. The Artist’s Palette purchases its inventory from a number of suppliers and each supplier offers different purchasing discounts. The manager of The Artist’s Palette, Marty Parma, is currently comparing two offers for purchasing modeling clay and supplies. The first company offers a chain discount of 20/10/5, and the second company offers a chain discount of 18/12/7 as long as the total purchases are $300 or more. Assuming Parma purchases $300 worth of supplies, a) what is the net price from supplier 1? And b) From supplier 2? And c) From which supplier would you recommend Parma purchase her modeling clay and supplies?

Variables Used S1 S2

PP= $300.00 $300.00

1st CDF 0.80 0.82

2nd CDF 0.90 0.88

3rd CDF 0.95 0.93

Excel Formula NP= $205.20 $201.33

2. What is the net decimal equivalent for supplier 1? For supplier 2?

(Use this cell to answer parts a, b, and c. Be sure to show your work.)Part ASupplier 1$300(0.20) = $60$300 - $60 = $240$240(0.10) = $24$240 - $24 = $216$216(0.05) = $10.80Net Price = $216 - $10.80 = $205.20OR A more direct formula: Net Price = $300(0.8)(0.9)(0.95) = $205.20

Part BSupplier 2 $300(0.18) = $54$300 - $54 = $246$246(0.12) = $29.52$246 - $29.52 = $216.48$216.48(0.07) = $15.15$216.48 - $15.15 = $201.33 OR A more direct formula: Net Price= $300(0.82)(0.88)(0.93) = $201.33

Part CParma would save $3.87 with supplier 2. The difference in the net price of the two suppliers; $205.20 - $201.33 = $3.87

Now use the marked cells below to fill in the appropriate numbers for the variables to check your answers.

In the yellow cells, create the Microsoft® Excel® formulas that will give you the net prices for the two suppliers. Use cell references in the formula—do NOT use numbers.

(Show your work in this cell to solve for the net decimal equivalent for the two suppliers.)Supplier 1 (0.80)(0.90)(0.95) = 0.648

Supplier 2 (0.82)(0.88)(0.93) = 0.671088 (rounded)


1st CDF 0.80 0.82

2nd CDF 0.90 0.88

3rd CDF 0.95 0.93

Excel Formula NDE= 0.684 0.671088

3. What is the trade discount from supplier 1? From supplier 2?


PP= $300.00 $300.00

NDE 0.684 0.671088

Excel Formula TD= $94.80 $98.67

Use the marked cells below to fill in the appropriate numbers for the variables to check your answers.

In the yellow cells, create the Microsoft® Excel® formulas that will give you the net decimal equivalent for the two suppliers. Use cell references in the formula—do NOT use numbers.

(Show your work in this cell to solve for the trade discount for the two suppliers.)Supplier 11 - 0.684 = 0.3160.316($300) = $94.80

Supplier 21 - 0.671088 = 0.3289120.328912(300) = $98.67 (rounded)


In the yellow cells, create the Microsoft® Excel® formulas that will give you the trade discount for the two suppliers. Use cell references in the formula—do NOT use numbers.

4. The Artist’s Palette recognizes that students may purchase supplies at the beginning of the term to cover all of their art class needs. Because this could represent a fairly substantial outlay, the Artist’s Palette offers discounts to those students who pay sooner than required. Assume that if students buy more than $250 of art supplies in one visit, they may put it on a student account with terms of 2/10, n/30. If a student purchases $250 of supplies on September 16, what amount is due by September 26? How much would the student save by paying early?

(Show your work in this cell to solve for the amount due by September 26 and the amount saved by paying early.)Due by Sept. 26: $250(0.98) = $245Amount saved by paying early: $250 - $245 = $5

Variables Used Original Cost= $250.00

Discount= 2.00%

Excel Formula Due by 9/26= $245.00

$5.00


Amount saved

In the yellow cells, create the Microsoft® Excel® formulas that will give you the amount due by September 26 and the amount saved by paying early. Use cell references in the formula—do NOT use numbers.

5. Assume that if students buy more than $250 of art supplies in one visit, they may put the charge on a student account with terms of 2/10 EOM. If a student makes the purchase on September 16, on what day does the 2% discount expire? If the purchase is made on September 26, on what day does the 2% discount expire? If you were an art student, which method would you prefer: 2/10, n/30, or 2/10 EOM?

(Use this cell to respond to the questions.)If the purchase is made on September 16, the offer expires on October 10.If the purchase is made on September 26, the offer expires on November 10. Both discount periods are the same. If the student is not paying during the discount period and the purchase is made on September 26, the 2/10 EOM gives the student longer to pay.

Case 9-1 Template

1. What is the markup percentage for a box of ginger tea?

Variables Used Cost per BT= $2.59

S&H= $0.21

P= $0.70

Excel Formulas 25.00%

2. If the rice-filled heating pads sell for $7.00, $10.00, and $15.00 for small, medium, and large, respectively, what is the markup percentage on each one?

Variables Used $7.00

$10.00

INSTRUCTIONS: Read the case in the textbook. As a team, answer the questions in this spreadsheet, then save and submit the assignment as one Microsoft® Excel® attachment. Also, submit a 1-paragraph Microsoft® Word document explaining any issues or successes you had in answering these questions.

Refer to and use the following abbreviations for the problems below: • BT = Box of tea • S&H = Shipping and handling • P = Desired profit per box of tea • RFP = Rice-filled pad

(Show your work in this cell to solve for the markup percentage for a box of ginger tea.)Ginger Tea Markup: $0.70/($2.59 + $0.21) = 0.25 = 25%

Now use the marked cells below to fill in the appropriate numbers for the variables to check your answers.

Ginger Tea % Markup

In the yellow cells, create the Microsoft® Excel® formula that will give you the ginger tea markup percentage. Use cell references in the formula—do NOT use numbers.

(Show your work in this cell to solve for the markup percentages for small, medium, and large rice-filled heating pads.)Small rice-filled heating pads$7.00 - $5.00 = $2.00 markup, so $2.00/$5.00 = 40% markup for the small sizeMedium rice-filled heating pads$10.00 - $8.00 = $2.00 markup, so $2.00/$8.00 = 25% markup for the medium sizeLarge rice-filled heating pads$15.00 - $12.00 = $3.00 markup, so $3.00/$12.00 = 25% markup for the large size


Small RFP Selling Price=

Medium RFP Selling Price=

$15.00

Small RFP Cost= $5.00

Medium RFP Cost= $8.00

Large RFP Cost= $12.00

Excel Formulas Small % Markup= 40.00%

25.00%

Large % Markup= 25.00%

Amount Sold

Variables Used $7.00 2

$10.00 4

Large RFP Selling Price=

Medium % Markup=

In the yellow cells, create the Microsoft® Excel®formulas that will give you the markup percentages for small, medium, and large rice-filled heating pads. Use cell references in the formula—do NOT use numbers.

3. Karen wants to compare using the cost plus method to the percentage markup method. If she sells 2 small rice pads, 4 medium rice pads, 2 large rice pads, and 20 boxes of $3.50 tea in a month, how much profit does she accumulate? What markup percentage based on cost would she have to use to make the same amount of profit on this month’s sales?

(Show your work in this cell to solve for the profit she accumulates and the markup percentage based on cost she would have to use to make the same amount of profit.)Profit2 small rice pads profit: $2 (2) = $ 4 4 medium rice pads profit: $2 (4) = $ 8 2 large rice pads profit: $3 (2) = $ 6 20 boxes of tea profit: $0.70 (20) = $ 14Total profit= $32Cost2 small rice pads cost: $5 (2) = $ 104 medium rice pads cost: $8 (4) = $ 322 large rice pads cost: $12 (2) = $ 2420 boxes of tea cost: $2.80 (20) = $ 56Total cost= $122

Markup percentage based on cost= $32/$122 = 0.2623 = 26.23%




$15.00 2

BT Selling Price $3.50 20

Small RFP Cost= $5.00 2

Medium RFP Cost= $8.00 4

Large RFP Cost= $12.00 2

BT Cost $2.80 20

Excel Formulas Total Profit $32.00

Total Cost $122.00

26.23%

Variables Used Small RFP Cost= $5.00

Medium RFP Cost= $8.00

Large RFP Cost= $12.00

BT Cost $2.80

26%

Excel Formulas $6.30

$10.08


Markup % based on cost=

In the yellow cells, create the Microsoft® Excel® formulas that will give you the total profit, total cost, and required markup percentage based on cost. Use cell references in the formula—do NOT use numbers.

4. What prices should Karen charge (using the markup percentage) to obtain the same amount of profit as she did with the cost plus method? Do not include shipping.

(Show your work in this cell to solve for the profit she accumulates and the markup percentage based on cost she would have to use to make the same amount of profit.)Selling price = cost + (cost * markup)small rice pads $5 + ($5)(26%) = $6.30medium rice pads $8 + ($8)(26%) = $10.08large rice pads $12 + ($12)(26%) = $15.12boxes of tea $2.80 + ($2.80)(26%) = $3.53


Markup % based on cost=



$15.12

BT Selling Price $3.53


In the yellow cells, create the Microsoft® Excel® formulas that will give you the selling prices based on markup percentage. Use cell references in the formula—do NOT use numbers.

Case 9-1 Template

1. What is the markup percentage for a box of ginger tea?

2. If the rice-filled heating pads sell for $7.00, $10.00, and $15.00 for small, medium, and large, respectively, what is the markup percentage on each one?

Read the case in the textbook. As a team, answer the questions in this spreadsheet, then save and submit the assignment as one Microsoft® Excel® attachment. Also, submit a 1-paragraph Microsoft® Word document explaining any issues or successes you had in answering these questions.

In the yellow cells, create the Microsoft® Excel® formula that will give you the ginger tea markup percentage. Use cell references in the formula—do NOT use numbers.

In the yellow cells, create the Microsoft® Excel®formulas that will give you the markup percentages for small, medium, and large rice-filled heating pads. Use cell references in the formula—do NOT

3. Karen wants to compare using the cost plus method to the percentage markup method. If she sells 2 small rice pads, 4 medium rice pads, 2 large rice pads, and 20 boxes of $3.50 tea in a month, how much profit does she accumulate? What markup percentage based on cost would she have to use to make the

(Show your work in this cell to solve for the profit she accumulates and the markup percentage based on cost she would have to use to make the same amount

In the yellow cells, create the Microsoft® Excel® formulas that will give you the total profit, total cost, and required markup percentage based on cost. Use cell references in the formula—do NOT

4. What prices should Karen charge (using the markup percentage) to obtain the same amount of profit as she did with the cost plus method? Do not include

(Show your work in this cell to solve for the profit she accumulates and the markup percentage based on cost she would have to use to make the same amount

In the yellow cells, create the Microsoft® Excel® formulas that will give you the selling prices based on markup percentage. Use cell references in the formula—do NOT use numbers.

qrb 501 week 3 learning team case studies

Documents