budgeting and forecasting

profileASHLEYFIRST01
budgeting__forecasting.xlsx
Home>Business & Finance homework help>budgeting and forecasting

Instructions

BUSN-278 Budgeting and Forecasting Template Instructions
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

SALES FORECAST (amounts in thousands) using multiplicative model
Quarters (x) sales (y) XY X² Trend %age change seasonal variations Sales forecast(trend*seasonal variation)
Year 1 1 51.00 51.00 1 49.44 103.16 100.206 49.54
2 52.28 104.55 4 51.22 102.06 100.036 51.24
3 53.58 160.75 9 53 101.10 99.952 52.97
4 54.92 219.69 16 54.78 100.26 99.946 54.75
Year 2 5 56.43 282.16 25 56.56 99.77 100.206 56.68
6 57.98 347.90 36 58.34 99.39 100.036 58.36
7 59.58 417.05 49 60.12 99.10 99.952 60.09
8 61.22 489.73 64 61.9 98.90 99.946 61.87
Year 3 9 62.90 566.10 81 63.68 98.78 100.206 63.81
10 64.63 646.30 100 65.46 98.73 100.036 65.48
11 66.41 730.48 121 67.24 98.76 99.952 67.21
12 68.23 818.80 144 69.02 98.86 99.946 68.98
Year 4 13 70.11 911.43 169 70.8 99.03 100.206 70.95
14 72.04 1,008.53 196 72.58 99.25 100.036 72.61
15 74.02 1,110.28 225 74.36 99.54 99.952 74.32
16 76.05 1,216.87 256 76.14 99.89 99.946 76.10
Year 5 17 78.15 1,328.48 289 77.92 100.29 100.206 78.08
18 80.29 1,445.31 324 79.7 100.75 100.036 79.73
19 82.50 1,567.56 361 81.48 101.26 99.952 81.44
20 84.77 1,695.43 400 83.26 101.82 99.946 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 1 103.16 102.06 101.1 100.26
∑YX=a∑x+b∑x² 15118.38=a(210)+b(2870) year 2 99.77 99.39 99.1 98.9
year 3 98.78 98.73 98.76 98.86
Eqn: Y= 47.66+1.78b year 4 99.03 99.25 99.54 99.89
year 5 100.29 100.75 101.26 101.82
totals 501.03 500.18 499.76 499.73
average 100.206 100.036 99.952 99.946
Final Yearly sales forecast(amounts in thousands)
Year Sales
1 208.51
2 237.00
3 265.49
4 293.98
5 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 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

3.0 Capital Expenditure Budget

CAPITAL EXPENDITURE BUDGET (Amounts in Dollars)
Decsription Per team 5 teams Cost per unit Total amounts
Initial route of 100 homes 120000
A pickup truck 1 5 20000 100000
A trailer 1 5 2000 10000
A large riding lawn mower 1 5 800 4000
A small riding lawn mower 1 5 600 3000
A push lawn mower 1 5 120 600
A gas powered lawn trimmers 2 10 200 2000
A gas powered lawn edgers 2 10 360 3600
A gas powered blowers 2 10 80 800
A hedge trimmer 2 10 500 5000
A long ladder 2 10 5 50
A back saw 1 5 2 10
A drink cooler 1 5 200 1000
Tools for minor repair 100
Protection equipments 100
Office furnitures and fittings 1000
Laptop 300
Accounting softwares 250
Other office equipments 700
TOTAL INITIAL INVESTMENT 252510
All the costs shown are derived from the manufacturers that have posted their prices on the internet
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

Put your detailed cash inflows and cash outflows here, also showing net cash flow.
CASH FLOWS
Year 1 2 3 4 5
Cash inflows 208510 237000 265490 293980 322470
Cash outflows:
Advertising 10000 8000 6000 4000 2000
Rent 30000 30000 30000 30000 30000
Interest 17676 14141 10605 7070 3535
Salary to manager 24000 42000 42000 42000 42000
Insurance 1000 1000 1000 1000 1000
Salary to workers 50000 50000 50000 50000 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 since it does not involve real cash

4.2 NPV Analysis

Create an NPV Analysis here.
year Net cashflows Discounting rate PVAF at 7% Present value
1 65119 0.07 0.9346 60,860.22
2 75279 0.07 0.8734 65,748.68
3 97141 0.07 0.8163 79,296.20
4 118948 0.07 0.7629 90,745.43
5 140809 0.07 0.713 100,396.82
Total 397,047.34
Initial investment cost 252510
NPV 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

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 INVESTMENT
1 14556 252510
2 24717 0
3 46578 0
4 68538 0
5 90399 0
TOTALS 244788 252510
AVERAGE 48957.6 126255
RRR = 0.3877676132
%AGE 38.80%

4.4 Payback Period Calculations

Show your payback period calculations here.
Year Net cashflows Accumulated payment
1 65119 65119
2 75279 140398
3 97141 237539
4 118948 252510
5 140809
payback period = 3 +[ (252510-237539)/118948]*12
Payback period is 3 years and 2 months

5.0 Pro Forma Financials

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 year 2 year 3 year 4 year 5
Details Amounts Amounts Amounts Amounts Amounts Amounts Amounts Amounts Amounts Amounts
Sales 208510 237000 265490 293980 322470
Expenditures
Salary to manager 24,000.00 42000 42,000.00 42000 42000
Salary to Lawn service workers 50,000.00 50000 50,000.00 50000 50000
Advertsing 10,000.00 8000 6,000.00 4000 2000
Office rent 30,000.00 30000 30,000.00 30000 30000
Depreciation 50,410.00 50410 50,410.00 50410 50410
Interest cost 17,676.00 14141 10,605.00 7070 3535
Insurance 1,000.00 1000 1,000.00 1000 1000
Dimuniation 153.00 153 153.00 0 0
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
Income tax 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
amount rate payment Interest 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
year 1 252,510.00 0.07 50502 17,676
year 2 202,008.00 0.07 50502 14,141
year 3 151,506.00 0.07 50502 10,605
year 4 101,004.00 0.07 50502 7,070
year 5 50,502.00 0.07 50502 3,535
Depreciation charge
cost rate depriciation 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%
year 1 252,050.00 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
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 year 1 year 2 year 3 year 4 year 5
Non-current assets 252,510.00 252,510.00 252,510.00 252,510.00 252,510.00
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 24717 46578 68538 90,399.00
Loan liability 202,008.00 151506 101004 50502 - 0
216,564.00 176,223.00 147,582.00 119,040.00 90,399.00
Current liabilties 32,706.00 32,797.00 32,889.00 33,134.00 33,226.00
Total capital and liabilities 249,270.00 209,020.00 180,471.00 152,174.00 123,625.00

Sheet1