Formula suggestion

[zaska]

Hello,

I am using the formula " =D6-SUM(B2:B4) " in the below instance to find out profit . I kindly request to suggest a good alternative to find out the net profit / net loss .


<table border="0" cellpadding="0" cellspacing="0" width="286"><col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="width:48pt" span="3" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:71pt;font-size: 11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="94">Particulars</td> <td class="xl67" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">Debit</td> <td class="xl67" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">Particulars2</td> <td class="xl68" style="border-left:none;width:48pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#4F81BD;mso-pattern:#4F81BD none" width="64">Credit</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20">Opening Stock</td> <td class="xl63" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">5000</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">Sales</td> <td class="xl65" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">10000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">Purchases</td> <td class="xl63" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">8000</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext">Interest</td> <td class="xl65" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">5000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> </td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none">Closing stock</td> <td class="xl65" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">3000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" height="20">Profit</td> <td class="xl63" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext" align="right">5000</td> <td class="xl63" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> <td class="xl65" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="20"> </td> <td class="xl70" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">18000</td> <td class="xl70" style="border-top:none;border-left:none;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none"> </td> <td class="xl71" style="border-top:none;border-left:none; font-size:11.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#DCE6F1;mso-pattern:#DCE6F1 none" align="right">18000</td> </tr> </tbody></table>

 

[pedro-egoli]

The calculation of Net Profit/Loss is calculated as follows

 Sales Less Cost of Good sold  gives Gross Profit. From Gross Profit deduct Expenses to give Net Profit /Loss

From figures in your post it is a Net LOSS of $5000 and the following shows the steps to that using above calculation and also a formula in E9 which covers it in one step.

Excel Workbook
	A	B	C	D	E
2	Opening stock	$5,000	*	Sales	$10,000
3	Purchases	$8,000	*	Less *COGS	-$10,000
4	Closing stock	$3,000	*	Gross profit	$0
5	Cost of goods sold	$10,000	*	Less Interest	-$5,000
6	(COGS)	*	*	Gives Net LOSS	-$5,000
7	Expenses	*	*	*	*
8	Interest	$5,000	*	Net Profit/Loss	-$5,000
Sheet1

Pedro