I am open to the format for creating the project, I would like to hear ideas for filemaker, access and a web app based solution(hosted internally), using visio and MS SQL express
We are a company that dismantles Computers to sell the parts. We need to create a report / Analysis tool to review sales and inventory Data. There will be a primary catalog of items referenced by Sku and regularly imported data on those sku’s containing sale data and inventory levels. There should be a GUI, for adding and adjusting records but the primary import export tool will be via .csv.
We will need to be able to generate reports, and export data to a csv to be uploaded to our other systems. All reports should be able to export data to a .csv for easy work in excel. We will also need to specify header fields so that they are consistent across platforms.
A list of all our existing products. Sku field, Description.
Several Groupings of Sku’s that make up a particular model Computer.
Data to be imported.
Sales data organized by Sku. One import record will contain the SKU, Qty Sold, Date sold and total price for that sale.
Inventory Qty on hand. Imported as SKU with Qty.
Purchasing information, containing date and price paid.
A sales report / graph showing total sales by Dollar. It would be adjustable for both periods of time and aggregated totals for daily, monthly, quarterly, yearly totals…etc. I would like to be able to add trend lines (moving averages) like in excel. I want to see linear sales i.e. all sales in one line from start to finish. I would also like to be able to compare the same time period from different years.
A sales report / graph by SKU that shows units sold and average sale price per unit. This report would need to be adjustable for time periods. I would also like to be able to look at a single sku or a larger list of all items.
A report that shows SKU Qty on hand, average sales price for that item (with an adjustable time to calculate the average 30 60 90 days etc), and compares stock on hand sales to determine Sales velocity for that item. The concept here is to have a report that tells me what my fastest moving products are, and what the slowest moving SKU’s are.
A report generating a list of SKU’s that we have QTY of but have no sales in a given time frame and be able to adjust that time frame on the fly.
A report to value inventory. Qty on hand multiplied by the average sale price (again average for sales over an adjustable time frame).
A report by SKU groupings (a list of skus contained in a computer). This report would show inventory on hand, sales price and sales velocity. The idea is to know what a computer is worth by the sum of its parts.
A report showing Purchases in dollars vs Sales in Dollars vs inventory value in dollars.