qrb 501 week 3 learning team case studies

Case 8-3Case 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 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 Artists Palette purchases its inventory from a number of suppliers and each supplier offers different purchasing discounts. The manager of The Artists 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?

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

Variables UsedS1S2PP=$300.00$300.001st CDF0.200.182nd CDF0.100.123rd CDF0.050.07

Excel FormulaNP=$205.20$201.33In the yellow cells, create the Microsoft Excel formulas that will give you the net prices for the two suppliers. Use cell references in the formulado NOT use numbers.

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

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

Variables UsedS1S21st CDF0.200.182nd CDF0.100.123rd CDF0.050.07

Excel FormulaNDE=0.6840.671In 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 formulado NOT use numbers.

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


Variables UsedS1S2PP=$300.00$300.00NDE0.6840.671088

Excel FormulaTD=$94.80$98.67In the yellow cells, create the Microsoft Excel formulas that will give you the trade discount for the two suppliers. Use cell references in the formulado NOT use numbers.

4. The Artists 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 Artists Palette offers discounts to those students who pay sooner than required. Assume that if students buy a minimum of $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?


Variables UsedOriginal Cost=$250.00Discount=2.00%

Excel FormulaDue by 9/26=$245.00Amount saved$5.00In 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 formulado NOT use numbers.

5. Assume that if students buy a minimum of $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?

Part A and B:Supplier 1 Net Price: (1-0.20)(1-0.10)(1-0.05)(300) = $205.2Supplier 2 Net Price: (1-0.18)(1-0.12)(1-0.07)(300) = $201.33Part C: I would recommend Parma to purchase from Supplier 2, since his/her trade discount in series results in a cheaper net price.Supplier 1: (1-0.20)(1-0.10)(1-0.05) = 0.684Supplier 2: (1-0.18)(1-0.12)(1-0.07) = 0.671Trade discount: Original price - net priceSupplier 1: 300 - 205.2 = 94.8Supplier 2: 300 - 201.33 = 98.67Discount = 0.02Amount due = (1-0.02)*250 = $245Amount saved = 250 - 245 = $5The 2% discount expires on October 10th. I would prefer 2/10 EOM over 2/10, n/30 since for 2/10 EOM, the money can always be paid after the month with a discount, while with 2/10 n/30 it must be paid within the month, a specific window, to get a discount.

Case 9-1Case 9-1 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 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

1. What is the markup percentage for a box of ginger tea?Percentage markup = 0.70/2.80 * 100% = 25%


Variables UsedCost per BT=$2.59S&H=$0.21P=$2.80

Excel FormulasGinger Tea % Markup25.00%In the yellow cells, create the Microsoft Excel formula that will give you the ginger tea markup percentage. Use cell references in the formulado NOT use numbers.

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 UsedSmall RFP Selling Price=$7.00Medium RFP Selling Price=$10.00Large RFP Selling Price=$15.00Small RFP Cost=$5.00Medium RFP Cost=$8.00Large RFP Cost=$12.00

Excel FormulasSmall % Markup=40.00%Medium % Markup=25.00%Large % Markup=25.00%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 formulado 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 months sales?


Amount SoldVariables UsedSmall RFP Selling Price=$7.002Medium RFP Selling Price=$9.004Large RFP Selling Price=$14.002BT Selling Price$3.5020Small RFP Cost=$5.002Medium RFP Cost=$8.004Large RFP Cost=$12.002BT Cost$2.8020

Excel FormulasTotal Profit$61.50Total Cost$55.80Markup % based on cost=1.1021505376In 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 formulado 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.


Variables UsedSmall RFP Cost=$5.00Medium RFP Cost=$8.00Large RFP Cost=$12.00BT Cost$2.80

Markup % based on cost=40%

Excel FormulasSmall RFP Selling Price=$7.00Medium RFP Selling Price=$11.20Large RFP Selling Price=$16.80BT Selling Price$3.92In the yellow cells, create the Microsoft Excel formulas that will give you the selling prices based on markup percentage. Use cell references in the formulado NOT use numbers.

Original prices are $5, 8, and 12.Markup % (for small pads) = ($7 - $5)/($5) *100% = 40%Markup % (for medium pads) = ($10 - $8)/($8) *100% = 25%Markup % (for large pads) = ($15 - $12)/($12) *100% = 25%The cost plus markup method uses a markup that is not determined by cost nor selling price. The percentage markup method uses a markup that is based of a percentage of the cost or selling price. Total profit: $61.50Markup %: 1.102%5.00 * 0.40 = 25 + 2= 7Karen's selling price with a 40% markup percentage on the cost will be $7.

qrb 501 week 3 learning team case studies

Documents