Financial Modeling for Business Expansion: An Excel-Based Approach

Desklib provides past papers and solved assignments. This project uses Excel for business financial modeling.

EXCEL SPREADSHEET
1

Table of Contents
Assessment 2..............................................................................................................................3
Task 1.....................................................................................................................................3
Task 2.....................................................................................................................................7
Reference list..............................................................................................................................9
2

Assessment 2
Task 1
a) My business is a business of retailers in west London. It has been successful so far,
however presently I am trying to expand the business (Burns, 2016). Due to the
expansion, I would require certain funding. I have arranged 10000 pounds from my
family and friends. Further I need certain funding for goods and capital. The overall
amount I need for goods and capital to expand my business is 52500 pounds (Aalbers
et al. 2017). Thus I would require loan to recover this amount in order to expand my
business. Therefore I would have to apply for bank loan considering in context of my
sales and expenses for 10 to 12 months.
b) a) Auto sdum is the summation process of the units in cells of income. Thus we find
the total income of 12 months the company (Burt and Burzynska, 2017). Overall
income of the company have also been depicted by the autosum process (Cowling et
al. 2016). Further overall revenue have been calculated by the autosum.
b) Auto format is done in the form of highlight of the income statement of my
company. The overall cost and expenses of the company have been highlighted.
C) Formula of average have been used on the basis of finding the average of the income
(Gillett and Tennent, 2018). The mean income for twelve months have been calculated by
using the formula for average.
d) Percentage formula have been used for estimating the profit percent and income percent
(Invernizzi et al. 2017). The percentage of income and profit have been calculated by the
percentage formula of excel.
Financial
details of
expenses
and sales.
Assum
ptions
Jan
uar
y
Feb
rua
ry
Mar
ch
Apr
il
Ma
y
Jun
e
Jul
y
aug
ust
Se
pte
mb
er
Oct
obe
r
No
ve
mb
er
De
ce
mb
er
T
ot
al
Ave
rag
e
Reven
ue
Units 100 50 65 70 50 60 60 50 50 180 300 50
pricing
in
pound
s 230 230 230 230 230 230 230 230 230 230 230 230
3

Expenses of
operation
work labour
cost(pounds)
£35
0 350 350 350 350 350 350 350 350 350 350 350
cost of
marketing(po
unds) 500 500 500 500 500 500 500 500 500 500 500 500
Tax
30
%
30
%
30
%
30
%
30
%
30
%
30
%
30
%
30
%
30
%
Cogs
Cost of units
in pounds 120 120 120 120 120 120 120 120 120 120 120 120
1
4
4
0
Statem
ent of
income
Overal
l
reven
ue
230
00
115
00
149
50
161
00
115
00
138
00
138
00
115
00
115
00
414
00
690
00
115
00
Overal
l Cogs
120
00
600
0
780
0
840
0
600
0
720
0
720
0
600
0
600
0
216
00
360
00
600
0
overall
profit
110
00
550
0
715
0
770
0
550
0
660
0
660
0
550
0
550
0
198
00
330
00
550
0
Overall
profit
percent
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
47.
826
086
96
Expenses of
operation
work
labour
cost 350 350 350 350 350 350 350 350 350 350 350 350
4
2
0
0
Cost of
market
ing 500 500 500 500 500 500 500 500 500 500 500 500
6
0
0
0
Aggreg
ate 850 850 850 850 850 850 850 850 850 850 850 850
I
ncome
on
operat
ion
101
50
465
0
630
0
685
0
465
0
575
0
575
0
465
0
465
0
189
50
321
50
465
0
1
0
9
1
5
0
margi
n of
operat
44.
130
434
40.
434
782
42.
140
468
42.
546
583
40.
434
782
41.
666
666
41.
666
666
40.
434
782
40.
434
782
45.
772
946
46.
594
202
40.
434
782
4

ion 78 61 23 85 61 67 67 61 61 86 9 61
Taxati
on
304
5
139
5
189
0
205
5
139
5
172
5
172
5
139
5
139
5
568
5 0 0
overall
incom
e
710
5
325
5
441
0
479
5
325
5
402
5
402
5
325
5
325
5
132
65
321
50
465
0
8
2
7
9
5
358
7.0
833
33
Overall
income
percen
t
30.
891
304
35
28.
304
347
83
29.
498
327
76
29.
782
608
7
28.
304
347
83
29.
166
666
67
29.
166
666
67
28.
304
347
83
28.
304
347
83
32.
041
062
8
46.
594
202
9
40.
434
782
61
Avera
ge
incom
e:
358
7.0
833
3
f) Bar chart have been used in order to highlight the business expense of my company. The
proportionality of expenses have been outlined in the bar chart.
Expense list
Cogs 1440
labour cost 1200
marketing
cost 6000
Expense list
Cogs
labour cost
marketing cost
Chart No 1: Expense chart
(Source: Self Created)
5

g) pie chart have been used in order to highlight the source of income of my company
(Mimir, 2016). Various sources of income regarding the expansion of my business have been
highlighted by the help of pie chart.
source of income and loan
Family and friends
in
pounds 10000
Loan for goods
in
pounds 50000
Loan for capital
in
pounds 2500
Income on operation 58650
Family
and
friends
Loan for
goods
Loan for
capital
Income on
operation
0
10000
20000
30000
40000
50000
60000
70000
source of income and loan
source of income and loan
Chart No 2: Source and Income chart
(Source: Self Created)
6

h) Barchart have been used in order to highlight the proportionality of stock, operating money
and capital (Owen et al. 2019). Thus the proportionality of distribution of these three factors
of my company have been clearly outlined.
Operation money 109150
Capital 12500
Stock 50000
Operation money Capital Stock
0
20000
40000
60000
80000
100000
120000
Series1
Series2
Chart No 2: Capital, stock and Operational Money
(Source: Self Created)
Task 2
Commentary:
Formula used for the summation of revenue, income and operation money by me is autosum
(Pfitzner and McLaren, 2018). Autosum formula has been useful to calculate the estimates of
income and expenses and operation money. Aggregating all the fields of expenses and
purchases is done by the help of autosum. Percentage of income and profit has been
calculated by the help of percentage formula. Overall calculation of the percentage for 12
months has been done easily by the usage of this formula (Townsend, 2018). Auto format
have been helpful in order to highlight the income statement of my company. Finally the
7

formula for average has been useful to calculate the mean income of my company. All these
calculations have been the guideline to depict the distributions in the chart. (unctad.org 2002)
I have used different charts in order to outline the distributions of income and expense
of my company. Further bar chart has been used by me to outline the distribution of
stock, capital and operation money. Segmentation of expenses, income, stock, capital
and operation money have been outlined by the help of graph. Distributors for income
and loan and their percentages have been showcased by me in the form of pie chart.
Thus my designing of spreadsheet have been helpful to formulate the segments of
income statement into chart. (infoentrepreneurs.org 2019)
8

Reference list
Book
Burns, P., 2016. Entrepreneurship and small business. UK: Palgrave Macmillan Limited.
Journal
Aalbers, M.B., Loon, J.V. and Fernandez, R., 2017. The financialization of a social housing
provider. International Journal of Urban and Regional Research, 41(4), pp.572-587.
Burt, R.S. and Burzynska, K., 2017. Chinese entrepreneurs, social networks, and
guanxi. Management and Organization Review, 13(2), pp.221-260.
Cowling, M., Liu, W., Minniti, M. and Zhang, N., 2016. UK credit and discouragement
during the GFC. Small Business Economics, 47(4), pp.1049-1074.
Gillett, A.G. and Tennent, K.D., 2018. Shadow hybridity and the institutional logic of
professional sport: Perpetuating a sporting business in times of rapid social and economic
change. Journal of Management History, 24(2), pp.228-259.
Invernizzi, A.C., Menozzi, A., Passarani, D.A., Patton, D. and Viglia, G., 2017.
Entrepreneurial overconfidence and its impact upon performance. International Small
Business Journal, 35(6), pp.709-728.
Mimir, Y., 2016. Financial intermediaries, credit shocks and business cycles. Oxford Bulletin
of Economics and Statistics, 78(1), pp.42-74.
Owen, R., Deakins, D. and Savic, M., 2019. Finance pathways for young innovative small‐
and medium‐size enterprises: A demand‐side examination of finance gaps and policy
implications for the post‐global financial crisis finance escalator. Strategic Change, 28(1),
pp.19-36.
Pfitzner, D.M. and McLaren, J., 2018. Microbusinesses in Australia: a robust
definition. Australasian Accounting Business & Finance Journal, 12(3), pp.4-18.
Townsend, N., 2018. PolyGram Filmed Entertainment and Working Title Films: the making
of a film studio and its production label. Historical Journal of Film, Radio and
Television, 38(3), pp.555-583.
Yermack, D., 2017. Corporate governance and blockchains. Review of Finance, 21(1), pp.7-
31.
Online article
unctad.org (2002),Excel spreadsheet,Available at: https://unctad.org/en/Docs/iteiia5_en.pdf
(Accessed on: 3rd April 2019)
9

Website
infoentrepreneurs.org (2019), infoentrepreneurs Available at:
https://www.infoentrepreneurs.org/en/guides/budgeting-and-business-planning/ (Accessed
on: 2nd April 2019)
10