Sales Analysis and COGS

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hello!

Another question, when I make Sale Analysis Reports , the COGS is the average price per unit, as we do average inventory costing method, nevertheless sometimes to yield this reports this amount is not very useful, as for example the price of certain raw materials peak and drop cyclically, so it would be fantastic if we could for example select the last unit cost for the last sale, or the average unit cost for only the last 3 months. Is this possible, and if so, how?

Thanks!!

*This post is locked for comments

All responses (19)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS

Hello Mr. Salot,

Yes, I am facing with a problem which is that I cant manage to get the last unit cost on the matrix. My approach was as it follows: As you proposed I created the Code UNIT-COST in analysis type,with value type "Unit Cost". Then in order to get the last unit cost I tried to apply a date filter in Item ledger Entry Type Filter as well as in Value Entry Type Filter, but unfortunately, the system doesnt allow me to do so. So I tried to change the value type to Cost Amount, there I could use a date filter but the value given was not the last unit cost. Any ideas? Thanks heaps!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS

Yes , I understand your approach. I used to download fields of data from our old system into Excel and from there I made my reports. Our IT guy is not here today, I will comment him that on Tuesday.
Suggested answer

Alex A 2,348 on at

Like (1)

Report

RE: Sales Analysis and COGS

You can select the Unit Cost for each sale directly from the Posted Sales Invoice line. Using the Quantity sold and the Unit Cost you can then determine the Cost of that Sale.

You can do this for a single invoice or ten thousand invoices or group the results by month, by year, and/or by product class, or however you want. When you sum it together over a long period of time you still get the exact Cost of Sales from each individual sale.

If you have someone who knows SQL, then there is no limit to what you can do, such as determine the Adjusted Sales amount (after returns), the amount of Sales Credits, or the Gross Margin (GM%) with comparisons between months or years for the same product class.
Suggested answer

Community Member Microsoft Employee on at

Like (1)

Report

RE: Sales Analysis and COGS

Right. You can achieve the result by managing formula.

Let us know, if you face any challenge within same.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS

Ok, good , I figured out what you meant. Correct me if i am wrong. By having the last unit cost , I can then multiply that for the quantity of the period I want and then deduct it from the sales price. So just making new formulas and so on.

I just have to manage now to get that last unit cost there.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS

by that I mesan that resolts were shown. But the profit, cost of sales is the same.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS

we shouldnt, just for a couple of products. The reason why we didnt go for FIFO is because the rule first in first out would not be carried out by our warehouse personal. We still have to put some order there, so untill then I thought that average would be better.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Sales Analysis and COGS

If you have too much fluctuation in Raw Material Pricing and would like to see the actual COGS, you should use FIFO costing method instead of Average.