Excel Sheet Amazon Calculator

We require 1 Excel Workbook with 2 worksheets the details of each worksheet are highlighted below. The objective of this excel sheet is to be able to input products and values to automatically calculate profit however we also need to be able to input a required profit and work backwards to show us the required list price to attain the inputted profit. Example If I place my cost price list price shipping packing material cost labor cost it should give me a final profit amount Alternatively If I place a desired profit it should be able to reverse calculate and suggest a list price taking into consideration all the factors that are attributed in finding the profit. Ie. Cost, Cost vat, Shipping, Amazon %, Post % and anything else. In the second sheet we require the same however we would like to be able to have a global drop down box with a markup percentage that can be set and added to the cost so we can price our products based on desired markup. Example 5%-200% When an amount in the drop down is selected that adds a percentage markup to the cost in order to reach a suggested list price. 1) Price Calculator Columns Required 1) ProductName This requires no formulas It’s just a field we enter a product name 2) Weight This requires no formulas it is just a field we enter a value into 3) MSRP (Manufacturer Suggested Retail Price) This requires no formulas it is just a field we enter a value into 4) Cost This requires no formulas it is just a field we enter a value into 5) VAT RATE This requires no formulas it is just a field we enter a number into 6) Cost VAT VAT is value added tax, in the UK which is currently 20% This field will need to automatically calculate the VAT payable on the cost price entered above taking into consideration the value in the VAT RATE COLOUMN. That formula should be (Cost x VATRATE) 7) ListPrice This is the field that contains the suggested list price based on the percentage mark up that calculated when a certain mark up is selected from the global drop down box The formula Should be (Cost x Markup%) + Cost 8) Shipping This requires no formulas it is just a field we enter a shipping value into 9) RRP This Is a Field that checks if our ListPrice price has exceeded the MSRP price by showing one of 2 Values a) Higher b) Lower The formula for this should be If List price > MSRP show HIGHER in Red colour If List price < MSRP show LOWER In Green Colour 10) Amazon % This is the a field that displays the percentage amazon take from the list price . That is currently 15% commission. The formula for this is ListPrice x 0.15 11) ShippingPrice % This is a field that displays the percentage amazon take from the Shipping. That is currently 15% commission. The formula for this should be Shipping x 0.15 12) Actual Postage Cost This field will display the final postage cost for the sale this needs to be able to calculate in 2 ways If Weight value is entered then it will check the following bands of postage: If weight is 0-100g then the postage cost will equal £1.36. If the weight is 101-250g then the postage cost will equal £1.69. If the weight is 251-500g then the postage cost will equal £1.90. If the weight is 501-750g then the postage cost will equal £2.36. If the weight is 751-1000g then the postage cost will equal £2.86. If the weight is 1001-1250g then the postage cost will equal £3.98. If the weight is 1251-1500g then the postage cost will equal £4.68. If the weight is 1501-1750g then the postage cost will equal £5.37. If the weight is 1751-2000g then the postage cost will equal £5.99. If the weight is 2001-4000g then the postage cost will equal £7.83. If no weight value is entered then postage cost will equal £1.90. 13) Shipping +/- This is the final amount added or subtracted from the profit. The formula for this should be (Shipping-Actual Postage costs-ShippingPrice%) 14) List VAT This is a field that calculates the Amount of VAT within the List price The formula for