Northwind Template Modification

[Raleyoz]

Hi All


i have been looking a developing a database for my business, after several personal attempts to no avail and 2 trys at getting a Pro to develop but can not seem to get them to do what i need, i still find that the Northwind Template is very close, with a few tweeks
i would like to be able to see profitability for each sale
each sale will have a commission and actual shipping charge and i would like to be able to see profit for each supplier/ product.

i dont think this would be hard but beyond my skills

any advice would be great

[orange]

Do you have specifications?
Can you show us what you have, what the pro left you etc?

[Raleyoz]

How do I show you?
Sorry I'm new

[June7]

Type out detailed explanation of attempts. Show query statements. Show examples of data - raw data and desired output. Attach images or database to post. Follow instructions at bottom of my post.

Essentially, will need to do aggregate queries that summarize costs of product and sales of product then a query that joins those. Probably more difficult than it sounds because of inventory methods. Product prices can change and determining profit/loss for a particular period must consider that.

Bing: Access database profit loss report

Unfortunately, not finding much but this looks interesting https://www.microsoft.com/en-us/down....aspx?id=38838

Not sure I would want to reinvent the wheel. OTS applications like QuickBooks probably have this capability.

[Raleyoz]

sorry for the delayed response

my business is a little different, when i purchase a product the from my supplier the purchase cost is set in stone and will remain that way until a new years products.

so when i make a sale i can ,but not always, charge a shipping fee. This shipping fee is an educated guess, as the actual cost of shipping is often 20-30 days away. when i receive the shipping invoice i need to be able to enter the actual cost against the sale

example
sale $ + Shipping Fee= income,
cost of goods (COG) + actual shipping Charge + Sales commission = expense per sale
income - expense = profit

Currently using the Northwind template i can add shipping fees to the sale order , but i can not add the actual shipping fee to the expenses of each sale

I would like to be able to allocate a sales persons commission to the sale to correctly represent the profit for each sale and the any group of sales

[Raleyoz]

Product prices can change and determining profit/loss for a particular period must consider that.

Fortunately this does not occur in my business

Unfortunately, not finding much but this looks interesting https://www.microsoft.com/en-us/down....aspx?id=38838

had a look at this but i have no idea what this actually means

Not sure I would want to reinvent the wheel. OTS applications like QuickBooks probably have this capability.

I have been working with Quick books and as sales analysis tool and inventory manager it is basically useless

[June7]

Accounting as I understand it (speaking as one who studied it and used to be a bookkeeper), a business's proft/loss is not normally calculated at each individual sale level. Profit/loss is determined by period (month, quarter, year).

Calculating a profit margin by product in order to set selling price is another matter. Product pricing is, at best, a guesstimate and something of an art.

Been a long time since I looked at QuickBooks. I have used Great Plains Accounting - a very sophisticated, very expensive product (bought by someone and renamed, forget who and what).

[Raleyoz]

Thanks June7

I understand that this is not normal, i can make it work with excel, but it is very time consuming and it have limitations when i get to the inventory and the reporting function

just to try and be a little clearer here is more detail

I am a Wine Importer
i buy wine from my own companies in AUST and export it to my company in the USA

so my purchase cost are fixed

when i sell the wine to a customer, they can order any wine(SKU) from all across my portfolio.
weather it be a online sale , a retailer, or a distributor, they all have a wide and varied discount structure.
also some pay shipping, and some have shipping included
and some sales have Sales Commission and some dont

each of these factors effect the profit of of each SKU

i need to be able to report the performance of each sale to my supplier Wineries, to analyse the profitability of each SKU or category

[Raleyoz]

FYI, as the title suggests i have entered some preliminary data into the Northwind Template and it looks to me like it would just need some tweaking

or is that just Fantasy

Cheers

[June7]

Not saying can't be done, am saying seems difficult and probably a major change in Northwind structure.

You pay salesperson a commission by check? That check could include commission for multiple SKU or category sales? How do you want to input that check data so it is broken out by SKU/category?

The AUST companies costs and prices for a particular vintage will never change - no matter when or where they acquired the bottle?

[Raleyoz]

Not saying can't be done, am saying seems difficult and probably a major change in Northwind structure.

ok i have tried to get a "Access programmer" (2 in fact) to look at the idea but it does not appear to be getting through and i have lot confidence in them understanding

You pay salesperson a commission by check? That check could include commission for multiple SKU or category sales? How do you want to input that check data so it is broken out by SKU/category?

i do not want this to be my accounting software, i want it add up the commission for each sale add them together and give me an amount each period then i write a check/transfer the money to the salesperson. in Quickbooks i just need to record the expense

The AUST companies costs and prices for a particular vintage will never change - no matter when or where they acquired the bottle?

Correct, i set the COG,

[June7]

The accounting app or not, check number or not, if you want to summarize by SKU/category, records will have to be input reflecting that detail. This might be another field (Commission) in the sales record or some other structure. Maybe not so major a change after all. Not a strict normalization because not every record will have commission but my attitude is "normalize til hurts, and de-normalize till it works", or vice versa.