Detailed Cash Budget Analysis Report for XYZ Ltd. Loan Application

This report presents a comprehensive analysis of the cash budget for XYZ Ltd., a retail company seeking a bank loan to expand its business. The report includes a detailed cash flow statement projecting cash inflows and outflows over a 12-month period. It provides information on estimated cash sales, collections from creditors, bank loans, and interest on investments. The report also breaks down the company's expenses, including payments to suppliers, operating expenses, and loan repayments. Additionally, it uses charts to illustrate the company's expenses and sources of income. The report explains the approach to designing a spreadsheet and the appropriateness of the formulas used. It also provides an interpretation of the financial data, concluding that the company's liquidity position will improve with business expansion, thus justifying the loan. Finally, the report includes references to relevant sources.

DHICT

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
TASK 1............................................................................................................................................1
Description about the company...................................................................................................1
Statement showing details regarding sale and expenses of business for 12 months for
supporting the loan request of the company................................................................................1
Chart showing expenses of the company.....................................................................................2
Chart showing estimated sources of income of the company.....................................................3
TASK 2............................................................................................................................................3
Explaining approach to design the spreadsheet and appropriateness of formula used for
designing spreadsheet..................................................................................................................3
CONCLUSION................................................................................................................................4
CONCLUSION................................................................................................................................4
REFERENCES................................................................................................................................5

INTRODUCTION
The present study shows cash budget of XYZ Ltd. which is a company relating to the
retail industry in order to get loan from bank. Further, it shows graphical representation of
various estimated expenses and income of the company. It also includes a spreadsheet showing
the use of various formulae along with benefits of these formulae.
TASK 1
Description about the company
XYZ Ltd. is a company performing its business in the retail industry. It is a well
established business that wants to expand its business activities. As per the estimation of
managers, by expanding business, XYZ Ltd, would be able to generate more profit from the
business activities. For the expansion purpose, it has taken a sum of 10000 from family and
friends. In addition, the company is in need of more funds amounting to 2500 for capital and
50000 for purchase of goods. For arrangement of such needs, the company wants to take loan
from bank.
Statement showing details regarding sale and expenses of business for 12 months for supporting
the loan request of the company
Statement showing estimated cash receipts and payments for 12 months
Particulars
Janu
ary
Febr
uary
Marc
h April May June July
Augu
st
Septe
mber
Octo
ber
Nove
mber
Dece
mber
Cash at the
beginning 7000
5195
0
5040
0
5105
0
5320
0
5625
0
5800
0
6235
0
6596
0
7228
0
8066
0
9072
0
estimated cash
inflows
Cash sales 6500 7800
1000
0
1150
0
1320
0
1200
0
1460
0
1500
0
1670
0
1753
0
1900
0
2100
0
collection from
creditors 1200 3000 3200 2400 3100 2700 3000 3300 3540 4500 5400 6000
Bank loan
5250
0 0 0 0 0 0 0 0 0 0 0 0
interest on
investment 0 300 300 400 400 500 600 600 800 900 900 900
Total cash
inflows
6720
0
6305
0
6390
0
6535
0
6990
0
7145
0
7620
0
8125
0
8700
0
9521
0
1059
60
1186
20
estimated cash
outflow
payment made to
suppliers 3000 3150 3400 3650 4000 4200 4600 4840 4970 5000 5240 5300
operating 4000 4250 3200 3250 3400 3000 4000 3200 3500 4300 4750 5000
1

expenses
repayment of loan
@ 10% 5250 5250 5250 5250 5250 5250 5250 5250 5250 5250 5250 5250
short term
investment 3000 0 1000 0 1000 1000 0 2000 1000 0 0 1200
Total cash
outflows
1525
0
1265
0
1285
0
1215
0
1365
0
1345
0
1385
0
1529
0
1472
0
1455
0
1524
0
1675
0
Net cash
available with the
company
5195
0
5040
0
5105
0
5320
0
5625
0
5800
0
6235
0
6596
0
7228
0
8066
0
9072
0
1018
70
Interpretation
From the above cash budget it can be seen that the liquidity position of XYZ Ltd. will
improve day by day through expansion of business. In addition, with improvement in liquidity,
the business' ability to repay the loan amount will also be improved. Therefore, bank can grant
loan to it as risk of repayment is low with company.
Chart showing expenses of the company
chart showing overall expense of company
Particulars
Janu
ary
Febr
uary
Marc
h April May June July
Augu
st
Septe
mber
Octo
ber
Nove
mber
Dece
mber
Payments to
suppliers 3000 3150 3400 3650 4000 4200 4600 4840 4970 5000 5240 5300
Direct labour
charges 3200 3350 3500 3750 3800 3700 4000 4300 4550 4760 4800 5000
selling and
distribution
expenses 3500 3650 3800 4000 4150 4300 4400 4680 4700 4950 5150 5200
Income tax 519 550 580 600 630 640 660 670 695 710 730 750
other operational
expenses 1200 1450 1600 1700 1950 2000 2100 2240 2450 2500 2620 2700
Total expenses
1141
9
1215
0
1288
0
1370
0
1453
0
1484
0
1576
0
1673
0
1736
5
1792
0
1854
0
1895
0
2

Chart showing estimated sources of income of the company
Statement showing current sources of income
Particulars
Janu
ary
Febr
uary
Marc
h April May June July
Augu
st
Septe
mber
Octo
ber
Nove
mber
Dece
mber
Sales
1050
0
1200
0
1350
0
1460
0
1500
0
1540
0
1660
0
1730
0
1790
0
1850
0
1920
0
1950
0
Bank loan 5250 0 0 0 0 0 0 0 0 0 0 0
interest on
investment 0 300 300 400 400 500 600 600 800 900 900 900
TASK 2
Explaining approach to design the spreadsheet and appropriateness of formula used for designing
spreadsheet
Spreadsheet is a tool that can be used for designing tables, charts, etc. Preparation of
statements using spreadsheet leads in doing calculations easily and accurately as well with the
help of computerised analogous.
In addition, for improving presentation of any imformation, it can be presented in the
form, of graphs and charts (Hopkins, 2017) . It also improves understandability of the
information that has been presented through charts.
3
Payments to suppliers
Direct labour charges
selling and distribution expenses
Income tax
other operational expenses
Total expenses
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
20000
January chart showing overall expense
of company February
March April
May June
July August
September October
November December
Illustration 1: Extimated expenses of XYZ Ltd.

In the above task, two types of charts are being used namely column charts and bar
charts. These charts shows clear presentation of each information shown. Furthermore, these are
easy to understand. Therefore, these has been chosen for presenting estimated expenses and
income of the company (Van Norman, Christ and Newell, 2017).
There are various formulae that can be used while using spreadsheet more effectively
such as:
Auto sum: It helps in totalling large numbers easily. This formula is being used to add the
numbers mentioned in columns above the cell.
Auto Average: This formula of excel spreadsheet is being used to taking average of a large
number of items. It helps in deriving average of a large number of items accurately.
Percentage: It is the formulae that is being used while using spreadsheet for determining the
percentage of value mentioned (Fadeel and et.al., 2015).
In this regard, it can be said that spreadsheet improves tha quality of presentation and
understandability of the information provided. Further, it also improves the probability accuracy
as well.
CONCLUSION
From the above study it can be said that for the purpose of taking bank loan, it is
necessity to show improved liquidity position of the company. For this purpose, company needs
to show estimated cash flow statement of the company so that bank could evaluate its risk
involved with granting loan to the company. In addition, it can be concluded that with the help of
spreadsheet quality of representation of any information can be improved along with improving
understandability of the information shown. Moreover, by using different formulae, each
calculation can be done more accurately and free from errors as well.
CONCLUSION
4

REFERENCES
Books and Journals
Hopkins, W.G., 2017. A Spreadsheet for Deriving a Confidence Interval, Mechanistic Inference
and Clinical Inference from a P Value. Sportscience, 21.
Van Norman, E.R., Christ, T.J. and Newell, K.W., 2017. Curriculum-based measurement of
reading progress monitoring: The importance of growth magnitude and goal setting in
decision making. School Psychology Review, 46(3), pp.320-328.
Fadeel, B. and et.al., 2015. Keeping it real: The importance of material characterization in
nanotoxicology. Biochemical and biophysical research communications, 468(3), pp.498-
503.
Online
[Online]. Available through : <>.
[Online]. Available through : <>.
5