Calculate target margin, break-even and purchase order

[Legacy 483478]

Hi guys can someone help me solve these problems

Pricing

Below are the costs to source and sell a laptop cooling pad on Amazon.

1. Purchase cost - 7.49€
2. Ocean freight - 1.03€
3. Amazon commission - 15% of selling price
4. Order fulfilment fees - 3.99€
5. Storage fees - 0.15€
6. Average PPC (advertising) spend - 8% of selling price
7. VAT - 20%. The selling price should be inclusive of VAT. For example if the VAT inclusive price of a product is 120€, the customer pays 120€ which includes the 20% VAT 20€. Net proceeds for the seller will be 100€.

Calculate

1. the ideal price to meet target margins of 18-22% and,
2. the breakeven price

Production and shipment planning



You are required to decide the next purchase order (PO) for a gaming keyboard with the information below. Feel free to make any assumptions necessary.


A. Stock situation

Stocks at Amazon - 1 Mar 2021	2241 units
Daily velocity based on the last 30 days	28 units
In production - completion date 28 Mar 2021	1500 units

1. Production timelines

Lead time - 60 days

Minimum order quantity - 1000 units

Order schedule - POs are placed once every 30 days


Shipment timelines
Transit time from factory in China to Amazon - 45 days

1. Past sales history

Mar 20​

Apr 20​

May 20​

Jun 20​

Jul 20​

Aug 20​

Sep 20​

Oct 20​

Nov 20​

Dec 20​

Jan 21​

Feb 21​

Total Sales​

€22,655​

€22,655​

€20,228​

€20,228​

€20,228​

€20,228​

€22,655​

€24,273​

€43,691​

€68,774​

€29,128​

€22,655​

Total Units​

840​

840​

750​

750​

750​

750​

840​

900​

1620​

2550​

1080​

840​

Daily Velocity​

28​

28​

25​

25​

25​

25​

28​

30​

54​

85​

36​

28​

Questions

• When will you place the next PO?
• What would be the PO quantity?
• Explain your reasoning and state any assumptions you made to arrive at your decision.
• What additional information would be relevant to make a better decision?
• Do you have any proposals to improve the supply chain?

 

[StephenCrump]

Welcome to the Forum

If you get stuck with Excel, and have specific questions, we'll be very happy to help.

But no one here is going to do your assignment for you.