week 7 project

Author: Your year 1 sales are $100K

Author: You wrote over the formula, but you can enter the PV values from the PV table based on your interest rate--see cell A17.

Author: This row from year 1 - 5 should show discounted cash flows.

Author: Your payback is more than four years but less than five years. Look at the year in which cumulative cash flow goes from negative to positive. Payback is achieved in the year when cumulative cash flow is $0 or positive. The initial investment is a minus amount, and the annual cash flow for years 1, 2, etc should be positive. Please see the payback calculation example in the Week 4 online lesson. Year 1 cumulative cash flow = the initial investment (minus) + year 1 annual cash flow. Year 2 cumulative cash flow = year 1 cumulative cash flow + year 2 annual cash flow. It is usually shown as year + decimal. For example, if cumulative cash flow goes positive sometime during year 3, then payback will be in 2.3 or 2.6 etc years.

Instructions

BUSN278 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

Column1	Column2	Column3	Column4	Column5	Column6	Column7
Sales Fore Cast
years		2017	2018	2019	2020	2021
Sales		100000	150000	250005	400008	500010
assumptons and methods used
The above mentioned figures have being arrived at after some calculations as illustrated below
Year one 100000 dollars in sales
It is assumed that the percentage increase in sales for the first one year will be 50%
Thus (150/100)*100000
=150000 this is the year 2 sales
Year two sales 150000 dollars
Assumed that there is 66.67% increase in sales
(166.67/100)*150000
= 250000 this is the year 3 sales
Year 3 sales 250000
Percentage increase in sale 60%
(160/100)*250000
400000 sales in year four
Year four sales 400000
Anticipated percentage increase in sale 25%
(125/100)*400000
500000 sales in year five
Year five sales 500000

3.0 Capital Expenditure Budget

Item	Cost	Quantity	Total cost	Source
Registering a business	$ 200.00		$ 200.00	ehow.com
Renovation of facility	$ 18,000.00	1	$ 18,000.00	Given
Soda fountain bar	$ 3,620.00	1	$ 3,620.00	Soda-dispenser.com
2 pizza ovens	$ 860.00	2	$ 1,720.00	Alibaba.com
salad and Pizza/dessert bar	$ 1,250.00	1	$ 1,250.00	Alibaba.com
Commercial Refrigerator	$ 3,400.00	1	$ 3,400.00	Coldtechcommercial.com
Cash Register	$ 180.00	2	$ 360.00	ebay
Range /Oven	$ 530.00	1	$ 530.00	Restaurant Depot
Hood /Ancillary System	$ 7,000.00		$ 7,000.00	Restaurant Depot
Laptop for management	$ 285.00	1	$ 285.00	Best Buy
desk for mgmt	$ 25.00	1	$ 25.00	Restaurant Depot
Staff Microwave	$ 320.00	1	$ 320.00	Restaurant Depot
Staff cupboard	$ 120.00	1	$ 120.00	Assumed
staff refrigerator	$ 700.00	1	$ 700.00	Restaurant Depot
Tables	$ 250.00	20	$ 5,000.00	tableschairsbarstools.com
Chairs	$ 40.00	80	$ 3,200.00	restaurant-services.com
Busing cart for restaurant	$ 60.00	1	$ 60.00	Ebay
Commercial Dishwasher	$ 2,100.00	1	$ 2,100.00	Ebay
Restaurant signage	$ 130.00	1	$ 130.00	brightledsigns.com
Total			$ 48,020.00

4.1 Cashflows

Project with uneven cash flows
Year	Metric	0	1	2	3	4	5
ARR accounting rate of return
Enter total annual sales years 1 to 5 from for your course project company's Week 2 Five Year Sales Forecast			10,000	15,000	25,000	40,000	50,000
Calculate net operating cash flows years 1 to 5 (assume = 35% sales).	35.00%		3,500	5,250	8,750	14,000	17,500
Enter straight line annual depreciation years 1 to 5 = (initial investment - salvage value)/5.			11,525	11,525	11,525	11,525	11,525
Annual accounting income/(loss) years 1 to 5 = net operating cash flow minus annual depreciation			-8,025	-6,275	-2,775	2,475	5,975
Compute ARR = average accounting income for years 1 to 5 which is the sum of accounting income /5, divided by the initial investment(with no minus sign).	3.60%

4.2 NPV Analysis

NPV net present value
Project interest rate (assume = 8%)	8.00%
Enter net operating cash flows			3,500	5,250	8,750	14,000	17,500
Enter initial investment in year 0 (a cash outflow is a negative amount).		-48,020
Add cash inflow (positive amount) from receipt of salvage value (aka FMV fair market value) in year 5 (assume = 20% of initial investment)	20.00%						9,604
Net operating and investing cash flows years 0 to 5		-48,020	3,500	5,250	8,750	14,000	27,104
PV $1 factor using project discount rate. The PV factors were calculated using the formula for PV $1,		1	0.9259	0.8573	0.7938	0.735	0.6806
Calculate each column's PV cash flows		-48,020	3,241	4,501	6,946	10,290	18,447
Compute NPV $ years 0 to 5 which = the sum of the PV cash flows	-4,595

4.3 Rate of Return Calculations

IRR Internal rate of return
IRR Internal rate of return %:	5.20%

4.4 Payback Period Calculat

Payback
Payback table >>>		Yr	Investment	Ann CF	Cumulative CF
Enter initial investment from year 0 shown in (cash outflow is negative)		0	-48,020		-48,020
Enter the net cash flows for years 1 to 5		1		3,500	-44,520
Payback is achieved when cumulative cash flow is $0		2		5,250	-39,270
		3		8,750	-30,520
		4		14,000	-16,520
		5		27,104	10,584
Payback is achieved in which year	5.39
PI Profitability Index
Divide the sum of the PV cash flows for years 1 to 5, by the PV cash flow in year 0	0.9

5.0 Pro Forma Financials

Fiscal Year Begins	2017
	2017	% T Rev	2018	% T Rev	2019	% T Rev	2020	% T Rev	2021	% T Rev
Revenue (Sales)
Buffet sales	504,000	98.6	609,000	98.3	634,375	98.3	660,801	98.3	688,330	98.3
Gaming coin	7,200	1.4	10,800	1.7	11,232	1.7	11,681	1.7	12,148	1.7
Total Revenue (Sales)	511,200	100	619,800	100	645,607	100	672,482	100	700,478	100
Cost of Sales
Meat	28,800	5.6	31,700	5.1	32,900	5.1	33,500	5	34,210	4.9
Vegetables	19,800	3.9	20,200	3.3	21,000	3.3	22,100	3.3	22,900	3.3
pasta	12,000	3.9	15,000	2.4	15,700	2.4	16,100	2.4	16,900	2.4
Beverages	16,200	2.3	16,900	2.7	17,100	2.6	17,800	2.6	18,120	2.6
Dairy	18,000	3.2	19,400	3.1	19,700	3.1	20,000	3	20,345	2.9
Chef wages	43,200	3.5	48,600	7.8	50,400	7.8	52,200	7.8	54,000	7.7
Total Cost of Sales	138,000	27	151,800	24.5	156,800	24.3	161,700	24	166,475	23.8
Gross Profit	373,200	73	468,000	75.5	488,807	75.7	510,782	76	534,003	76.2
				0
Expenses				0
Salary expenses	194,040	38	196,995	31.8	199,995	31	203,040	30.2	206,132	29.4
Supplies (office and operating)	500	0.1	600	0.1	650	0.1	720	0.1	775	0.1
Repairs and maintenance	1,000	0.2	1,000	0.2	1,000	0.2	1,000	0.1	1,500	0.2
Advertising	15,000	2.9	8,000	1.3	6,000	0.9	4,000	0.6	2,000	0.3
Telephone	500	0.1	480	0.1	520	0.1	550	0.1	470	0.1
Rent	36,000	7	36,000	5.8	36,000	5.6	36,000	5.4	36,000	5.1
Utilities	18,000	3.5	20,200	3.3	19,000	2.9	19,700	2.9	19,000	2.7
Insurance	1,000	0.2	1,000	0.2	1,000	0.2	1,000	0.1	1,000	0.1
Taxes (real estate, etc.)	0		36,000	5.8	38,752	6	40,862	6.1	42,963	6.1
Depreciation (Assume straight-line X years)			1,000	0.2	1,000	0.2	1,000	0.1	1,000	0.1
Total Operating Costs	266,040	52	301,275	48.6	303,917	47.1	307,872	45.8	310,840	44.4
Operating Profit	107,160	21	166,725	26.9	184,890	28.6	202,910	30.2	223,163	31.9
Income Tax Expense (assume $0 for LLC)	0	0	0	0	0	0	0	0	0	0
Net Income	107,160	21	167,725	27.1	185,890	28.8	203,910	30.3	224,163	32
Beginning balance	0	250,000	121,037	276,639	450,407	642,194
Add sales	0	511,200	619,800	645,607	672,482	700,478
Cash available to use	0	761,200	740,837	922,246	1,122,889	1,342,672
Less disbursements	0	403,640	451,675	459,317	468,172	475,915
Cash surplus	0	357,560	513,162	686,929	878,717	1,090,757
investment	250,000
Minus other payment	250,000
Budgeted ending cash balance	250,000	345,037	500,639	674,407	866,194	1,078,234
Net cash flow	0	107,560	168,125	186,290	204,310	224,563
Interest rate	8%
Term in years	5
Principal borrowed	50,000
Ending loan balance	50,000	41,477	32,273	22,332	11,595	0
Balance sheet
Period Ending		"17	"18	"19	"20	"21
Assets
Current Assets
	Cash And Cash Equivalents	115,000	140,000	150,000	160,000	165,000
	Short Term Investments	40,000	50,000	60,000	70,000	80,000
	Net Receivables	-	-	-
	Inventory	30,000	45,000	55,000	65,000	70,000
	Other Current Assets	25,000	30,000	35,000	34500	40,000
Total Current Assets		210,000	265,000	300,000	329,500	355,000
Long Term Investments		40,000	45,000	45,000	50,000	55,000
Cooking Equipments		110,000	110,000	105,000	120,000	135,000
Goodwill		60,000	60,000	60,000	60,000	60,000
Intangible Assets		45,000	55,000	60,000	65,000	70,000
Accumulated Amortization		-	-	-
Other Assets		35,000	34,550	36,760	37,870	38,000
Deferred Long Term Asset Charges		-	-	-	-	-
Total Assets		500,000	569,550	606,760	662,370	713,000
Liabilities
Current Liabilities
	Accounts Payable	45,000	44,700	50,000	55,400	64,500
	Short/Current Long Term Debt	34,600	30,000	45,500	55,600	66,000
	Other Current Liabilities	16,400	16,300	17,500	18,000	19,500
Total Current Liabilities		96,000	91,000	113,000	129,000	150,000
Long Term Debt		145,000	155,000	160,000	176,890	214,600
Other Liabilities		65,000	76,000	80,000	98,780	113,000
Deferred Long Term Liability Charges		23,000	25,000	20,000	26,789	28,780
Minority Interest		-	-	-
Negative Goodwill		-	-	-
Total Liabilities		329,000	347,000	373,000	431,459	506,380
Stockholders' Equity		171,000	222,550	233,760	230,911	206,620
Total shareholder and Liabilities		500,000	569,550	606,760	662,370	713,000

Sheet1

		-	-	-
Redeemable Preferred Stock		-	-	-
Preferred Stock		-	-	-
Common Stock			28,767,000	25,922,000
Retained Earnings		89,223,000	75,066,000	61,262,000
Treasury Stock		-	-	-
Capital Surplus		-	-	-
Other Stockholder Equity		-1,874,000	27,000	125,000
Total Stockholder Equity		120,331,000	103,860,000	87,309,000