Comprehensive Financial Analysis: Excel Coursework Report

This report presents a financial analysis based on an Excel coursework assignment. It covers key aspects of financial statement analysis, including income statements and cash flow statements, along with the assumptions used in forecasting. The report examines revenue and expenditure forecasts, overhead costs, distribution costs, and dividend payments. It differentiates between cash flow statements and income statements, highlighting their distinct purposes and the information they convey. The analysis includes a summary of the coursework, emphasizing the skills gained in Excel and the assumptions made in the financial model. The report also offers insights into how the company can improve its cash flow management. Overall, the report provides a detailed overview of financial forecasting and analysis techniques using Excel, offering a practical application of financial principles.

`
EXCEL COURSEWORK

TABLE OF CONTENTS
TABLE OF CONTENTS................................................................................................................2
REPORT..........................................................................................................................................1
a) Covered in excel......................................................................................................................1
b) Covered in excel......................................................................................................................1
c) Assumptions Regarding the Income Statement.......................................................................1
d) Assumptions related with the Cash flow statement.................................................................1
e) Difference between cash flow statements and income statements..........................................2
f) Summary of the individual coursework...................................................................................4
REFERENCES................................................................................................................................6

REPORT
a) Covered in excel
b) Covered in excel
c) Assumptions Regarding the Income Statement
The revenues and expenditure forecast in order to determine the sustainability of business is
the key action performed by financial experts in an organisation (Yoo and Pae, 2013). Here, in
the given case of forecast also consist of forecasting the income statement for upcoming five
years considering all the relevant changes.
Overhead costs are including to manufacturing and other operating expenses. In the
projected income statements all the expenses are inflated using the given inflation rates.
Manufacturing overhead costs include the utility expenses that are related to the manufacturing
concerns. They are used as per the production level of the company as per the respective years.
They are reflected with the increase in prices for every year.
Other expenses included the overhead are depreciation, auditors fees and the lease of the
premises. All these costs are included in the overhead. It is assumed that lease cost is related to
the factory therefore represented under overhead costs (Tobback and et. al., 2014). Distribution
costs are calculated as per the country. Dividend payments are made on the basis of operating
profit of previous year. It is paid at the constant percentage during the five year periods that is
five percent.
Cost of sales is calculated considering the materials consumed for the sold output plus
closing stock. This reflects the total materials for production during the year(Fawzi, Kamaluddin,
and Sanusi, 2015).. Retained are the earning are the earnings left after payment of dividends. It
has been assumed that the funds in retained earnings have not been used for any other purpose
and are accumulated for future expansions. Any of the capital expenditures have not been made
in the five years from the retained earnings.
Income statements of the company show that the business is incurring expenses for the
period. The rent, water, power and the audit fees are the expenses are required to the business.
The performance of the company is efficient and is showing effective growth of the business
1

during all the years. The sales of the company are showing considerable growth in the business.
Company is paying dividend at the rate of 2% on the profits. The remaining amount of profits
after the payment of dividends. The remuneration to the director is paid every year by the
company. The operating expenses of the company will be increasing at prescribed percentage of
the last year’s expense.
d) Assumptions related with the Cash flow statement.
Cash flow statement refers to the statement reflecting the summary of all the cash
transactions carried out during the year. This contains information regarding all the cash inflows
and outflows of the business. Cash flow statement of the company has been prepared on number
of assumptions.
It is reflecting three heads that are variable expenses, fixed expenses and the other
expenses. Variable expenses are the expenses that are change with the levels and the fixed costs
do not change with the change in units.
Variable expenses are the raw materials that are changing with the change in production.
Wages are also related to the manufacturing expenses as wages are paid on hourly basis. Wages
are calculated on the average basis for the whole year as considered in the financial statements.
Other variable overhead expenses are the utility expenses that are directly connected with the
manufacturing of the products. Distribution expenses are variable expenses but not covered
under the manufacturing costs. It is operating expense.
Fixed expenses for the year are considered as salary paid to the admin employees. Audit
fees is also considered as the fixed as it required to be paid every year and is not dependent on
the number of units (Podsiadło and Rybinski, 2015). Remuneration paid to the directors are also
assumed to be fixed costs as the remuneration is paid in the form of salaries to the directors on
monthly basis.
Other expense includes the dividend payment paid at fixed percentage of the profits. This
is neither fixed nor is variable paid when there is profit. Lease rentals are paid at fixed rate at
quarterly basis without any change. First payment for the new lease is due in October as paid in
advance. Company also made purchased fixed assets that are paid in instalments at the year end.
All the expenses are reported considering the inflations for the year. No extra costs are
covered. Depreciation is a non cash item therefore not reported in cash flow statement.
2

e) Difference between cash flow statements and income statements
Cash flow statement in turn is considered to be one of the most effective measure which
helps in predicting the cash inflows and outflows of the cash (Miao, Teoh and Zhu, 2016). It is
considered to be very useful in evaluating how cash has been received from the business in order
to carry out ongoing projections and also from the internal external sources. The cash flow
statement in turn tends to include only outflows and cash inflows. However, they do not tend to
include transactions which in turn largely influence the cash payments and recipients. However,
the cost transactions mainly comprise of write offs, depreciation, credit losses, debts, etc. The
cash flow statement comprises of sales, receipts and payments (Balance and et.al., 2015).
Collection from Customer is considered to be as receipt Which in turn has been increasing every
month from Euro 5,83,390 to Euro 6,36,399 in December. The total of cash collection from
customs for the year 2020 is Euro 94,92,977. The list of items included in cash payments mainly
comprises of payment to suppliers, power, wages, water, waste disposable, fixed expenses,
distribution cost, salaries, audit fees, dividend paid, least of assets, purchase of non-current
assets, directors demonstration, et cetera. The total payment for the year 2020 is 84,87,234
euros. However, the company is a profit of euros 10,80,743.
Cash flow statement covers only the expenses for which the cash has been paid or the
ouflow of cash has been made. Income statement covers all the expenses that are incurred by the
organisation whether monetary and non monetary. Cash flow statements does not accounts for
profits or losses that arises due to the change in fair value of assets. Changes that are occurred on
revaluation of assets are not recorded in cash flow statement. Cash flow statement records the
capital expenditure in the month or year it is occurred with full payment where capital
expenditures in the profit or loss are deferred over the lifetime of the assets in the form of
depreciation. Profit or loss statement also provides for the outstanding and prepaid expenses for
the year when they become due in the cash flow statement are recorded only when they are paid.
Similarly the sales are shown in cash flow statements only when the collections are made from
debtors where on the other it is booked as income in income statement as and when the goods are
invoiced.
On the contrary, Income statement is considered to be one of the core financial statement
of the organisation which tends to show profit and loss for the particular period. It helps in
indicating how the set of revenues are transformed into the specific net profit. The profit and loss
3

statements health and showing the ability of the company to generate sales, create profit and
manage their expenses over the particular period of time (Foerster, Tsagarelis, and Wang, 2017).
There are certain set of list of items which are included within the income statement which
includes revenue, cost of goods sold, sales, multiple range of revenue streams, gross profit,
advertisement, marketing, promotion expenses, general and administrative expenses, earnings
before interest, tax, amortisation and depreciation.
Income statement also comprises of operating income various other expenses such as
technology, research and development, impairment charges, gain or losses from sale of
investment, stock based compensation, Impact due to foreign exchange, various of the expenses
related with industry or company. Pre-tax income, income taxes, net income et cetera considered
to be the key elements and items of their income and profit statement This is very useful in
predicting the operations and position of the organisation. The projected income statement of the
company is 8400000 in the year 2019 and the turnover in turn has continuously increasing year
after year. It has increased to 96,42,411.492 in 2020, 99,70,325.1 in 2021 to 110,25,327.4 in the
year 2024. The Cost of sales of the company has been increasing every year but at a slower pace
i.e., from 50,94,000 in the year 2019 to 61,98,980.34 in the year 2024. The Gross profit of the
company has increased relevant year after year from 33,06,000 in the year 2019 to 4826347 in
the year 2024. The operating profit of the company in turn has increased significantly from
56,000 in the year 2019 to 815052 in the year 2024.
Company can improve its cash flows for the business by changing the lease payments
schedule. It can make payments for the lease on the monthly basis. This will improve the cash
flows of business. Company should ensure that there is not wide gap between the collection from
the customers and the payments to supplier it creates more cash requirements for the business
affecting the cash cycle. The cash flows are the life blood of the business because of which the
business are required to make proper strategies for ensuring that their effective inflows of the
money. This is essential for the business for carrying out the operations for the growth and
success of business.
The cash flow statement in turn is considered to be very useful in showing the exact
number associated with the cash outflows and inflows of the company (Nwanyanwu, 2015).
However, the income statement is very considerate in showing the total expenses and revenues
associated with the organization. The key parts associated with the cash flow statements are cash
4

flow from operations, investment and financing. However, the income statement in turn is very
beneficial in the determining the income and expenses of the organization. The key overhead
expenses for the period includes lease and salaries, utility expenses includes water, power, waste
disposal, audit fee, remuneration to directors and depreciation. The cost of sales comprises of
raw material and wages. Hence, both of these statements tends to have different use for the
organization and depicts different results because of varied items included in both the financial
statements.
In order to carry out the functions effectively it is very crucial for the company to
simplify and streamlining process. The company must in turn also tend to focus on setting
benchmark in order to measure the performance of the company (Fawzi, Kamaluddin, and
Sanusi, 2015). Resolving issues and finding the sources for increasing the revenue of the
company in turn is considered to be very prominent. The management of the company must in
turn also focus on preparing the specific budget sets which in turn eventually helps in effectively
carrying the business operations.
f) Summary of the individual coursework
The ABC is highly responsible for effectively carrying out the coursework. My phone
number is XXXXXXXXX. The researcher of the study will focus on preparing complete set of
Excel coursework which in turn is considered to be very useful the prediction of the financial
statements. I have also improved my Excel skills which helps me in predicting various set of the
data. I have made certain set of assumption within the specific model which clearly helps in
bifurcating the income and expenses of the company. I have gained wide degree of knowledge
which eventually helps in further carrying out the study with utmost accuracy and efficiency. I
have also carried out the cash flow statement which in turn is very useful in improving my
financial skills. I have carried out projections for five year which in turn has helped me in
understanding the cash inflow and cash outflow of each specific period. Moreover, this study in
turn has eventually helped me in effectively summarizing with the key set of findings which is
useful in gaining wider set of the knowledge. I have also contributed it to the profit projection
which helps me completely analysing the specific set of working of the organization.
Moreover, this study in turn is considered to be very useful because it helped me develop my
own set of skills associated with cell formatting, data validation, sorting of the data, pivot tables
and various other Excel shortcut keys which eventually leads to higher performance of the work
5

and profitability. I have effectively used the cash flow forecasting model by setting certain
assumptions such as only cash elements will be recognised in calculation and non-cash elements
will not be considered, all the calculations are based upon historical figures from the year 2019,
Bed-debts, Director’s remuneration and depreciation expenses will be ignored, etc. in order to
carry out study with utmost accuracy and relevancy.
6

REFERENCES
Books and Journals
Yoo, C. Y. and Pae, J., 2013. Estimation and prediction tests of cash flow forecast
accuracy. Journal of Forecasting. 32(3). pp.215-225.
Tobback, E. and et. al., 2014. Forecasting Loss Given Default models: impact of account
characteristics and the macroeconomic state. Journal of the Operational Research
Society. 65(3). pp.376-392.
Podsiadło, M. and Rybinski, H., 2015, June. Application of Fuzzy Rough Sets to Financial Time
Series Forecasting. In International Conference on Pattern Recognition and Machine
Intelligence (pp. 397-406). Springer, Cham.
Miao, B., Teoh, S.H. and Zhu, Z., 2016. Limited attention, statement of cash flow disclosure, and
the valuation of accruals. Review of Accounting Studies, 21(2), pp.473-515.
Balance, B.C and et.al., 2015. Cash Management.
Foerster, S., Tsagarelis, J. and Wang, G., 2017. Are Cash Flows Better Stock Return Predictors
Than Profits?. Financial Analysts Journal, 73(1), pp.73-99.
Fawzi, N.S., Kamaluddin, A. and Sanusi, Z.M., 2015. Monitoring distressed companies through
cash flow analysis. Procedia Economics and Finance, 28, pp.136-144.
Nwanyanwu, L.A., 2015. Cash flow and Organizational performance in Nigeria: Hospitality and
Print media industries perspectives. European Journal of Business, Economics and
Accountancy, 3(3), pp.66-72.
7