SALES Tracker
The system is designed to support management decisions for B2C distributors, dealers, or suppliers maintain an active sales team with multiple product lines.
The system intent to deliver the following metrics:
- Up to date Order levels in the current and previous season YTD at financial and SKU Volume level.
- Grouping all order data by product lines and/or by product name.
- Track intermediary approvals status if business is not direct
- Measure SALES team performance against Yearly targets
- Compare Seasonal Sales Representatives Forecast against previous season performance.
Felhasználói jogok:
The system differentiates 3 main user type:
Owners: is developers user right, which provide full access to all databases
Management: is the Management Team right the team has editing rights for all database, they are able also to change views and create pages.
Agents: is the default user right to all employees with an active Microsoft subscription in the company. This right provides reading right for the site, and editing rights for lists which they will interact from the UI, but have no direct links to the DS.
Agents has managed Access on selected databases, as the Order Form PDFs and Activates Products, these relations will be details in the Automations.
Szolgáltatási követelmények:
The system is based on following service foundations which are criteria to set up the system at the company:
- Microsoft Office 365 Enterprise Subscription for all users
- PowerBI Premium Subscription for the developer account
- PowerAutomate and PowerApp default package
- Microsoft Teams enrolled for the organization
- Microsoft Sharepoint Service for Businesses
Adatstruktúra
Foundational Database:
Currency: SourceData for Pricing and calculations
Country List: Source Data for Geography
CompanyForms: Source date for Account List
ProductLines: Source data for Products; Source of Distributor Discounts as static database. Update on discounts applies all pending and newly generated OrderRequest!
Opt_ActiveIngredients: List store all Ais used in product, and core information.
Functional Database for Members:
Product_List: Struct based Product DataSource. Product Category, core UOM are defined. Indexed by Product Name.
ActivePrice_List: Indexed by Unique Product. Products could be removed or added, Prices could be updated any time. Fully automated based on ProductList generated by PA see in Automation Section.
Agents_List: Details for Agents, company sales representatives. Indexed by Agent. Company e-mail is stored in the list.
Distributor_List: Indexed by Distributor name, Billing Address, VAT. Records managed at national level, no regional areas managed in this list.
DistributorContact_List: Indexed by Dsitributor ContactName. Multiple Agent association is allowed by Distributor. List is filtered in UI by Agent to look up contacts.
Commissions: The list is a yearly log of commission rates and fees. Enable Volume rates for the Commision Levels. Define target Annual target volumes and the Weight of the ProductLine element in the financial bonus calculation.
Executive Lists for Agents edited through UI
Account_List: Account details Indexed by VAT Number.
DeliveryAddress_List: Unique Delivery address update by Account. Indexed by Address && Account Name. Duplicates are allowed, list will be filtered by Account Name in UI. PA is connected (Delivery Address Updater)
OrderRequest_List: Indexed by OrderRequest (Megrendelesi Szam) Details of the delivery terms, and distribution, payment. Info of the Ordering account and the agent. Tracking of the Order Status. Tracking of the approval, and cancellation PA is connected (OrderNumberID Generator)
OrderDetails_List: Indexed by Product && OrderRequestID. Duplicates allowed. Product details in the order, with an auto filled OrderRequest Number.
Forecast_List: User provide Forecast for each SALES Season by Account and Product. List Structure: Account (Lookup), Season (Lookup), Account (Lookup), Products (Lookup from Active Price List) Forecast_Archive: Same structure as above, all FCST should be moved based on a time counting of the season. PA triggered if a new Season is starting.
Data Archive
Archive_List: List collects all OredrDetail lines and OrderRequest Combinations. List is available of Members, designed for tracking Deliveries and Invoices. The list is generated by PA after status changed to Approved by internal and external approvals.
The list is the foundation of all reports, as it summarize all predefined standard database in ONE list.
Automatizálások
- DeliveryAddress Concatenator: Targeted List DeliveryAddres_List, run if UpdateRequest is true. Triggered by UI. Simple TEXT Value generator.
- Account Updater: Targeted List Account_List, run if UpdateRequest is true. Triggered by UI. Simple TEXT Value generator.
- ProductActivator: Targeted List Product_List, run if Update Requested is true && Activation is Activate. Create lines in Active Pricelist by Unique Product Name and by user request on Activation type.
- Product Deactivator: Targeted List Product_List, run if Update Requested is true && Activation is Deactivate. PA removes all user reading permission for the Active Price_List record. Then checks if there is appending order for the product, and loop the review till all records with the product are archived. Delete the item from ActvePrice_List.
- OrderNumberID Generator: : Targeted List OrderRequest_List, run if new item created. Calculate the length of the ListID, and assign number of 0 to have the same 5 digit length number. Calculates current Year number and assign in the ID. Updates the OrderNumber.
- SaveSigniture: Targeted List Megrendelo Documentumok Library, run if new OrederRequest sent for approval. Decodes the binary image save it to the same folder as the Megrendelo Extra extension after the OrderRequest Code.
- PDF_Generator: Targeted List Megrendelo Documentumok Library, run if new OrederRequest sent for approval.
Archive the Megrendelo preview from the HTML Form on the Signiture page. Ad the logo & the Saved Signiture of the Partner. Store the final document at Sharepoint.
- OrderRequestConfirmation: Targeted List OrderRequest_List, run if Order Status="Sent".
Manage the Internal and Distribution approval for each OrderRequest. Read Forgalmazo Kontakt e-mail, and send an approval with comment option to extrenals (Approve, Rejecy), and an Approval for internals with simple approve or reject option. Based on approval outcomes set Status Confirmed or Cancelled.
- UpdateUserPermissions: Targeted List ActivePriceList, run if AgentAssignmnet is not Empty. If true then rewrite the Read permission only to the selected Agent.
- Archivation: Targeted List Archive_List, run if OrderRequest staus is set to Confirmed.
The system copy the records with the conformed OrderRequest numbet to the Archive_List, and change the status Archived. Update User Access for each item for the Agent.
- Forecast ItemRights Updates: Targeted Forecast_List, run id record created or edited. AgentAssinment column is empty? If NOT it rewrite the Read permission only to the selected Agent.
- Forecast Updater: Targeted Forecast_List, run id record created or edited.
PA retrieve Product Information and udates the ProductLine Value.
User Interface:
The UI is defined in PowerApp for iPad screens. Could be used from iOS Android app store through the dedicated PowerApps app, or at the web by the link provided to the users. The look of the UI could be adjusted by the branding guideline of the purchasing company.
The system has a backside user authentication by the Microsoft account, which get verified evry time the system is opened by the user. All data is queried to the user access in terms of Accounts Account details.
The UI has the following main pages defined for the users:
UI Architecture:
Left panel is functional area for start new processes, top area is main navigation buttons – back refresh, home. Color splits the customer facing and internal functions
Top right area is the administrative working area. Search option, Cancel and Save work.
Right mid and bottom area is the main working area, where the lists and forms will load data could be selected, or loaded.
The UI has the following main pages defined for the users:
Orders Overview: Quick review of all Orders created by the user. Chance to modify, cancel existing OrderRequest, or add new ones.
Add New Order Request: Form to fill details about the new OrderRequest.
Order Details overview: Quick view of the total list of products, Volumes Price, and discounts for the selected Order Request
Add New Product to the Order Request: Form to fill with all details for the product sales. Delivery location, and deadline, payment, stock location, discounts, and FOC products.
Add New Account: Form to fill all details to record a new Account
New Delivery Address: Form to add a new delivery address to a Selected, or defined Account.
Order Form Review: Visual Review of the total order and delivery location. Panel for signature from the Account representative, and send in the request to approval
Reports:
The goal of the system to provide an accurate, and up to date status on the total orderbank of the company, with relevant financial calculations. The reports also intend to provide direction to the management in terms of individual and company performance.
The reports are available in Microsoft PowerBI service in a dedicated Workspace which could be accessed by the management members and the Agents. The report is updated in every 2 hours with the most updated view. The reports are visually design of a quick decision making, but a full database export is also enabled in the report, design by the company structure.
By default the system provides the following default reports:
Pending Distributor Orders: All OrderRequests which are waiting for Distributor approval
Country Overview: Quick insights about the current and previous year performance by Net Sales which could be filtered across product lines
ProductLine Review: Weekly Sales comparison of current and previous year by a selected product line for the sales and volumes. Further filtering by SALES rep is available.
Regional Sales performance YTD current and previous year, by Account of Sales and average sales by account.
Forecast Review: Comparison of the Sales Rep forecast with the previous year sales performance in the region, by Account.
Sales View: designed for Sales reps, Shows current year and previous year Net Sales performance, pending deals in Distributor approval, filter to the User visiting the report.
Sales Goals: Target measures by Management goal plan. Showing an objective performance of each
Sales History: Sales Region historical sales report for Sales force seasonal planning process.
