Data Handling and Using ICT - Desklib

This article discusses data handling and using ICT with a focus on a third-party logistics company in the UK. It covers tasks such as AutoSum, Auto-Format, Average Formula, Percentage, and more. The article also includes charts and graphs for presentation of expenses, income sources, and distribution of investment. References and appendices are also provided.

0DATA HANDLING AND USING ICT
Data Handling and Using ICT
Name of Student:
Name of University:
Author’s Note:

1DATA HANDLING AND USING ICT
Table of Contents
Task 1a.............................................................................................................................................2
Task 1b.............................................................................................................................................2
Task 2...............................................................................................................................................3
References........................................................................................................................................5
List of Appendix..............................................................................................................................6

2DATA HANDLING AND USING ICT
Task 1a
The organisation selected is a third-party logistics company in the United Kingdom. The
main operations performed by the company is seen with perfuming various types of the
integrated solution across the UK. Some of the main functions performed by the company is seen
in terms of warehousing, Transportation services, Cross-docking, Inventory management,
Packaging and different types of freight forwarding services. The company will provide various
types of the value-added services which will consists of the functions such as “tracing, cross-
docking and specific packaging”.
Task 1b
AutoSum
Auto-Format
Average Formula
Percentage

3DATA HANDLING AND USING ICT
Chart for presentation of the expenses
Chart for presentation of the expenses
Premises (RENT & RATES) Staff Salary Executive Fees
Payroll Tax Retainer contracts Sales and Marketing
Postage & Telephone Brouchers Website Development Expenses
Logo Designs Interest on loan 10% Market survey
Preliminary expenses Lease payments
Chart for presentation of the income sources
Month
1
Month
2
Month
3
Month
4
Month
5
Month
6
Month
7
Month
8
Month
9
Month
10
Month
11
Month
12
0
200,000
400,000
600,000
800,000
1,000,000
1,200,000
1,400,000
Chart for presentation of the income sources
Retainer Consulting Project Consulting Market Consulting Projected Sales
Chart for distribution of investment between capital, stock and operation money
1 2 3 4 5 6 7 8 9 10 11 12
0
1,000
2,000
3,000
4,000
5,000
6,000
7,000
Chart for distribution of investment between capital, stock
and operation money
Capital Employed Premises (rent, rates) General expenses
Interest and bank charges payable Lease payments Cost of Sales Sold

4DATA HANDLING AND USING ICT
Task 2
The rationale for the auto sum formula has been seen with adding all the start-up
expenses to find the fixed cost. The different items are seen with the inclusion of “Staff Salaries,
Executive Salaries, Interest on loan 10%, Executive Fees, Payroll Tax, Retainer contracts, and
Website Development Expenses”. The average formula has been used with computing the
Average Monthly Expenses. This has been used to find the average monthly expenses by
including the items such as “Rent, Lease payments, Interest on loan 10%, Postage & Telephone
and Salaries of the Staff”. The percentage function is used to compute the net Profit percentage,
which is considered with net profit and sales. The pie chart for the presentation of the expenses
has been considered with premises, payroll tax, postage and telephones etc and their contribution
in the respective areas of the expenses. This has shown the relevant distribution and maximum
category of the expense. For instance, it can be clearly seen from the pie chart that maximum
contribution of the expense in executive fees and staff salaries (Jablonsky 2014). The
presentation of the sources of income is depicted with line graph. This graph has been conducive
in depicting the increasing trend of the income over 12 months. The bar chart has been
conducive in showing the relevant distribution of the investment between capital, stock and
operation money. For instance, from the visual representation from the bar graph it can be clearly
seen that the cost of sales is the highest for operation money (Donaubauer, J., Meyer, B.E. and
Nunnenkamp, P., 2016.

5DATA HANDLING AND USING ICT
References
Jablonsky, J., 2014. MS Excel based software support tools for decision problems with multiple
criteria. Procedia Economics and Finance, 12, pp.251-258.
Wang, L., Wang, G. and Alexander, C.A., 2015. Big data and visualization: methods, challenges
and technology progress. Digital Technologies, 1(1), pp.33-38.

6DATA HANDLING AND USING ICT
List of Appendix
Start-up Requirements
Start-
up
Expens
es
Fixed
Costs Particulars Amount
($)
Premises (RENT &
RATES) $1,600
Staff Salaries $50,000
Executive Salaries $60,000
Interest on loan 10% $250
Executive Fees $60,000
Payroll Tax $1,200
Retainer contracts $1,600
Sales and Marketing $1,400
Postage & Telephone $350
Brouchers $100
Website Development
Expenses $650
Logo Designs $300
Market survey $750
Preliminary expenses $150
Lease payments $800
Total Fixed Costs $179,150
Total
Yearly
Costs
Rent $1,600
Lease payments $800
Interest on loan 10% $250
Postage & Telephone $350
Maintainance $100
Salaries $110,000
Total Monthly Costs $113,100
Average Monthly
Expenses $18,850
x Number of Months: 12
Total Yearly Costs $1,357,20
0
Total Startup Expenses $1,536,35
0
Start-
up
Assets
Owner
Funding
Owners Fund $1,000
Total Owner Funding $1,000
Loans
Bank Loan $2,500
Other
Total Loans $2,500
Total Start up Funds $3,500
Assets
Land and Buildings $100,000
Vehicles $25,000
Computer $40,000
Total Fixed Assets $165,000
Total
Start-
up
Assets
$168,500

7DATA HANDLING AND USING ICT

8DATA HANDLING AND USING ICT
X-Axis Label Sales Gross
Margin Net Profit Net Profit Percentage
Month 1 $32,000 $95,105 ($63,105) -197%
Month 2 $44,800 $98,697 ($53,897) -120%
Month 3 $62,720 $104,429 ($41,709) -67%
Month 4 $87,808 $112,366 ($24,558) -28%
Month 5 $122,931 $123,167 ($235) 0%
Month 6 $172,104 $138,467 $33,637 20%
Month 7 $240,945 $159,757 $81,188 34%
Month 8 $337,323 $189,414 $147,910 44%
Month 9 $472,252 $230,761 $241,491 51%
Month 10 $661,153 $288,450 $372,704 56%
Month 11 $925,615 $368,985 $556,630 60%
Month 12 $1,295,861 $482,868 $812,993 63%

0DATA HANDLING AND USING ICT
(1) SALES FORECAST
Month 0 1 2 3 4 5 6 7 8 9 10 11 12
Projected Sales 32,000 44,800 62,720 87,808
122,93
1
172,10
4
240,94
5
337,32
3
472,25
2 661,153 925,615
1,295,8
61
(b) Cost of sales 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 6,000
(2) CASHFLOW FORECAST
Preop
Month 0 1 2 3 4 5 6 7 8 9 10 11 12
CASH INFLOWS
Cash from Sales 32,000 44,800 62,720 87,808
122,93
1
172,10
4
240,94
5
337,32
3
472,25
2 661,153 925,615
1,295,8
61
Directors loans 2,500 2,500 2,500 2,500 2,500 2,500 2,500 2,500 2,500 2,500 2,500 2,500 2,500
Capital Employed
100,00
0 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 500 500 500 500
Other cash inflows
TOTAL CASH INFLOW
102,50
0 35,500 48,300 66,220 91,308
126,43
1
175,60
4
244,44
5
340,82
3
475,25
2 664,153 928,615
1,298,8
61
CASH OUTFLOWS
Payments for materials 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 6,000
operating expenses ( ) 0
Premises (rent, rates) 0 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600
Staff salaries 0 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000
General expenses 0 300 300 300 300 0 0 0 0 0 0 0 0
Interest and bank charges payable 0 250 250 250 250 250 250 250 250 250 250 250 250

1DATA HANDLING AND USING ICT
Lease payments 0 800 824 849 874 5000 5001 5002 5003 5004 5005 5006 5007
Corporation Tax
-
27,045
-
23,099
-
17,875
-
10,525 -101 14,416 34,795 63,390
103,49
6 159,730 238,556 348,425
Market survey costs 0 750 758 765 773 780 788 796 804 812 820 828 837
Other preliminary expenses 0 150 165 182 200 220 242 266 292 322 354 389 428
capital expenditure
Plant and other capital expenditure 0 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000
financing repayments
Loan repayments 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000
TOTAL CASH OUTFLOWS 0 70,805 80,798 86,070 93,472
107,74
9
122,29
7
142,70
9
171,33
9
211,48
4 267,759 346,629 458,547
Cash flow summary
NET CASHFLOW FOR PERIOD
102,50
0
-
35,305
-
32,498
-
19,850 -2,164 18,682 53,307
101,73
6
169,48
4
263,76
9 396,394 581,986 840,314
OPENING CASH BALANCE 0
102,50
0 67,195 34,697 14,847 12,683 31,365 84,673
186,40
9
355,89
3 619,661
1,016,0
56
1,598,0
42
CLOSING CASH BALANCE
102,50
0 67,195 34,697 14,847 12,683 31,365 84,673
186,40
9
355,89
3
619,66
1
1,016,0
56
1,598,0
42
2,438,3
55
(3) DEPRECIATION SCHEDULE
Month 0 1 2 3 4 5 6 7 8 9 10 11 12
Fixed Assets
Furniture
10000
0 90,000 80,000 70,000 60,000 50,000 40,000 30,000 20,000 10,000 0 -10,000 -20,000
Vehicles 25000 20,000 16,000 12,800 10,240 8,192 6,554 5,243 4,194 3,355 2,684 2,147 1,718
Plant and Machinery 40000 32,000 25,600 20,480 16,384 13,107 10,486 8,389 6,711 5,369 4,295 3,436 2,749
Total book values (i.e. net fixed assets) 0
142,00
0
121,60
0
103,28
0 86,624 71,299 57,039 43,631 30,905 18,724 6,979 -4,417 -15,533

2DATA HANDLING AND USING ICT
Monthly Depreciation
Furniture-10% straight line 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000 10,000
Vehicles - 20% reducing balance 5,000 4,000 3,200 2,560 2,048 1,638 1,311 1,049 839 671 537 429
Plant & machinery-20% reducing balance 8,000 6,400 5,120 4,096 3,277 2,621 2,097 1,678 1,342 1,074 859 687
Total monthly depreciation 23,000 20,400 18,320 16,656 15,325 14,260 13,408 12,726 12,181 11,745 11,396 11,117
(4) PROFIT AND LOSS FORECAST
Preop
Month 0 1 2 3 4 5 6 7 8 9 10 11 12
Revenue 0 32,000 44,800 62,720 87,808
122,93
1
172,10
4
240,94
5
337,32
3
472,25
2 661,153 925,615
1,295,8
61
Cost of sales 0 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 6,000
Gross profit 0 28,000 40,800 58,720 83,808
118,93
1
168,10
4
236,94
5
333,32
3
468,25
2 657,153 921,615
1,289,8
61
Gross Margin 95,105 98,697
104,42
9
112,36
6
123,16
7
138,46
7
159,75
7
189,41
4
230,76
1 288,450 368,985 482,868
Expenses/overheads
Premises (rent, rates) 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600 1,600
Salaries 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000
General expenses 300 300 300 300 0 0 0 0 0 0 0 0
Executive Fees 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000
Payroll Tax 1,200 1,260 1,323 1,389 1,459 1,532 1,608 1,689 1,773 1,862 1,955 2,052
Utilities 1,600 1,680 1,764 1,852 1,945 2,042 2,144 2,251 2,364 2,482 2,606 2,737
Sales and Marketing 1,400 1,680 2,016 2,419 2,903 3,484 4,180 5,016 6,020 7,224 8,668 10,402
Postage & Telephone [phone] 386 394 402 410 418 427 435

3DATA HANDLING AND USING ICT
Repairs and Maintainance 100 103 106 109 113 116 119 123 127 130 134 138
Website Development Expenses 650 650 650 650 650 650 650 650 650 650 650 650
Preliminary expenses 150 165 182 200 220 242 266 292 322 354 389 428
Lease Payments 800 824 849 874 900 927 955 984 1,013 1,044 1,075 1,107
Total expenses/overheads
118,15
0
117,79
5
118,30
5
118,89
1
119,26
7
120,05
1
120,96
2
122,02
4
123,26
5 124,720 126,429 128,443
Profit before tax
-
90,150
-
76,995
-
59,585
-
35,083 -336 48,053
115,98
3
211,30
0
344,98
8 532,434 795,185
1,161,4
18
Tax @ 30%
-
27,045
-
23,099
-
17,875
-
10,525 -101 14,416 34,795 63,390
103,49
6 159,730 238,556 348,425
Profit after tax
-
63,105
-
53,897
-
41,709
-
24,558 -235 33,637 81,188
147,91
0
241,49
1 372,704 556,630 812,993
Transfer to reserves
-
90,150
-
76,995
-
59,585
-
35,083 -336 48,053
115,98
3
211,30
0
344,98
8 532,434 795,185
1,161,4
18
Balance Sheet
Assets Month
1 Month 2 Month
3
Month
4
Month
5
Month
6
Month
7 Month 8 Month 9 Month 10 Month 11 Month 12
Current
Assets
Cash $106,0
50 $137,050 $124,6
50
$110,9
00
$116,6
50
$116,6
51
$116,6
52 $116,653 $116,654 $116,655 $116,656 $116,657
Accounts
receivable
$32,00
0 $44,800 $62,72
0
$87,80
8
$122,9
31
$172,1
04
$240,9
45 $337,323 $472,252 $661,153 $925,615 $1,295,861
Total current
assets
$138,0
50 $181,850 $187,3
70
$198,7
08
$239,5
81
$288,7
55
$357,5
97 $453,976 $588,906 $777,808 $1,042,271 $1,412,518
Fixed (Long-
Term) Assets

4DATA HANDLING AND USING ICT
Vehichels $25,00
0 $80,000 $70,00
0
$60,00
0
$50,00
0
$40,00
0
$30,00
0 $20,000 $10,000 $0 -$10,000 -$20,000
Furniture $100,0
00 $16,000 $12,80
0
$10,24
0 $8,192 $6,554 $5,243 $4,194 $3,355 $2,684 $2,147 $1,718
Equipment $40,00
0 $25,600 $20,48
0
$16,38
4
$13,10
7
$10,48
6 $8,389 $6,711 $5,369 $4,295 $3,436 $2,749
(Less
accumulated
depreciation)
$23,00
0 $20,400 $18,32
0
$16,65
6
$15,32
5
$14,26
0
$13,40
8 $12,726 $12,181 $11,745 $11,396 $11,117
Intangible
assets
$50,70
0
Total fixed
assets
$192,7
00 $101,200 $84,96
0
$69,96
8
$55,97
4
$42,78
0
$30,22
4 $18,179 $6,543 -$4,766 -$15,812 -$26,650
Total Assets $330,7
50 $283,050 $272,3
30
$268,6
76
$295,5
56
$331,5
34
$387,8
21 $472,155 $595,450 $773,043 $1,026,458 $1,385,868
Liabilities and
Owner's Equity
Current
Liabilities
Accounts
payable
$24,00
0 $25,000 $27,00
0
$25,00
0
$27,00
0
$27,00
1
$27,00
2 $27,003 $27,004 $27,005 $27,006 $27,007
Accrued Rent $1,600 $1,600 $1,600 $1,600 $1,600 $1,600 $1,600 $1,600 $1,600 $1,600 $1,600 $1,600
Bank Charges
Payable $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000
Short-term
loans
$10,00
0 $10,000 $10,00
0
$10,00
0
$10,00
0
$10,00
0
$10,00
0 $10,000 $10,000 $10,000 $10,000 $10,000

5DATA HANDLING AND USING ICT
Income taxes
payable
-
$27,04
5
-$23,099
-
$17,87
5
-
$10,52
5
-$101 $14,41
6
$34,79
5 $63,390 $103,496 $159,730 $238,556 $348,425
Accrued
salaries and
wages
$50,00
0 $50,000 $50,00
0
$50,00
0
$50,00
0
$50,00
0
$50,00
0 $50,000 $50,000 $50,000 $50,000 $50,000
General
Expenses $300 $300 $300 $300 $0 $0 $0 $0 $0 $0 $0 $0
Lease Payment $800 $824 $849 $874 $900 $927 $955 $984 $1,013 $1,044 $1,075 $1,107
Current portion
of long-term
debt
$150,0
00 $140,000 $130,0
00
$140,0
00
$130,0
00
$130,0
01
$130,0
02 $130,003 $130,004 $130,005 $130,006 $130,007
Total current
liabilities
$212,6
55 $207,626 $204,8
73
$220,2
49
$222,4
00
$236,9
45
$257,3
54 $285,980 $326,118 $382,384 $461,243 $571,147
Long-Term
Liabilities
Long-term debt $2,500 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $60,007
Less: Loan
Repayment $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000 $6,000
Deferred
income tax $1,000 $0 $176,2
90
$109,9
82
$84,94
6
$84,94
7
$84,94
8 $84,949 $84,950 $84,951 $84,952 $84,953
Total long-
term liabilities $3,500 -$6,000 $170,2
90
$103,9
82
$78,94
6
$78,94
7
$78,94
8 $78,949 $78,950 $78,951 $78,952 $138,960
Owner's
Equity
Owner's
investment $1,000 $1,000 $1,000 $1,000 $1,000 $1,000 $1,000 $1,000 $500 $500 $500 $500
Net Profits -
$63,10
-$53,897 -
$41,70
-
$24,55
-$235 $33,63
7
$81,18
8
$147,910 $241,491 $372,704 $556,630 $812,993

6DATA HANDLING AND USING ICT
5 9 8
Reserve and
Surplus
-
$90,15
0
-$76,995
-
$59,58
5
-
$35,08
3
-$336 $48,05
3
$115,9
83 $211,300 $344,988 $532,434 $795,185 $1,161,418
Total owner's
equity
-
$152,2
55
-$129,892
-
$100,2
94
-
$58,64
1
$428 $82,68
9
$198,1
71 $360,209 $586,979 $905,637 $1,352,315 $1,974,911
Total
Liabilities and
Owner's
Equity
$63,90
0 $71,734 $274,8
69
$265,5
90
$301,7
74
$398,5
81
$534,4
74 $725,138 $992,047 $1,366,972 $1,892,510 $2,685,018
{42}
Common
Financial
Ratios
Debt Ratio
(Total
Liabilities /
Total Assets)
0.65 0.71
Current Ratio
(Current Assets
/ Current
Liabilities)
0.65 0.88
Assets-to-
Equity Ratio
(Total Assets /
Owner's
Equity)
-2.17 -2.18
Debt-to-
Equity Ratio
(Total
Liabilities /
-1.42 -1.55

7DATA HANDLING AND USING ICT
Owner's
Equity)