Question

Part A a) 40 marks A fumiture 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 (ksha 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 below (a loss from operations), the tax is zer (you will need to use the IF function here). 1. What is the change in net inccme resulting from a plan that would reduce the materials cost fraction to 20% per year but would increase labor costs to 16%? 2. What would be the change in net income if ixed overhead is doubled? 3. Generate a table showing a 5% monthly increase in gross sales over a period of five months March. Jaly 2020 Part B 20 marks b) Describe two examples of intelligent algorithms that can be used in DSS c) Describe the DSS taxonomy d) Describe the advantages and disadvantages of end user developed DSS

Answer 1

We have a furniture Company .Let us note down its figures

TABLE 1
Fields	Formula	Actual values
Gross Sales	Total sales	2250000
Returns and allowance	4% of Gross sales	90000
Net sales	Gross sales-Returns and allowance	2160000
MM&G Marketing, Management and general expense	18% of Net sales	388800
Labor	14% of Sales(assuming Net sales)	302400
Material	23% of sales	496800
Overhead(variable)	28%labour and material	223776
Cost of goods sold	Labor +material + overhead (variable + Fixed)together	1102976
Gross profit	Net sales - Cost of goods sold	1057024
Profit before Tax	Gross profit – MM&G	668224
VAT	27% of profit	180420.5
Taxes	3 % of profit	20046.72
Net Profit	Profit before tax – VAT and Taxes	467756.8

As per the data provided the values will be as mentioned in TABLE 1.

Answer 1 :

change in net income resulting from a plan that would reduce the materials cost fraction to 20 % per year and would increase labor cost to 16%.

Labour and material are a percentage of sales ,let us assume sale her eis net sales.

Changing the Labour from 14 % to 16% and reducing the Material cost to 20 %

Labour = (2160000*16)/100 =345600

Material cost =(2160000*20)/100=432000/-

In the previous scenario the

Cost of goods sold was 1102976/-

But in this new Scenario

Cost of goods Sold will be 1075328/-

Thus there is a reduction in the cost of goods sold which will result in an increased net profit.

The working result of the new scenario is shown in TABLE 2

TABLE 2
Fields	Formula	Actual values
Gross Sales	Total sales	2250000
Returns and allowance	4% of Gross sales	90000
Net sales	Gross sales-Returns and allowance	2160000
MM&G Marketing, Management and general expense	18% of Net sales	388800
Labor	16% of Sales(assuming Net sales)	345600
Material	20% of sales	432000
Overhead (variable)	28%labour and material	217728
Cost of goods sold	Labor +material + overhead (variable + Fixed)together	1075328
Gross profit	Net sales - Cost of goods sold	1084672
Profit before Tax	Gross profit – MM&G	695872
VAT	27% of profit	187885.4
Taxes	3 % of profit	20876.16
Net Profit	Profit before tax – VAT and Taxes	487110.4

The Increase in Net profit according to new scenario is 19353.6 kshs.

Answer 2:

Keeping all the values same and doubling the fixed overhead the case scenario changes as demonstrated in TABLE 3 below

TABLE 3
Fields	Formula	Actual values
Gross Sales	Total sales	2250000
Returns and allowance	4% of Gross sales	90000
Net sales	Gross sales-Returns and allowance	2160000
MM&G Marketing, Management and general expense	18% of Net sales	388800
Labor	14% of Sales(assuming Net sales)	302400
Material	23% of sales	496800
Overhead(variable)	28%labour and material	223776
Cost of goods sold	Labor +material + overhead (variable + Fixed)together 2 *80000 =160000	1182976
Gross profit	Net sales - Cost of goods sold	977024
Profit before Tax	Gross profit – MM&G	588224
VAT	27% of profit	158820.5
Taxes	3 % of profit	17646.72
Net Profit	Profit before tax – VAT and Taxes	411756.8

Increasing the overhead to double causes the company a reduction in net profit to 56000kshs.

Answer 3:

Generate a table showing a 5% monthly increase in gross sales over a period of five months (March –July 2020.

Upon increasing the monthly gross sales by 5 % every month the result will be as demonstrated below in TABLE 4

TABLE 4
Months	Gross sale	Returns and allowance	Net sale	MM&G	Labour	Material	Overhead	Cost of goods sold	Gross profit	Profit before Tax	VAT	Taxes	Net Profit
March	2250000	90000	2160000	388800	302400	496800	223776	1102976	1057024	668224	180420.5	20046.72	467756.8
April	2362500	94500	2268000	408240	317520	521640	234964.8	1154124.8	1113875.2	705635.2	190521.5	21169.06	493944.64
May	2480625	99225	2381400	428652	333396	547722	246713.04	1207831.04	1173568.96	744916.96	201127.6	22347.51	521441.872
June	2604656.25	104186.25	2500470	450084.6	350065.8	575108.1	259048.692	1264222.592	1236247.408	786162.808	212264	23584.88	550313.9656
July	2734889.063	109395.5625	2625493.5	472588.83	367569.09	603863.505	272001.1266	1323433.722	1302059.778	829470.9484	223957.2	24884.13	580629.6639

PART 2

Answer 1:

Two examples of intelligent algorithms that can be used in DSS :

• DSS stands for Decision support system. The prime purpose of the system is to identify and give outputs in intelligent ways as an expert human consultant analyzing the data, compiling the statistical and mathematical values.
• An excellent example of an intelligent DSS model is distance calculator in google map
• The distance calculator calculates the distance and time required to reach a destination taking into consideration different possible options such as traffic on the route, weather, road closures, toll plaza etc.
• An Expense and budget application is another simple application that analysis data mathematically and statistically and provide us user friendly visible results in the form of charts and percentages ,even though they handles same data.

Answer 2 :

DSS taxonomy :

DSS taxonomy analyses the data based on the environmental, economical, technical, mathematical and statistical bases and organizes them for meaningful intelligent decisions.

Answer 3 :

The advantage of DSS includes

• Quick and intense analysis
• Automation of an organization or company is possible
• Combine various aspects while arriving results

​​​​​​​​​​​​​​Disadvantages:

• Increase in unemployment
• Emotional quotient ignored
• Changes in the market not reflected like present case of Corona as affected all the industries but this cannot be incorporated in the system.