Finance 3.0 - Social Network for Finance

Smart financial thinking

Calculating Cost of Material

I have problem with calculation of the material cost. I have been calculating material cost using standard method (Beginning Inventory - Ending Inventory + Purchases = Material Cost)

I am valuing products at different unit costs because value is being added at each stage (i.e. Raw $1, WIP $2, FG $3). So in this case, if there are more products in FG stage, I would end with higher ending inventory, which would give me lower material cost.

To better understand this, I have attached a spreadsheet. Please take a look at it.
I am willing to change method or way how inventory is being valued. I appreciate all your inputs and methods.

Thanks for your help.
Jatin

Views: 57

Attachments:

▶ Reply to This

Replies to This Discussion

Permalink Reply by Werner Reisacher on June 24, 2010 at 12:54pm
Please have a look at the returned spreadsheet. I added some explanations to your data. I hope that this will help you to trace the transactions though the accounting system.
Werner Reisacher
Attachments:
Permalink Reply by jatin on June 25, 2010 at 8:45am
Thanks for quick response!

Just to take one step forward, how would you explain the scenario in the attached spreadsheet.

-jatin
Attachments:
Permalink Reply by Werner Reisacher on June 25, 2010 at 1:02pm
Have a look at the attached spreadsheet. I opened a new worsheet (sheet 2) and made some corrections to your data shown on sheet 1. I eliminated the "Production line". As long as you are working with the various inventories (raw,wip,finished) the only movements you will have are opening balances, transfer to the next level of inventories and finally out of finished goods into Cost of Sales.
Please let me know if you have any further questions.
Attachments:
Permalink Reply by jatin on June 25, 2010 at 2:00pm
This is amazing…It matched decimal to decimal.
Just couple things I am still confused about. So what you calculated is cost of sales. But how would you go around booking this entry.
Material expense xxxx
Inventory xxxx

The expense will hit the income statement which is obviously accurate. But what about for the balance sheet, how would the ending inventory be reconciled?
Permalink Reply by Werner Reisacher on June 25, 2010 at 10:31pm
Attached is a spreadsheet that will lead you through the entire accounting process of a Standard Cost System from the assumptions to the general ledgers, the P&L, the Balance Sheet, the Cash Flow statement to the Financial Analysis. The spreadsheet is interactive. You can change the input in the "assumptions sheet". Be careful, once you completed the assumption sheet, the system will automatically update the accounting system. I suggest you are making a copy first in case you are accidentially overriding a formula.
Attachments:
Permalink Reply by Werner Reisacher on June 25, 2010 at 10:56pm
Attached is the original cost sheet with Balance Sheet and P&L info shown in the lower part of sheet 2.
Attachments:
Permalink Reply by jatin on June 27, 2010 at 5:44pm
To my understanding, there should not be any Finished Goods inventory on the balance sheet because there is no ending FG inventory.

According the calculation, Ending inventory on hand inventory is...
RAW 39,295.33
WIP 184,151.66
FG 0
Total 223,447.00
Attachments:
Permalink Reply by jatin on June 29, 2010 at 3:00pm
Werner,

Thanks for all your help. I am going to take in some of your suggestions.

- jatin
Permalink Reply by Marty Sammon on June 27, 2010 at 8:10pm
Werner

Many thanks for sharing this. I am looking at developing a model based on the Business Excellence ebook by Phil Robinson of UK, and I am not sure of the best costing process to use at this point. We are an electrical manufacturing business, and in growth mode, so really appreciate your sharing this info.

Regards Marty Sammon
Permalink Reply by Eugene S. Magaway on June 28, 2010 at 9:44am
Please look at the plot Ive made for you to see the manufacturing flow in my own little way, Hope it'll help you,
Attachments:
Permalink Reply by Suman Kumar Saha on June 29, 2010 at 4:02am
Dear Jatin,
Please clear your concept on process of Material Flow & the theory of Process costing.
It will be easier for you to solve it yourself.

Suman
Permalink Reply by Gokhan Cansen on June 29, 2010 at 9:00am
You forgot to debit the WIP account with the added values. Unit Cost=Total Cost/Amount...Therefore you can not see an incresed unit cost if the total does not increase. In your example you kept the same 100 RawMaterial value all throughout did not add any value to WIP account, but claim that the unit cost increased..

RSS

Create Excel dashboards quickly with Plug-N-Play reports.

Continuing Education

Accounting
Banking Regulations
Certification Training
Commercial Bank Management
Corporate Finance & Valuation
Treasury Management
Credit Risk Management
Financial Derivatives
Financial Institution Management
Financial Markets
Financial Modeling
Financial Planning
Market Risk Management
Operational Risk Management
Operations & Compliance
Quantitative Analysis
Strategic Financial Management

Connect With Us

Newsletter Linkedin RSS Facebook Widgets Twitter

© 2012   Created by Finance 3.0.

Badges  |  Report an Issue  |  Terms of Service

Sign in to chat!