Question

Prepare a monthly budgeted cash flow, income statement and balance sheet:

Sales Projection for next 3 months

October	$30,000
November	$70,000
December	$50,000

75 % of sales are expected to be Collected in current month and the remaining 25% to be collected in the following month.

Cost of Goods equals =45% of sales (Materials) 15% of sales (direct labor) 15% of sales (subcontractors)

*Materials and subcontractors and paid in the following month while direct labor is paid in the month worked*

The owners Debbie and Mark will deposit $20,000 in the bank at the beginning of October. $100 of these funds will be used to pay for their common shares and the remaining $19,900 will be set up as a loan from the share holder with no interest and no set terms of repayment.

The corporate income tax is 17%

Operating Expenses

Depreciation

Truck	cost $38,000, useful life 5 years, residual value 20% of costs (sold to the company by the owners in exchange for a non-interest baring share holder loan)
Tools	cost $18,000, useful life 8 years (sold to the company by the owners in exchange for a non-interest baring share holder loan)
Office Furniture	cost $5000, useful life 3 years
Building	Cost $50,000 useful life 20 years

Other expenses

Insurance	Quote received $6900 for property & liability and quote received $1500 for vehicle
Interest on long term debt	Mortgage on building- Full amount required/ Bank quoted 4.25% for 20 years amortization. Refer to the loan amort. schedule included below
property taxes	Re building- annual taxes $420

Other Operating Expenses

Administrative salaries	October 4,800.00 November 3900.00 December 3500.00
Accounting & legal	October 4,000.00
Advertising & promotions	October 3,000.00 November 1000.00 December 1000.00
Business fees & licenses	$ -
Depreciation Expenses	$
Insurance	$
interest & bank charges	$                                                  35.00 per month
interest on long term debt	$                                                         -
office supplies	$                                                100.00 for October 25 each month after
property tax	$ 420 annually
motor vehicles expenses	$                                                450.00 per month
repair & maintenance
telephone	$                                                165.00 per month
travel & entertainment	$                                                  40.00 per month
Utilities	$                                                250.00 per month

Loan Calculator

Loan amount $50,000.00

Annual interest rate 4.25%

Loan period in years 20

Start date of loan 10/01/2019

Monthly payment $309.62

Number of payments 240

Total interest 24308.14

total cost of loan 74308.14

No 1	11/01/2019 (payment date)	50,000 (beg balance)	309.62 (Payment)	132.53 (Principal)	177.08 (interest)	49867.47 (ending balance)
2	12/1/2019	49867.47	309.62	133.00	176.61	49734.46
3	1/01/2020	49734.46	309.62	133.47	176.14	49600.99

All operating expenses are to be paid in the month following the purchase with the exception of the following shich do not have credit terms:

insurance, property taxes, business fees and licenses and interest and bank charges

Answer 1

This is a huge task to solve this problem, it took almost 1.5 hour hence no other person would have tried to attempt it. Please rate positively for my efforts :

Income Statement
Months	October	November	December
Sales	30,000.00	70,000.00	50,000.00
Total Revenue	30,000.00	70,000.00	50,000.00
Expenses
Materials (45% of Sales)	13,500.00	31,500.00	22,500.00
Direct Labour (15% of Sales)	4,500.00	10,500.00	7,500.00
Labour to sub-contractor (15% of Sales)	4,500.00	10,500.00	7,500.00
Depreciation - Truck ((38000-38000*20%)/5/12)	506.67	506.67	506.67
Depreciation - Tools (18000/8/12)	187.50	187.50	187.50
Depreciation - office furniture (5000/3/12)	138.89	138.89	138.89
Depreciation - building (50000/20/12)	208.33	208.33	208.33
Admin Expenses	4,800.00	3,900.00	3,500.00
Accounting & legal	4,000.00
Advertising and promotion	3,000.00	1,000.00	1,000.00
Interest & bank charges	35.00	35.00	35.00
Office Supplies	100.00	25.00	25.00
Property Taxes	35.00	35.00	35.00
Motor Vehicle expenses	450.00	450.00	450.00
telephone	165.00	165.00	165.00
travel & entertainment	40.00	40.00	40.00
Utilities	250.00	250.00	250.00
Insurance	700.00	700.00	700.00
Interest	177.08	176.61	176.14
Total Expenses	37,293.47	60,318.00	44,917.53
Profit before Tax	(7,293.47)	9,682.00	5,082.47
Tax @ 17%	1,239.89	(1,645.94)	(864.02)
Profit after Tax	(6,053.58)	8,036.06	4,218.45

Balance Sheet
Assets	October	November	December
Cash	23,835.39	42,185.78	41,511.17
Account Receivable (25% of Sales)	7,500.00	17,500.00	12,500.00
Truck (38000-Cumulative Depreciation)	37,493.33	36,986.67	36,480.00
Tools (18000 -Cumulative Depreciation)	17,812.50	17,625.00	17,437.50
Office Furtniture (5000-Cumulative Depreciation)	4,861.11	4,722.22	4,583.33
Building (50000-Cumulative Depreciation)	49,791.67	49,583.33	49,375.00
Prepaid Expenses - Insurance (6900+1500-Cumulative expensed out)	7,700.00	7,000.00	6,300.00
Prepaid Expenses - Property Tax (420 -Cumulative expensed out)	385.00	350.00	315.00
Total Assets	149,379.00	175,953.00	168,502.00
Liabilities
Accounts Payable-Material (Current month Cost)	13,500.00	31,500.00	22,500.00
Accounts Payable-Subcontractor (Current month Cost)	4,500.00	10,500.00	7,500.00
Accounts Payable-Operating Expenses (Current Month Expenses)	12,805.00	5,830.00	5,430.00
Common Stock	100.00	100.00	100.00
Loan from Shareholder (19900+38000+18000)	75,900.00	75,900.00	75,900.00
Bank Loan (50000-cumulative principal repayment)	49,867.47	49,734.47	49,601.00
Retained Earning	(6,053.58)	1,982.48	6,200.93
Corporation tax payable	(1,239.89)	406.05	1,270.07
Total Liabilities	149,379.00	175,953.00	168,502.00

Cash Flow	October	November	December
Opening Balance	-	23,835.39	42,185.78
Net Profit After Tax	(6,053.58)	8,036.06	4,218.45
Add: Depreciation	1,041.39	1,041.39	1,041.39
Increase in Current Liabilities	29,565.11	18,670.94	(11,535.98)
Increase in Current Assets	(15,585.00)	(9,265.00)	5,735.00
Investing Activities
Purchase of Fixed Assets	(111,000.00)	-	-
Financing Activities
Increase in Common Stock	100.00	-	-
Loan from Shareholder	75,900.00	-	-
Bank Loan	49,867.47	(133.00)	(133.47)
Cash Flow during the year	23,835.39	18,350.39	(674.61)
Clsoing Cash Balance	23,835.39	42,185.78	41,511.17