KASNEB Business Data Analytics - Question 22

Matrix Limited has provided the following data for the six months ended 31 December 2023: MonthRevenueCost of SalesGross ProfitOperating ExpensesSh.'mil... [Click to view full question and step-by-step solution]

CPA Business Data Analytics April 2024 Past Paper with Answers (PDF)

Q: KASNEB Business Data Analytics - Question 10

You are given the following statements about data mining:1. Descriptive data mining is a type of analysis that extracts data that may help determine an outcom... [Click to view full question and step-by-step solution]

1

Emma Atieno would like to determine the number of times revenues have exceeded Sh.12 million over the past 15 years. If the revenues are listed vertically in column A (From Cell A2 to Cell A16) of Excel, which of the following formulas will provide the correct output?

A. COUNTIF(A2:A11,“=12”)

B. COUNTIF(A2:A11,“>12”)

C. COUNTIF(A2:J11, “>12”)

D. COUNTIF(“>12”, A2:A11)

	2019	2020	2021	2022	2023
	Sh."million"	Sh."million"	Sh."million"	Sh."million"	Sh."million"
Total Revenue	250,283	262,556	264,027	298,078	310,905
Other Incomes	10	-	-	-	-
Direct Costs	(72,399)	(75,285)	(80,853)	(91,468)	(92,232)
Other Expenses	(53,590)	(49,229)	(49,045)	(57,548)	(78,810)
EBITDA (Earnings Before Interest, Tax, Depreciation and Armotisation)	124,304	138,042	134,129	149,062	139,863
Depreciation and Amortisation	(35,332)	(36,548)	(37,964)	(39,933)	(54,866)
	88,972	101,494	96,165	109,129	84,997
Share of Profit in associate and Joint Venture	5	3,357	(507)	(476)	(38)
Finance income	2,760	3,519	2,198	2,413	14,548
EBIT (Earnings Before Interest and Tax)	91,737	108,370	97,856	111,066	99,507
Finance costs	(520)	(2,597)	(4,221)	(8,853)	(11,162)
Profit before Tax	91,218	105,773	93,636	102,213	88,345
Income Tax Expense	(28,727)	(32,115)	(24,959)	(34,717)	(35,862)
Profit After Tax	62,491	73,658	68,676	67,496	52,482

Statement of Financial Position as at 31 March:
	2019	2020	2021	2022	2023
Assets:	Sh."million"	Sh."million"	Sh."million"	Sh."million"	Sh."million"
Non-current assets:
Property, plant and Equipment	125,217.80	129,337.20	133,833.70	148,993.00	221,987.60
Intangible assets	7,385.40	6,026.20	8,475.50	93,647.20	150,198.80
Right-of-Use Assets	3,609.90	18,495.00	17,608.40	93,647.20	39,168.50
Investment Property	845.00	845.00	845.00	845.00	935.00
Investment in Associate Company	150.30	4,965.10	4,458.20	3,982.10	3,943.90
Restricted Cash	2,741.00	2,747.80	2,540.70	2,167.80	1,759.60
Contract Assets	964.50	881.70	1,491.20	1,138.70	3,441.10
Deferred Tax asset	1,602.90	1,104.70	5,467.20	9,908.80	15,337.00
	142,516.80	164,402.70	174,719.90	354,329.80	436,771.50
Current assets:
Inventories	1,774.60	1,859.40	2,487.00	4,306.80	3,655.60
Receivables and Prepayments	18,126.30	17,190.30	23,635.70	27,204.20	40,791.50
Contract Assets	1,984.60	2,563.80	3,043.40	2,951.50	4,395.00
Tax recoverable	-	260.40	7.20	7.90	851.60
Financial Assets	8,043.00	188.60	-	-	28.60
Cash and cash equivalents	20,030.10	26,759.70	26,736.10	30,779.60	22,713.20
	49,958.60	48,822.20	55,909.40	65,250.00	72,435.50
Total Assets	192,475.40	213,224.90	230,629.30	419,579.80	509,207.00
Capital:
Capital and reserves:
Share capital	2,003.30	2,003.30	2,003.30	2,003.30	2,003.30
Share Premium	2,200.00	2,200.00	2,200.00	2,200.00	2,200.00
Translation Reserve	-	-	-	-5,312.70	36,688.40
Retained Earnings and Dividends	140,141.30	138,876.80	133,432.00	140,578.00	146,664.20
Total Equity to Parent	144,344.60	143,080.10	137,635.00	139,468.30	187,555.90
Total Equity to Non-controlling interest	-	-	-	40,232.30	75,810.00
Total Equity	144,344.60	143,080.10	137,635.00	179,700.60	263,365.90
Liabilities:
Non-current liabilities:
Borrowings	-	-	-	44,910.80	42,050.00
Payables and Accrued Expenses	1,131.00	985.40	-	4,330.60	27,359.30
Provisions	-	-	3,151.40	3,183.30	4,462.80
Contract Liability	739.50	983.40	2,436.10	1,937.50	1,607.80
Lease Liability	-	11,675.30	11,954.20	14,584.90	29,984.00
	1,870.50	13,644.10	17,541.70	68,947.10	105,463.90
Current liabilities:
Payables and Accrued Expenses	28,703.90	29,920.10	34,021.40	41,312.60	72,688.20
Borrowings	4,032.00	8,000.00	14,772.00	20,400.00	43,492.30
Dividends Payable	-	1,045.10	8,684.10	12,053.90	1,783.00
Provisions	3,893.50	4,462.30	2,561.50	3,373.80	4,524.10
Contract Liability	9,279.60	9,410.90	11,033.50	10,210.60	10,125.90
Lease Liability	-	3,549.40	4,119.50	5,508.50	5,354.90
Tax Payable	351.30	112.90	260.30	5,291.20	2,408.80
	46,260.30	56,500.70	75,452.30	98,150.60	140,377.20
Total Liabilities	48,130.80	70,144.80	92,994.00	167,097.70	245,841.10
Total Capital and Liabilities	192,475.40	213,224.90	230,629.00	346,798.30	509,207.00

	2019	2020	2021	2022	2023
Cash flows from Operating activities (Sh."million")	99,812	110,363	105,197	110,701	115,151
Number of Shares (million)	40,065	40,065	40,065	40,065	40,065
Market Price per share (Sh.)	28.00	27.00	37.00	35.00	18.10
Dividends Paid (Sh."million")	44,071.00	73,877.30	66,482.00	59,132.30	63,557.70

Key Metrics	2019	2020	2021	2022	2023
Total Assets Turnover	1.30	1.23	1.14	0.86	0.61
Net Profit Margin	50.27%	53.36%	51.20%	45.28%	37.52%
Return on Investment	32.47%	34.54%	29.78%	19.46%	10.31%
Return on Equity	43.29%	51.48%	49.90%	37.56%	19.93%
Current Ratio	1.08	0.86	0.74	0.66	0.52
Debt Ratio	25.01%	32.90%	40.32%	48.18%	48.28%
Earnings Per Share (Sh.)	1.56	1.84	1.71	1.68	1.31
Dividend Per share(Sh.)	1.10	1.84	1.66	1.48	1.59
Book Value Per Share (Sh.)	3.60	3.57	3.44	3.48	4.68

Month	Revenue	Cost of Sales	Gross Profit	Operating Expenses
	Sh."million"	Sh."million"	Sh."million"	Sh."million"
Jul-23	3,430	2,400	1,030	310
Aug-23	3,780	2,830	950	280
Sep-23	3,200	2,300	900	270
Oct-23	3,650	2,740	910	275
Nov-23	3,920	2,900	1,020	305
Dec-23	4,100	3,100	1,000	300

Month	EBITDA	Depreciation and Amortisation	EBIT	Interest	EBT
	Sh."million"	Sh."million"	Sh."million"	Sh."million"	Sh."million"
Jul-23	720	290	430	120	310
Aug-23	670	270	400	115	285
Sep-23	630	250	380	112	268
Oct-23	635	250	385	110	275
Nov-23	715	280	435	98	337
Dec-23	700	290	410	95	315

End Month	Inventory	Receivables	Payables	Cash/Bank Balances
	Sh."million"	Sh."million"	Sh."million"	Sh."million"
Jul-23	980	1,200	720	150
Aug-23	1,020	1,230	800	230
Sep-23	960	1,190	950	180
Oct-23	910	1,320	1,000	90
Nov-23	1,030	1,280	850	100
Dec-23	1,000	1,340	900	80

Required:
(a) Summarise the above data with the following additional sections for the Chief Finance Officer (CFO) Dashboard.

(i) Total profit or loss for the six months.

(ii) Various profit margins.

(iii) Days of Purchases (DOP) using end of year balances (Ignore averages).

(iv) Days of Inventory (DOI) using end of year balances (Ignore averages).

(v) Days of Sales (DOS) using end of year balances (Ignore averages).

Assume 360 days in a year and that inventory at the beginning of July was worth Sh.900 million.

(b) Prepare the CFO's dashboard showing the following charts:
(i) Revenue and profit.

(ii) Profit margin.

(iii) Payables levels.

(iv) Inventory levels.

(v) Receivables levels.

(vi) Number of days sales outstanding (DSO) and number of days payables outstanding (DPO).

	Sh.
Selling price per unit	10,000
Variable cost per unit	5,000
Total fixed costs	50,000,000

1.	Miriam opened a separate bank account for the practice and deposited Sh.5 million as capital.In addition, she obtained a loan from the bank amounting to Sh.5 million at an interest rate of 12% annually. The loan will be paid over a 5 year period at Sh.1 million annually, together with interest due at the end of each year.
2.	Miriam imported three dental equipment at a price of Sh.600,000 each and incurred freight, insurance and port clearance costs of Sh.200,000. The equipment is depreciated over five years.
3.	Miriam also bought other equipment for Sh.1 million, furniture and fixtures for Sh.2 million and dental books worth Sh.500,000. Both the equipment and furniture were to be depreciated over 8 years, while the dental books have a useful life of 5 years for the purpose of amortisation.
4.	Miriam hired a dental assistant, who also doubled as the clinic administrator at Sh.60,000 monthly salary, a receptionist, carrying out other clerical duties at Sh.30,000 per month and a cleaner/casual at Sh.15,000 per month. They all began employment on 1 January 2023.
5.	Miriam also hired the services of two other dentists to serve the increased number of patients or when she was visiting another hospital for consultation. During the year, the two dentists were paid for professional services at Sh.2 million. In addition, Miriam offered some interns learning opportunities and paid them a total stipend of Sh.300,000.
6.	During the year, patients paid Sh.6 million in cash, while those on various insurance and medical covers were treated at a total of Sh.4 million. By the end of the year, the corporates and insurance companies had paid Sh.2.8 million directly into the business bank account. Miriam also noted that a client owing Sh.200,000 became bankrupt and would likely not pay, while the balance had nearly 0% probability of default.
7.	The cash that was received was banked but after paying for the following items: ● Surgical and prescription medicine Sh.500,000. ● Stationery Sh.40,000. ● Cleaning consumables Sh.20,000. Cash drawings for personal use by Miriam averaged Sh.40,000 per month. By the end of the year, only Sh.120,000 was available in the office safe.
8.	Majority of transactions took place in the bank, except for the one highlighted in note 7. Miriam placed some amount in a fixed deposit account and earned interest of Sh.120,000. Other expenses were mainly salaries to staff, interest on loan, rent of Sh.100,000 per month, electricity and water worth Sh.60,000 for 11 months to 30 November 2023. She also paid Sh.30,000 as subscriptions to the dental association. Note that Miriam had paid 3 months' deposit on rent, which was refundable upon the termination of the lease. Miriam also drew Sh.60,000 every month for personal expenses from the bank. Meanwhile, the electricity and water bills for December 2023 had not been paid, but were subsequently received and settled in January 2024 at Sh.4,500.
9.	Miriam bought surgical and prescription medicine worth Sh.1.5 million on credit. By the end of the year, she had already paid Sh.800,000 of the amount owing while surgical and prescription medicine worth Sh.400,000 was available by the end of the year.

CPA Business Data Analytics – April 2024 Past Paper & Answers

Download Complete Period

Questions

Other Business Data Analytics Past Papers

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?

Want to join the discussion?