I recently started to work for a new company who has very limited financial systems. Until they implement oracle or SAP, I need to get into their financials and quickly automate some key reporting. Their current team seems to just be "data-mining" for even the most basic charts, taking hours on what should take minutes/seconds.
They have more than 30,000 accounts, and I need to be able to extract various elements for each customer like revenue, customer discounts, cost details, expense details, sales team, Region, etc.. They also don't have any Global roll-ups of customers, instead they only have different customer names/lines by region or type of delivery, so for example Amazon is broken out into 19 lines with varying designations after the word Amazon.
I am very rusty with these formulas, but using Amazon as an example, I imagine I'll need to extract / reference key customer words from their list (i.e. have a formula that pulls the word "Amazon" out of the the many longer Amazon names. But some customers have longer or shorter names than others, so this could be problematic with left function for example. Then possible use a sumif function to add up all the financials specific to that customer so I have a consolidated view. Lastly use a v-lookup(?) to reference the data quickly into various reports, sort customers by size, margins, etc..??? I will get new files from ERP exports weekly, # of rows will change each week with new customers, so I need to be both fllexible and automate reporting. Is there an easier way???
I'm attaching an example of a weekly ERP report thru "a" customers. With speed and automation in mind, what would you experts suggest? What formulas, how should I build it?
Thank much!
Bookmarks