budgeting and forecasting

Use this spreadsheet structure to lay out the various sections of your project.

The purpose of this spreadsheet is to make it easy for your professor to locate the 

various sections of your project.  Please don’t alter the Worksheet Tabs or titles.

 

2

>Instructions

Budgeting and Forecasting Template Instructions

BUSN-2 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper

7

8

Use this spreadsheet structure to lay out the various sections of your project.

The purpose of this spreadsheet is to make it easy for your professor to locate the

various sections of your project. Please don’t alter the Worksheet Tabs or titles.

After you finish your calculations in this spreadsheet, you will have to

create a written report where you take screenshots from this spreadsheet

and put them in the Budget Proposal Template, along with necessary

explanations. Detailed instructions for how to write the report

are found in the Budget Proposal Template, a word document.

2.

1

& 2.2

Sales

Forecast

s in thousands) using multiplicative model

1

1

1.

0

51.00 1

.44

.

.

6

.54

2

4

3

.58

0.75

9 53

0

4

.69

16

6

5

6

36

7

.05

49

0

99.952

8

0

99.946

9

100.206

10

100

100.036

99.952

12

.

4

99.946

100.206

14

100.036

99.952

16

99.946

17

100.206

18

100.036

19

99.952

20

99.946

1

102.06 101.1 100.26

99.77 99.39 99.1 98.9

98.78 98.73 98.76 98.86

99.03 99.25 99.54 99.89

100.29 100.75 101.26 101.82

.18

100.206 100.036 99.952 99.946

Year Sales
1

2

3

4

5

of 2.5% starting quater 2. there will also be increase in sales starting the first quarter of year 2 due to the 1% economic growth. the sales will increaese at a rate of 0.25% per quarter

SALES FORECAST (	amount
Quarters (x)	sales (y)	XY	X²	Trend	%age change	seasonal variations	Sales forecast(trend*seasonal variation)
			Year																5						0								4		9								10						3	1	6						100	20		49
52.28	104.55	51.22		102.06	100.0	36	51.24
	53		16		101.1						99.952	52.97
54.92	2	19	54.78		10					0.2						99.946	54.75
Year 2	56.43	282.16	25	56.56		99.77			100.206	56.68
57.98	347.90	58.34		99.39					100.036	58.36
59.58	4	17	60.	12		99.1	60.09
61.22	489.73	64	61.9		98.9	61.87
Year 3	62.90	566.10	81	63.68		98.78	63.81
64.63	646.30	65.46		98.73	65.48
11	66.41	730.48	121	67.24		98.76	67.21
68.23	8	18	80		14	69.02		98.86	68.98
Year 4	13	70.11	911.43	169	70.8		99.03	70.95
72.04	1,008.53	196	72.58		99.25	72.61
15	74.02	1,110.28	225	74.36		99.54	74.32
76.05	1,216.87	256	76.14		99.89	76.10
Year 5	78.15	1,328.48	289	77.92		100.29	78.08
80.29	1,445.31	324	79.7		100.75	79.73
82.	50	1,567.56	361	81.48		101.26	81.44
84.77	1,695.43	400	83.26		101.82	83.22
Sumation	210	1,327.09	15,118.38	2870
%age changes
Eqn: y=a+bx	Quarter 1	Quarter 2	Quarter 3	Quarter 4
∑Y=na+b∑x	1327.09=20a+b(210)		year	103.16
∑YX=a∑x+b∑x²	15118.38=a(210)+b(2870)					year 2
				year 3
Eqn: Y= 47.66+1.78b					year 4
				year 5
totals	501.03		500	499.76	499.73
average
Final Yearly sales forecast(amounts in thousands)
208.51
237.00
265.49
293.98
322.47
I have used the multiplicative model to forecast the sales. The first sales of the entreprenueur will be from servicing the 100 homes he will purchase from the retiring entreprenueur. He will then carry out extensive advertising campaign which will increase his sales at a		rate

3.0 Capital Expenditure Budget

in Dollars)

amounts

0

0

1 5

0

0

0

1 5 2000

1 5

1 5

0

1 5

600

2 10 200 2000

2 10

2 10 80 800

2 10 500

2 10 5 50

1 5 2 10

1 5 200 1000

100

100

1000

300

s shown are derived from the manufacturers that have posted their prices on the internet

CAPITAL EXPENDITURE BUDGET (

Amounts

Decsription

Per team

5 teams

Cost per unit

Total

Initial route of 100 homes

1

200

A pickup truck

2000

1000

A trailer

10000

A large riding lawn mower

800

4000

A small riding lawn mower

600

300

A push lawn mower

120

A gas powered lawn trimmers

A gas powered lawn edgers

360

3600

A gas powered blowers

A hedge trimmer

5000

A long ladder

A back saw

A drink cooler

Tools for minor repair

Protection equipments

Office furnitures and fittings

Laptop

Accounting softwares

250

Other office equipments

700

TOTAL INITIAL

INVESTMENT

252510

All the

cost

I have assumed that the enterprenueur will borrow the initial investment from a bank. The cost of borrowing for small businesses is 7%

4.1 Cashflows

Year 1 2 3 4 5

10000

4000 2000

30000 30000 30000 30000

42000 42000 42000

1000 1000 1000 1000 1000

50000 50000 50000 50000

since it does not involve real cash

Put your detailed cash inflows and cash outflows here, also showing net cash flow.
CASH FLOWS
Cash inflows		208510		237000		265490		293980		322470
Cash outflows:
Advertising		8000	6000
Rent								30000
	Interest	17676		14141	10605		7070		3535
	Salary to manager	24000							42000
	Insurance
Salary to workers								50000
	Income tax	7838	13309	25080	36905	48676
Commission	2877	3271	3664	4057	4450
Total outflows	143391	161721	168349	175032	181661
		Net cashflows				65119			75279			97141			118948			140809
We exclude	depriciation

4.2

NPV

Analysis

year Net cashflows

1 65119

2 75279 0.07

3 97141 0.07

4 118948 0.07

5 140809 0.07

Total

252510

NPV

Create an NPV Analysis here.
Discounting rate	PVAF at 7%	Present value
									0.07	0.9346	60,860.22
0.8734	65,748.68
0.8163	79,296.20
0.7629	90,745.43
0.713	100,396.82
397,047.34
Initial investment cost
144,537.34
I have used the discounting rate of 7% which is the cost of servicing the loan

4.3 Rate of Return Calculations

INVESTMENT

1

252510

2

0

3

0

4

0

5

0

252510

Show your rate of return calculations in this worksheet.
Rate of return = average profits/average investment
where: average profits=profit for 5 years/5years
average investment= total initial invstment/2
Therefore: ARR=
YEAR	PROFIT
14556
	24717
	46578
	68538
90399
TOTALS	244788
AVERAGE	48957.6	126255
RRR =	0.3877676132
%AGE	38.80%

4.4 Payback Period Calculations

Year Net cashflows

1 65119 65119
2 75279

3 97141

4 118948 252510
5 140809

Show your payback period calculations here.
Accumulated	payment
140398
237539
payback period = 3 +[ (252510-237539)/118948]*12
Payback period is 3 years and 2 months

5.0 Pro Forma Financials

year 2 year 3 year 4 year 5

Amounts Amounts Amounts Amounts Amounts Amounts Amounts Amounts Amounts Amounts

Sales 208510 237000 265490 293980 322470
Salary to manager

42000

42000 42000

50000 50,000.00 50000 50000

8000

4000 2000

30000 30,000.00 30000 30000

50,410.00 50410 50410

.00

14141

.00

7070 3535

Insurance

1000 1,000.00 1000 1000

.00

153

0 0

Income tax

amount rate payment Interest

year 1

0.07

17,676

year 2

0.07 50502

year 3

0.07 50502 10,605

year 4

0.07 50502

year 5

0.07 50502

cost rate depriciation

year 1

0.2 50410

year 2 252,050.00 0.2 50410
year 3 252,050.00 0.2 50410
year 4 252,050.00 0.2 50410
year 5 252,050.00 0.2 50410

year 1 year 2 year 3 year 4 year 5

252,510.00 252,510.00 252,510.00 252,510.00 252,510.00

24717 46578 68538

202,008.00

50502

90,399.00

249,270.00 209,020.00 180,471.00 152,174.00 123,625.00

Put your Pro-Forma Income Statement, Balance sheet and statement of cash flows here, along with any other supporting calculations or schedules.
Pro-forma income statement
			year 1
Details
Expenditures
24,000.00	42,000.00
Salary to Lawn service workers		50,000.00
Advertsing	10,000.00	6,000.00
Office rent		30,000.00
Depreciation		50,410.00								50410
Interest cost		17,676		10,605
	1,000.00
Dimuniation		153	153.00
Commission to credit card company	2,877.44	3270.6	3,663.76	4056.924	4450.086
Total expenses	186116.438	198974.6	193831.762	188536.924	183395.086
profit befor tax	22393.562	38025.4	71658.238	105443.076	139074.914
7837.7467	13308.89	25080.3833	36905.0766	48676.2199
Profit after tax	14555.8153	24716.51	46577.8547	68537.9994	90398.6941
Intrest payable
The amount of loan borrowed is equals to the initial capital investment which is payable in equal annual installments over a period of 5 years. The interest is payable at an actual rate of 7% per annum on a reducing balance method
					252,510.00						50502
	202,008.00	14,141
151,506.00
101,004.00	7,070
50,502.00	3,535
Depreciation charge
The equipments and other assets are depriciated over a period of 5 years after which they will have a nil salage value, over the period of five years this will give us annual rate of 20%
				252,050.00
I have excluded the cost of tools for minor repair, software, protection equipment and backsaw since they will suffer dimunuation for three years
Tax is payable at the rate of 35% of pbt
Advertising costs reduce over time since there will be increased bussiness goodwill as the bussiness continues in operation
Proforma Balance shhet
DETAILS
Non-current assets
Depriciation & DIMMUNIATION	(50,563.00)	-100973	-151383	-201640	(252,050.00)
Net book value	201,947.00	151,537.00	101,127.00	50,870.00	460.00
Current assets(bank)	47,323.00	57483	79344	101304	123,165.00
Total assets		249,270.00		209,020.00		180,471.00		152,174.00		123,625.00
Capital & Liability
Retained earnings	14,556.00		90,399.00
Loan liability	151506	101004	– 0
216,564.00	176,223.00	147,582.00	119,040.00
Current liabilties	32,706.00	32,797.00	32,889.00	33,134.00	33,226.00
Total capital and liabilities

Sheet1