Monthly cashflows projection-3hrs

NOW THIS IS URGENT: DON’T ACCEPT IF YOU CANNOT DELIVER!

please generate the monthly cash-flow projection using the excel sheet attached. You must use the annual cash-flow estimation in the workbook to determine the distribution of inflows and outflows during the months. 

I HAVE A SHORT WHILE!!

Annual

Income statement

Income statement

premiums

$ 6,000.00

$ 262,800.00

Net Income

																																																																																																																																																																																																																																																																																																																																																																																																																																	B&B FURLING BOARDING
For the year Ended 31 December			2018
	Revenues:
	Daycare and overnight care revenues		$ 270,000.00
	Basic grooming maintenance for multi-day stay care		$ 130,000.00
	Gross profit					$ 400,000.00
	Operating Expenses:
	Advertising				$ 1,400.00
		Utilities									$ 6,000.00
	Wages and Salaries							$ 40,000.00
		Insurance					$ 4,000.00
Operating lease ($4000 per month)				$ 4,800.00
		Repair and maintenance								$ 70,000.00
			Depreciation expense							$ 5,000.00
Miscellenous expenses
	Total expenses		$ 137,200.00
Operating			Net Income				$ 262,800.00
	Income before taxes
	Tax expense		30%		$ 78,840.00
			$ 183,960.00

Pro Forma Income statement

B&B FURLING BOARDING
Income statement

Revenues:

2018

2020

Daycare and overnight care revenues $ 270,000.00

$ 400,000.00

Basic grooming maintenance for multi-day stay care $ 130,000.00

Gross profit $ 400,000.00

Operating Expenses:

Advertising $ 1,400.00

Utilities $ 6,000.00 $ 6,000.00 $ 6,000.00
Wages and Salaries $ 40,000.00 $ 40,000.00 $ 40,000.00
Insurance premiums $ 4,000.00 $ 4,000.00 $ 4,000.00

$ 4,800.00 $ 4,800.00 $ 4,800.00

Repair and maintenance $ 70,000.00 $ 1,500.00

Depreciation expense $ 5,000.00 $ 5,000.00 $ 5,000.00

$ 6,000.00

Total expenses $ 137,200.00

$ 262,800.00

Income before taxes $ 262,800.00 $ 420,500.00 $ 529,200.00
Tax expense 30% $ 78,840.00

Net Income $ 183,960.00

For the year Ended 31 December 2018 through			2020
		2019
$ 320,000.00
	$ 170,000.00						$ 200,000.00
$ 490,000.00	$ 600,000.00
	$ 1,500.00	$ 1,600.00
Operating lease ($400 per month)
$ 1,700.00
	Miscellaneous expenses	$ 6,700.00	$ 7,700.00
$ 69,500.00	$ 70,800.00
Operating Net Income		$ 420,500.00		$ 529,200.00
$ 126,150.00	$ 158,760.00
$ 294,350.00	$ 370,440.00
Percentage change in net income	60%	26%

Balance sheet

B&B FURLING BOARDING

$ 400,000.00

$ 70,000.00

$ 200,000.00

$ 140,000.00

	Balance Sheet
	Ast at 31 December 2018
	ASSETS
	Current Assets:
	Cash
	Accounts Receivable			$ 80,000.00
	Inventories									$ 140,000.00
	Office supplies			$ 20,000.00
	Prepaid Insurance		$ 15,000.00
	Total Current Assets		$ 655,000.00
	Fixed Assets:
	Equipment
	Property
	Accumulated depreciation for property and equipment				$ (5,000.00)
	Total Fixed Assets				$ 265,000.00
	TOTAL ASSETS		$ 920,000.00
	LIABILITIES
	Current Liabilities:
	Allowance for bad debt		$ 50,000.00
	Accounts Payable		$ 47,000.00
	Total Current Liabilities		$ 97,000.00
	Long term Liabilities:
	Bank Loan
	Interest payable					$ 13,000.00
			$ 153,000.00
	Total Liabilities	$ 250,000.00
	OWNER’S EQUITY:
Lyndsey Capital				$ 500,000.00
	Retained earnings		$ 220,000.00

Proforma balance sheet

B&B FURLING BOARDING
Balance Sheet
Ast at 31 December 2018
2018 2019 2020
ASSETS
Current Assets:

Cash $ 400,000.00

Accounts Receivable $ 80,000.00

Inventories $ 140,000.00

Office supplies $ 20,000.00

Prepaid Insurance $ 15,000.00

$ 5,000.00

Total Current Assets $ 655,000.00

Fixed Assets:

Equipment $ 70,000.00 $ 70,000.00 $ 70,000.00
Property $ 200,000.00 $ 200,000.00 $ 200,000.00
Accumulated depreciation for property and equipment $ (5,000.00) $ (5,000.00) $ (5,000.00)
Total Fixed Assets $ 265,000.00 $ 265,000.00 $ 265,000.00
TOTAL ASSETS $ 920,000.00

LIABILITIES
Current Liabilities:

Allowance for bad debt $ 50,000.00

Accounts Payable $ 47,000.00 $ 195,000.00 $ 170,000.00
Total Current Liabilities $ 97,000.00

Long term Liabilities:

Bank Loan $ 140,000.00 $ 140,000.00 $ 140,000.00
Interest payable $ 13,000.00 $ 13,000.00 $ 13,000.00
$ 153,000.00 $ 153,000.00 $ 153,000.00
Total Liabilities $ 200,000.00

OWNER’S EQUITY:

$ 500,000.00 $ 500,000.00 $ 500,000.00

Retained earnings $ 220,000.00

$ 550,000.00	$ 610,000.00
$ 150,000.00	$ 160,000.00
$ 180,000.00		$ 195,000.00
$ 21,000.00	$ 21,500.00
$ 10,000.00
$ 911,000.00	$ 991,500.00
$ 1,176,000.00	$ 1,256,500.00
$ 15,300.00	$ 42,000.00
$ 210,300.00	$ 212,000.00
$ 348,000.00	$ 323,000.00
Lyndsey capital
$ 328,000.00	$ 433,500.00

Cash flow statement

B&B FURLING BOARDING

Cash flow statement
Net Income $ 183,960.00

Depreciation expense $ 5,000.00

$ 40,000.00

$ 80,000.00

$ 140,000.00

$ 20,000.00

$ 1,400.00

$ 70,000.00

$ 6,000.00

$ 210,000.00

$ 140,000.00

$ 140,000.00

For the period of one year
CASH FLOW FROM OPERATING ACTIVITIES
Adjustments for net income:
Increase in cash
Increase in accounts receivable
Increase in inventory
Increase in supplies
Decrease/Increase in advertising expense
Decrease/Increase in maintenance expense
Decrease in miscellaneous expense
$ 362,400.00
Net cash provided by operating activities	$ 546,360.00
CASH FLOWS FROM INVESTING ACTIVITIES
Renovations of building		$ 210,000.00
Net cash used in investing activities
CASH FLOW FRM FINANCING ACTIVITIES
Bank loan
Net cash by financing activities
Net Cash Increase	$ 896,360.00

Break-even point

$ 183,960.00

$ 40,000.00

$ 40,000.00

$ 1,400.00

Utilities $ 6,000.00
Insurance $ 4,000.00
Depreciation expense $ 5,000.00
Miscellaneous expenses $ 6,000.00
Repair and maintenance $ 70,000.00

$ 13,000.00

Break-even Point
Total revenue
Variable costs:
Wages and salaries
Total variable costs
Contribution margin	$ 143,960.00
Contribution margin ratio	78%
Fixed Costs:
Advertising expense
Interest expense
Total fixed costs	$ 105,400.00
Profits margin	$ 38,560.00
Break-even point	$ 134,685.91

Sales

and expenses forecast

Sales and expenses forecast
Sales 2018 2019 2020

$119,400.00

Day care

Overnight care	$200,000.00	$92,000.00	$113,000.00
	Day care	$70,000.00	$110,300.00		$119,400.00
Grooming	$130,000.00	$131,200.00
Total Sales	$400,000.00	$238,900.00	$261,900.00
Direct cost of sales
Overnight care costs	$41,000.00	$46,000.00	$51,000.00
$67,000.00	$71,000.00	$87,000.00
Grooming costs	$35,000.00	$3,900.00	$4,500.00
Total costs	$143,000.00	$120,900.00	$142,500.00