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 20212241 units
Daily velocity based on the last 30 days28 units
In production - completion date 28 Mar 20211500 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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,346
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution
Status
Not open for further replies.

Forum statistics

Threads
1,147,497
Messages
5,741,502
Members
423,662
Latest member
Ajmal Khursand

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top