Create Spreadsheet template

Hello, I need to create an excel template for my manager to fill in. My concept is below. I have 150 x items (maybe more to add later on). ITEM-1 to ITEM-150 These items are imported to my warehouse in parcels that contain various combinations of ITEM-1 through to ITEM-150. For example: Parcel 1, Tracking number #0001 May contain: 25 x ITEM-2 20 x ITEM-7 10 x ITEM-120 ———- I need to be able to enable a macro that automatically updates my warehouse stock once that parcel has arrived with one click, so that my local inventory is updated. ———- Once items are arrived to my warehouse, I sort them and store them until customers order. All my customers have the same flat rate per item, however I need to be able to add a function where I can discount their total order by whatever % I wish if I feel it is applicable. Some customers purchase products in full, Other customers purchase products on consignment And some customers purchase items with a % deposit and the rest on consignment I have up to 50 x customers (need to be able to add more later) ——- As for staff, they are paid by commission. Staff receives X% commission for every item sold. Each item the staff has different commission X% for. I need a function to be able to give staff bonuses. ——— I also have overheads. I need a function for this. —— I need to be able track profit. I have a cost price per item, and customers have a price per item. I need to be able to track staffs weekly commission, weekly profit/loss and overall profit/loss/expenses/staff. ——- I need to be alerted by a colour when items reach a low point of 25 x items each, or whatever low point I delegate. ——- I have multiple suppliers who supply different items to me within my inventory. I need to be able to track what I owe/pay each supplier. At current, I have 5 suppliers. My suppliers list items to me on consignment also and sometimes I pay up front. ———- For my template, I have the following scenario I would like entered so that we can create the sheet: I have $10,000 AUD in my business account I currently owe no suppliers any money and my customers currently owe me no money either. I order the following items from Supplier 1 on 1/12/2017 on consignment 10 x ITEM-1 10 x ITEM-2 I order the following items from Supplier 2 3/12/2017 up front payment 10 x ITEM-3 10 x ITEM-4 Supplier 1 sends the following parcels on the 2/12/3017 Parcel 1, tracking #001 - 5 x ITEM-1 - 5 x ITEM-2 Parcel 2, tracking #002 - 5 x ITEM-1 - 5 x ITEM-2 Supplier 2 sends the following parcels 4/12/2017 -10 x ITEM-3 -10 x ITEM-4 —————- All three parcels then arrive to my warehouse on the same date, the 10/12/2017 and are updated on my local Inventory —————- Week 1 of business 10/12/2017-17/12/2017: Customer-A places an order of 2 x ITEM-1 2 x ITEM-2 He pays for all the items up front. I discount him 10% ————— Customer-B places an order of 2 x ITEM-1 2 x ITEM-2 He pays for 50% of the items up front, and 50% he has a consignment owing. ———- Customer-C places an order of 9 x ITEM-4 He accepts the entire order on consignment. ———- My cost price for items: ITEM-1 $10 ITEM-2 $10 ITEM-3 $20 ITEM-4 $50 ———- Customers cost price for items: ITEM-1 $20 ITEM-2 $20 ITEM-3 $40 ITEM-4 $100 ———— Staff is paid 5% commission per item I give staff a bonus of $10 ———— Overheads for the week cost $20 ———— Low points to be alerted are when an item only has 1 item left on local stock. ———— What is my weekly profit/loss? What was overall weekly staff costs? What was overall weekly overheads? Do I need to order any products from suppliers ASAP? What do I owe suppliers?