# Calculate target margin, break-even and purchase order

Status
Not open for further replies.
L

#### Legacy 483478

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

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?
• What additional information would be relevant to make a better decision?
• Do you have any proposals to improve the supply chain?

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### StephenCrump

##### MrExcel MVP
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.

Status
Not open for further replies.

Replies
3
Views
258
Replies
6
Views
84
Replies
6
Views
375
Replies
5
Views
498
Replies
3
Views
351

1,191,093
Messages
5,984,635
Members
439,897
Latest member
osman53

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back