Extra hard challenge, please help

joanna17

New member
Joined
Feb 21, 2017
Messages
1
Reaction score
0
Points
0
Hi,

I want to produce a spreadsheet in which i have a schedule of services with pricing required by a customer to calculate the profit and annual Turnover etc like below;

BrandSite NameSite AddressSite TownSite PostcodeAccess IssuesServiceNumber of ContainersLifts Per WeekBuy PriceSell PriceWeekly Cost (£)Monthly Cost (£)Annual Cost (£)Monthly Sell (£)Annual Sell (£)Annual Profit (£)Profit Margin (%)
EGExample 1EXAMPLE 1IslingtonEXAMPLEN/A|Euro 240 Litre - Glass16£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!
Euro 360 Litre - General Waste17£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!
EGexample2EXAMPLE 2LondonEXAMPLEN/A|Euro 360 Litre - General Waste18£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!
EGexample 3EXAMPLE 3ChelseaEXAMPLEN/A|Euro 360 Litre - Glass15£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!
Euro 120 Litre - Food Waste16£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!
EGexample 4EXAMPLE 4LondonEXAMPLEN/A|Euro 240 Litre - Glass13£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!
Euro 1100 Litre - Mixed Recycling11£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!
Euro 1100 Litre - General Waste19£0.00£0.00£0.00£0.00£0.00£0.00#DIV/0!

However I would like to base the buy prices on 3 or 4 set of prices from various suppliers for each service and be able to quickly flick between the pricing sets. I would then like to base the sell price on various % mark ups from the buy prices to create what-if scenarios. I would also like to mix and match between suppliers on specific lines if this would be possible at all!

Can anyone help ?

The spreadsheet would have around 30 lines on it and a total of around 16 different services.

i don't think the what if analysis can do what i need ?

the business is of a broker/middle-man nature

thanks in advance
 
However I would like to base the buy prices on 3 or 4 set of prices from various suppliers for each service

Hello
If you have a list of suppliers prices that is kept up to date, you could create an extract facility that will identify all the instances of a particular product, and let you filter this down to the one you want. You would need to have a suitable grouping for a given product so that all the supplier instances could be found, and of course if your prices were out of date, you could make the wrong decision. If this idea sounds helpful, I can supply you with a sample workbook that demos it. The content will be different obviously. I think mine was used to get a list of customers that matched a partial key and the idea was to filter down to one line by entering the full key for the record you want. The detail from this was then copied into the invoice form on the adjoining sheet. Clearly the full key that finally goes into the search cell must uniquely match one record.
 
Back
Top