Question

A furniture company is producing and selling several products for grass sales (income) of 2.25 million shillings. Retums and allowances are 4 percent of gross sales, reducing the income to net sales. Marketing, management, and general expenses (MM&G Jare figured to be 18% of the net sales. Cost of goods sold is the sum of labor (14% of sales), materials (23% of sales) and overhead Overhead is figured to be variable overhead (28% of the combined cost of labor and matcrials) plus fixed overhead (kshs 30,000). Gross profit is the difference between net sales and cost of goods sold. To figure the profit before tax, MM&G expenses need to be subtracted from the gross profit Finally, there is the 27% VAT and 3% withholding taxes that need to be considered, but these are only assessed if there is indeed a profit; if the profit before tax is zero or be.ow (a loss from operations), the tax is zer (you will need to use the IF function here). 1. What is the change in net income resulting from a plan that would reduce the materials cost fraction to 20% per year hut would increase labor costs to 16%? 2. What would be the change in net income if fixed overhead is doubled? 3. Generate a table showing a 5% monthly increase in gross sales over a period of five months March- July 2020

use excel

Answer 1

Gross sales (G)= 2.25 million.

return and allowances = 4% of gross sales therefore, net sales (S) = 2.16 million (96%)

MM&G = 18% of net sales = 0.3888 million

Cost of goods(C) = labor cost (l = 14%S) + material (m= 23%S) + overhead (O).

overhead = vaariable + fixed = 28% of (l+m) + 80,000

where, l = 0.14*S = 0.3024 million and m = 0.4968 million.

Cost of goods = 0.3024+0.4968+ 0.28*(0.3024+0.4968)+ 0.08 = 1.102976 million.

Gross profit(GP) = S - C, and, P = GP-MM&G

and taxes only if P>0. i.e. T=0 if P<=0

taxes (T)= VAT (27%of P) + witholding tax (3% of P)

finally net profit (NP) = GP - MM&G - T(iff)

now,

GP = 2.16- 1.102976 = 1.057024 million2

P = 1.057024 - 0.3888 = 0.668224 million. (P>0)

NP = P - (27+3)%P = 70%P = 0.668224*0.7 = 0.4677568 million

NP =  0.4677568 million.

			Gross sales (G)	net sales(S=96%G)	MM&G=18%of S)	Cost of Goods©	Gross profit(GP)=S-C	Profit(P)=GP-MM&G	Taxes(T) if P>0	Net profit(NP) =P-T
	figures in millions					Labor l=14%S	material m= 23%S)	overhead(O)			VAT(27%P)	Witholding=3%P
conditions								variable=28% of (l+m)	fixed
0	Current operation	2.25	2.16	0.3888	0.3024	0.4968	0.223776	0.08	1.057024	0.668224	0.18042	0.020047	0.467757
1	l=16%S & m= 20%	2.25	2.16	0.3888	0.3456	0.432	0.217728	0.08	1.084672	0.695872	0.187885	0.020876	0.48711
2.1	fixed overhead doubled @1	2.25	2.16	0.3888	0.3456	0.432	0.217728	0.16	1.004672	0.615872	0.166285	0.018476	0.43111
2.2	fixed overhead doubled @0	2.25	2.16	0.3888	0.3024	0.4968	0.223776	0.16	0.977024	0.588224	0.15882	0.017647	0.411757
3.1@1	5% monthly increase in GS	march	2.3625	2.268	0.40824	0.36288	0.4536	0.228614	0.16	1.062906	0.654666	0.17676	0.01964	0.458266
	april	2.480625	2.3814	0.428652	0.381024	0.47628	0.240045	0.16	1.124051	0.695399	0.187758	0.020862	0.486779
	may	2.604656	2.50047	0.450085	0.400075	0.500094	0.252047	0.16	1.188253	0.738169	0.199306	0.022145	0.516718
	june	2.734889	2.6254935	0.472589	0.420079	0.5250987	0.26465	0.16	1.255666	0.783077	0.211431	0.023492	0.548154
	july	2.871634	2.756768175	0.496218	0.441083	0.551353635	0.277882	0.16	1.326449	0.830231	0.224162	0.024907	0.581162
3.2@0	5% monthly increase in GS	march	2.3625	2.268	0.40824	0.31752	0.52164	0.234965	0.08	1.113875	0.705635	0.190522	0.021169	0.493945
	april	2.480625	2.3814	0.428652	0.333396	0.547722	0.246713	0.08	1.173569	0.744917	0.201128	0.022348	0.521442
	may	2.604656	2.50047	0.450085	0.350066	0.5751081	0.259049	0.08	1.236247	0.786163	0.212264	0.023585	0.550314
	june	2.734889	2.6254935	0.472589	0.367569	0.603863505	0.272001	0.08	1.30206	0.829471	0.223957	0.024884	0.58063
	july	2.871634	2.756768175	0.496218	0.385948	0.63405668	0.285601	0.08	1.371163	0.874944	0.236235	0.026248	0.612461

figures in millions g=3%P conditi ons 0 Current operation 1 I=16%S &m= 20% 2.1 fixed overhead doubled @1 2.2 fixed overhead doubled @O 3.1@1 march april may 5% monthly june increase in GS july 3.2@o march april may 5% monthly june increase in GS july Gross Profit(P)= Net Gross net MM&G=1 profit(GP) GP- profit(NP) sales (G) sales(S=96%G) 8%of S) Cost of Goods =S-C MM&G Taxes(T) if P>0 =P-T Labor material m= VAT(27% Witholdin I=14% 23%) overhead(0) P) variables 28% of (1+m) fixed 2.25 2.16 0.3888 0.3024 0.4968 0.223776 0.08 1.057024 0.668224 0.18042 0.020047 0.467757 2.25 2.16 0.3888 0.3456 0.432 0.217728 0.08 1.084672 0.695872 0.187885 0.020876 0.48711 2.25 2.16 0.3888 0.3456 0.432 0.217728 0.16 1.004672 0.615872 0.166285 0.018476 0.43111 2.25 2.16 0.3888 0.3024 0.4968 0.223776 0.16 0.977024 0.588224 0.15882 0.017647 0.411757 2.3625 2.268 0.40824 0.36288 0.4536 0.228614 0.16 1.062906 0.654666 0.17676 0.01964 0.458266 2.480625 2.3814 0.428652 0.381024 0.47628 0.240045 0.16 1.124051 0.695399 0.187758 0.020862 0.486779 2.604656 2.50047 0.450085 0.400075 0.500094 0.252047 0.16| 1.188253 0.738169 0.199306 0.022145 0.516718 2.734889 2.6254935 0.472589 0.420079 0.5250987 0.26465 0.16 1.255666 0.783077 0.211431 0.023492 0.548154 2.871634 2.756768175 0.496218 0.441083 0.551353635 0.277882 0.16 1.326449 0.830231 0.224162 0.024907 0.581162 2.3625 2.268 0.40824 0.31752 0.52164 0.234965 0.08 1.113875 0.705635 0.190522 0.021169 0.493945 2.480625 2.3814 0.428652 0.333396 0.547722 0.246713 0.08 1.173569 0.744917 0.201128 0.022348 0.521442 2.604656 2.50047 0.450085 0.350066 0.5751081 0.259049 0.08 1.236247 0.786163 0.212264 0.023585 0.550314 2.734889 2.6254935 0.472589 0.367569 0.603863505 0.272001 0.08 1.30206 0.829471 0.223957 0.024884 0.58063 2.871634 2.756768175 0.496218 0.385948 0.63405668 0.285601 0.08 1.371163 0.874944 0.236235 0.026248 0.612461

ALL CONDITIONS ARE SOLVED. POST A COMMENT IF U HAVE A DOUBT. DO THUMB IT UP!!!!