COSTING BASIC FORMULAS & COST VOLUME PROFIT (CVP) FORMULAS

Cost of Goods Manufactured and Sold Statement Formulas:

v Prime Cost = Direct Materials Cost + Direct Labor Cost

v Total Factory Cost or Manufacturing Cost = Direct Materials + Direct Labor Cost + Factory Overhead

v Conversion Cost = Direct Labor Cost + Factory Overhead Cost

v Cost of Goods Manufactured (COGM) = Total Factory Cost + Opening Work in Process Inventory – Ending Work in Process Inventory
Or
Cost of Goods manufactured = Direct materials cost + Direct labor cost + Factory overhead cost + Opening work in process inventory – Ending work in process inventory

v Cost of goods sold (COGS) = Cost of goods manufactured + Opening finished goods inventory – Ending finished goods inventory
Or
Cost of goods sold = Direct materials cost + Direct labor cost + Factory overhead cost + Opening work in process inventory – Ending work in process inventory + Opening finished goods inventory – Ending finished goods inventory

v Number of units manufactured = Units sold + Ending Finished Goods units – Opening finished goods units

v Per unit cost of goods manufactured = Cost of goods manufactured / Units manufactured

v Materials used or consumed = Opening inventory or materials + Net purchases of materials – Ending inventory of materials

Income statement formulas:

v Gross profit = Net sales – Cost of goods sold

v Operating profit = Gross profit – Operating expenses

v Operating or commercial expenses = Selling or marketing expenses + General or administrative expenses

v Per unit gross profit = Gross profit / No. of units sold

v Per unit net profit = Net profit / No. of units sold

v Percentage of GP to sales = (Gross profit / Net sales) × 100

v Percentage of net profit to sales = (Net profit / Net sales) × 100

Cost Volume Profit (CVP) Formulas:

v Contribution margin = Sales – Variable expenses (manufacturing and non-manufacturing)

v Net operating income = Contribution margin – Fixed expenses (manufacturing and non manufacturing)

v Contribution margin ratio = Contribution margin / Sales

v Break even point (units) = Fixed expenses / Unit contribution margin

v Break even point (dollar sales) = Fixed expenses / CM ratio

v Units sales to attain target profit = (Fixed expenses + Target profit) / Unit contribution margin

v Sales to attain target profit = (Fixed expenses + Target profit) / Contribution margin ratio

v Margin of safety = Total budgeted or actual sales – Break even sales

v Margin of safety percentage or margin of safety ratio = Margin of safety / Total budgeted or actual sales

v Degree of operating leverage = Contribution margin / Net operating income

FINANCIAL RATIOS WITH FORMULAS

FINANCIAL RATIOS WITH FORMULAS

General profitability:

  • Gross profit ratio = (Gross profit / Net sales) × 100
  • Operating ratio = (Operating cost / Net sales) × 100
  • Expense ratio = (Particular expense / Net sales) × 100
  • Operating profit ratio = (Operating profit / Net sales) × 100

Overall profitability:

  • Return on shareholders’ investment or net worth = Net profit after interest and tax / Shareholders’ funds
  • Return on equity capital = (Net profit after tax Preference dividend) / Paid up equity capital
  • Earnings per share (EPS) ratio = (Net profit after tax Preference dividend) / Number of equity shares
  • Return on gross capital employed = (Adjusted net profit / Gross capital employed) × 100
  • Return on net capital employed = (Adjusted net profit / Net capital employed) × 100
  • Dividend yield ratio = Dividend per share / Market value per share
  • Dividend payout ratio or pay-out ratio = Dividend per equity share / Earnings per share

Short Term Financial Position or Test of Solvency:

  • Current ratio = Current assets / Current liabilities
  • Quick or acid test of liquid ratio (for immediate solvency) = Liquid assets / Current liabilities
  • Absolute liquid ratio = Absolute liquid assets / Current liabilities

Current Assets Movement, Efficiency or Activity Ratios:

  • Inventory / Stock turnover ratio = Cost of goods sold / Average inventory at cost
  • Debtors of receivables turnover ratios = Net credit sales / Average trade debtors
  • Average collection period = (Trade debtors No. of working days) / Net credit sales
  • Creditors or payables turnover ratio = Net credit purchase / Average trade creditors
  • Average payment period = (Trade creditors No. of working days) / Net credit purchase
  • Working capital turnover ratio = Cost of sales / Net working capital

Analysis of Long Term Solvency:

  • Debt to equity ratio = Outsiders funds / Shareholders funds or External funds / Internal funds
  • Ratio of long term debt to shareholders funds (Debt equity) = Long term debt / Shareholders funds
  • Proprietary of equity ratio = Shareholders funds / Total assets
  • Fixed assets to net worth = Fixed assets after depreciation / Shareholders’ funds
  • Fixed assets ratio or fixed assets to long term funds = Fixed assets after depreciation / Total long term funds
  • Ratio of current assets proprietors’ funds = Current assets / Shareholders’ funds
  • Debt service or interest coverage ratio = Net profit before interest and tax / Fixed interest charges
  • Capital gearing ratio = Equity share capital / Fixed interest bearing funds

GOOGLE DOCUMENTS – EXCEL WITH FORMS

GOOGLE DOCUMENTS EXCEL WITH FORMS

Google having documents, see docs.google.com, which is free to use and more flexible.

Through google documents, you can add data from anywhere and get all data in single file. Just think, if you have sales representatives through out the country and they will use their mobile phone or laptop to update daily collection, sales and customer feedback. How it is more useful to you to get a summarized report from the data at a same time once they entered. Fabulous. Once it is strutured correctly and securely, then activity analysis is very easy and costwise so cheap for a company. Even a small distributor below Rupees 10 lakhs turnover and having network of sales and distribution in more than two places , can use it without any additional cost for software.

I hereby given you the excel with forms – to create feedback through sales representative for proper distribution

1. First select – create new form

2. A separate window will open

3. Just add the following text / option box

a. Name of the customer

b. Invoice No

c. Date of receipt of goods

d. Whether reached before agreed delivery period

e. How is the service rate it

Once it is created, save it (it will save automatically in regular interval). The backend of the data will be stored in excel format.

The data can be entered by various people. For that you have to get the link of the webpage having this form and show it in your webpage. Once entry made, it will be stored in google excel.

It is quite useful.

See the final output form, in which the user has to enter details

Even you can email the form to the persons to whom you want reply. You can authorize the person email ids . This is helpful for security of your mishandling of the form.

In the excel you can fetch data and can analyse the same. We can also download the same in your computer as excel file.

Dear reader,

I have given the short brief for the understand basically. Hope you enjoyed and liked it. Kindly send your suggestions to vinodkumar2001

Vinothkumar.G

EXCEL2007 – HOW TO CREATE PIVOT TABLE

EXCEL2007 – HOW TO CREATE PIVOT TABLE

For describe the pivot table , I have given sample data. Just go through first

DISTRICT SALES REP NAME SALES
200 VIJAY 8805
201 RAVI 7226
202 ANAND 5268
203 ANAND 2884
203 SUJHAI 6056
203 KUMAR 3386
201 RAMESH 6980
201 ASHOK 3635
200 RAMESH 9878
202 SUJHAI 8482
203 VIJAY 3805
204 RAVI 4345
205 KUMAR 6430
206 SURESH 2980
210 ANAND 3215
210 SURESH 2458
209 ANAND 7471
208 RAMESH 3958
207 KUMAR 3280
206 SUJHAI 2850

While go through you found there are three columns namely DISTRICT, SALES REP NAME, SALES.

To find out who is making more sale, follow the steps

1. Select the data to analyse from A1:C21

2. Goto Insert menu, select Insert Pivot table

3. It will show a menu

In table range, it showing as Table1 as we already selected the range. We can change the data selection also.

Choose new worksheet, for convenient . You can use existing worksheet also, but you have to give the cell reference.

4. Then the screen shows

Just drag Sales Rep Name into Row labels and Sales into Values

Now, see the data are sorted and summed in the sheet. It gives data in meaningful way very beautifully.

See, if you want to check in which State having more sales, Then remove sales rep name and insert District into the Row labels.

Even, we can do, Districtwise, Sales rep wise as follows

If you drag the District into column labels, then it shows beautifully

This is very simple to use.

Readers,

Hope it is useful to you. I will post more in coming days. I suggest you to give comments on this article along with your requirements and suggestions to develop into good article.

Please email at vinodkumar2001 if you free.

Thanks

Vinothkumar