THIS MUST BE IN EXCEL DOCUMENT. PLEASE ATTACH WHEN FINISHED THE LINK TO EXCEL DOCUMENT. REQUIRED REVENUE BUDGET, COST OF GOODS SOLD, PURCHASES AND ACCOUNTS RECEIVABLE COLLECTION SCHEDULE BUDGET, OPEARTING EXPENSES, GST, CASH, INCOME STATEMENT BALANCE SHEET BUDGET.

It needs to be done in month format. June, July, August, Sept, Oct, Nov, Dec, Jan, Feb, Mar, April, May, June and Quarter at the end.

Need to be done in excel.

Happy Apple is owned 60% by the Toohey family and 40% by other shareholders. You have been approached by the Toohey family to prepare a budget for the next financial year. They have just come off a bad year and want to make sure they have sufficient cash to fund their operation through the next year.

Happy Apple grows apples and, due to the nature of the growing cycle, they only collect revenue in the last quarter of the financial year when the apples are harvested and sold. Apple sales are spread evenly over those three months. Apples are sold on credit and the cash is received the month after sale.

After the poor harvest and revenue from last year, Happy Apple has a lower than necessary cash balance to cover their expenses up until the final quarter when they can harvest and sell their apples. To cover this, Happy Apple can obtain an interest only loan from their bank with interest paid at the start of each month at a rate of 6% per annum, to ensure they have enough cash flow for the year. This loan would be paid out in full in June. Happy Apple would like you to forecast the required loan amount to ensure the closing cash balance does not fall below $10,000 at any month during the year. Happy Apple have a strict policy that the cash balance is always to be greater than $0.

Because of their growing cycle, some of Happy Apple's expenses are only applied at certain times of the year. Pruning is all done in August while the fertiliser is purchased and used half in October and half in November. Happy Apple always like to have some fertiliser on hand and have told you they wish to have $8,000 worth of stock on hand from when they make purchases in October right through to the end of the financial year. The salesman only works in February and March and then the pickers and cartage expenses are all spread evenly across the final quarter. The rest of the expenses are spread evenly throughout the year.

To help improve cash flow at the start of the year, Happy Apple reports and pays GST monthly on the accruals basis. The fertiliser, cartage, utilities and repairs and maintenance expenses are all GST supplies. In addition, the crop insurance they are planning to take out this year will also be a GST supply.

Previous years income statement and balance sheet.

This is assignment 1.

NEEDS TO BE IN EXCEL LINK. AS SAMPLE BELOW All information is supplied

														Checking Figures Column
Revenue Budget
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Revenue	$0	$0	$0	$0	$0	$0	$0	$0	$0	$138,600	$138,600	$138,600	$415,800
Quarter Total	$0	$0	$0	$415,800	$415,800
																			YR Figures	Mth Figures	GST Included
COGS Budget		Income Statement	Budget for next year
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total		Revenue		$252,000	Increase by 65%	$415,800	$138,600
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun		Cost of Goods Sold
Pruning		$25,214											$25,214		Pruning	$24,720		Increase by 2%	$25,214	$25,214	Aug Only
Fertiliser				$26,932	$26,932								$53,864		Fertiliser	$52,550		Increase by 2.5%	$53,864	$26,932	1/2 Oct & Nov	$29,625
Pickers										$6,930	$6,930	$6,930	$20,790		Pickers	$15,350	$92,620	5% of revenue	$20,790	$6,930
Total	$0	$25,214	$0	$26,932	$26,932	$0	$0	$0	$0	$6,930	$6,930	$6,930	$99,868	$99,868	Gross Profit		$159,380		$99,868
Quarter Total	$25,214	$53,864	$0	$20,790	$99,868		Operating Expenses
															Selling Expenses
															Cartage	$4,420		2% of revenue	$8,316	$2,772		$3,049.20
Purchases Budget		Salesman Salary	$20,000	$24,420	No change	$20,000	$10,000	Feb & Mar
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total		Administration Expenses
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun		Orchard Manager Salary	$60,000		Increase by 5%	$63,000.00	$5,250
Closing Stock				$8,000	$8,000	$8,000	$8,000	$8,000	$8,000	$8,000	$8,000	$8,000	$72,000		Orchard Staff Salaries	$80,000		Increase by 2%	$81,600.00	$6,800
COGS		$25,214		$26,932	$26,932	$0	$0	$0	$0	$6,930	$6,930	$6,930	$99,868		Utilities	$10,380		Increase by 4%	$10,795.20	$900		$989.56
Opening Stock				$5,000	$8,000	$8,000	$8,000	$8,000	$8,000	$8,000	$8,000	$8,000	$69,000		Rates	$5,210		Increase by 2%	$5,314	$443		$487.14
Purchases		$25,214		$26,932	$26,932	$0	$0	$0	$0	$6,930	$6,930	$6,930	$99,868		Repairs/Maintenance	$14,870		Decrease by 15%	$12,640	$1,053		$1,158.62
Quarter Purchases	$25,214	$53,864	$0	$20,790	$99,868		Deprecation Equipment	$20,000	$190,460	20% diminishing value	$16,000	$1,333
															Financial Expenses
															Bank Fees	$990	$990	Increase by 10%	$1,089	$91
Accounts Receivable Collection Schedule		Net Profit		-$56,490
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total				TOTALS		$834,290	$226,318
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun		Balance Sheet
Revenue										$84,000	$84,000	$84,000	$84,000		Current Assets
GST										$8,400	$8,400	$8,400	$25,200		Cash		$4,810
Collections	$0	$0	$0	$0	$0	$0	$0	$0	$0	$84,000	$84,000	$84,000	$252,000		Accounts Receivable		$84,000
Quarter Collections	$0	$0	$0	$252,000	$252,000		Fertiliser		$5,000	$93,810
															Non-Current Assets
															Land		$2,400,000
Operating Expenses Budget		Buildings		$460,000
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total		Equipment	$250,000
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun		Accumulated Depreciation Equipment	-$170,000	$80,000	$2,940,000
Selling Expenses															Total Assets			$3,033,810
Cartage										$2,772	$2,772	$2,772	$8,316		Current Liabilities
Salesmans Salary								$10,000	$10,000				$20,000		GST Payable		$1,630	$1,630
Total Selling	$0	$0	$0	$0	$0	$0	$0	$10,000	$10,000	$2,772	$2,772	$2,772	$28,316		Net Assets			$3,032,180
Admin Expenses
Orchard Manager	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$63,000		Capital
Orchard Workers	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$81,600		Opening Capital		3088670
Utilities	$900	$900	$900	$900	$900	$900	$900	$900	$900	$900	$900	$900	$10,795				-56490	3032180
Rates	$443	$443	$443	$443	$443	$443	$443	$443	$443	$443	$443	$443	$5,314
Repairs/Maintenance	$1,053	$1,053	$1,053	$1,053	$1,053	$1,053	$1,053	$1,053	$1,053	$1,053	$1,053	$1,053	$12,640
Insurance															BANK LOAN	10000			600
Depreciation	$1,333	$1,333	$1,333	$1,333	$1,333	$1,333	$1,333	$1,333	$1,333	$1,333	$1,333	$1,333	$16,000
Total Admin	$15,779	$15,779	$15,779	$15,779	$15,779	$15,779	$15,779	$15,779	$15,779	$15,779	$15,779	$15,779	$189,349	$189,349
Financial Expenses
Bank Fees	$91	$91	$91	$91	$91	$91	$91	$91	$91	$91	$91	$91	$1,089
Interest	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$7,200
Total Financial	$691	$691	$691	$691	$691	$691	$691	$691	$691	$691	$691	$691	$8,289	$8,289
Total	$16,470	$16,470	$16,470	$16,470	$16,470	$16,470	$16,470	$26,470	$26,470	$19,242	$19,242	$19,242	$225,954	$225,954
Quarter Total	$49,409	$49,409	$69,409	$57,725	$225,954
Cash Budget
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Cash Inflow
Accounts Receivable	$4,810	$0	$0	$0	$0	$0	$0	$0	$0	$84,000	$84,000	$84,000	$256,810
Loan	$883	$883	$883	$883	$883	$883	$883	$883	$883	$883	$883	$883	$10,600
Total Inflow	$5,693	$883	$883	$883	$883	$883	$883	$883	$883	$84,883	$84,883	$84,883	$267,410
Cash Outflow
Pruning		$25,214											$25,214
Fertiliser				$26,932	$26,932								$53,864
Pickers										$6,930	$6,930	$6,930	$20,790
Cartage										$2,772	$2,772	$2,772	$8,316
Salesman	$0	$0	$0	$0	$0	$0	$0	$10,000	$10,000	$0	$0	$0	$20,000
Orchard Manager	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$5,250	$63,000
Orchard Workers	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$6,800	$81,600
Utilities - Incl GST	$990	$990	$990	$990	$990	$990	$990	$990	$990	$990	$990	$990	$11,875
Rates - Incl GST	$487	$487	$487	$487	$487	$487	$487	$487	$487	$487	$487	$487	$5,846
Repairs/Maintenance - Incl GST	$1,159	$1,159	$1,159	$1,159	$1,159	$1,159	$1,159	$1,159	$1,159	$1,159	$1,159	$1,159	$13,903
Insurance - Incl GST													$0
Bank Fees	$91	$91	$91	$91	$91	$91	$91	$91	$91	$91	$91	$91	$1,089
Interest	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$7,200
GST Payable	$1,630	-$195	-$195	-$195	-$2,888	-$2,888	-$195	-$195	-$195	-$195	$7,928	$7,928	$10,341
Repayment Loan												$2,800	$2,800
Total Outflow	$17,006	$40,395	$15,181	$42,113	$39,419	$12,488	$15,181	$25,181	$25,181	$24,883	$33,006	$35,806	$325,838
Net Inflow/Outflow	-$11,313	-$39,512	-$14,297	-$41,229	-$38,536	-$11,604	-$14,297	-$24,297	-$24,297	$60,001	$51,878	$49,078	-$58,428
Quarter Inflow/Outflow	-$65,122	-$91,370	-$62,892	$160,956	$58,428
Opening Cash	$4,810	-$6,503	-$46,015	-$60,312	-$101,541	-$140,077	-$151,682	-$165,979	-$190,277	-$214,574	-$154,573	-$102,696	-$53,618
Net Inflow/Outflow	-$11,313	-$39,512	-$14,297	-$41,229	-$38,536	-$11,604	-$14,297	-$24,297	-$24,297	$60,001	$51,878	$49,078	-$58,428
Closing Cash	-$6,503	-$46,015	-$60,312	-$101,541	-$140,077	-$151,682	-$165,979	-$190,277	-$214,574	-$154,573	-$102,696	-$53,618	-$112,046
GST Budget
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
GST Paid on:
Fertiliser				$2,693	$2,693								$5,386
Cartage										$277	$277	$277	$832
Utlities	$90	$90	$90	$90	$90	$90	$90	$90	$90	$90	$90	$90	$1,080
Repairs/Maintenance - Incl GST	$105	$105	$105	$105	$105	$105	$105	$105	$105	$105	$105	$105	$1,264
Insurance													$0
Total GST Paid	$195	$195	$195	$2,888	$2,888	$195	$195	$195	$195	$472	$472	$472	$8,561
GST Payable	-$195	-$195	-$195	-$2,888	-$2,888	-$195	-$195	-$195	-$195	$7,928	$7,928	$7,928	$16,639
Quarter GST Paid	$586	$5,972	$586	$1,417	$8,561	$8,561
Income Statement Budget
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Revenue	$0	$0	$0	$0	$0	$0	$0	$0	$0	$138,600	$138,600	$138,600	$415,800
COGS	$0	$25,214	$0	$26,932	$26,932	$0	$0	$0	$0	$6,930	$6,930	$6,930	$99,868
Gross Profit	$0	-$25,214	$0	-$26,932	-$26,932	$0	$0	$0	$0	$131,670	$131,670	$131,670	$315,932
Operating Expenses	$16,470	$16,470	$16,470	$16,470	$16,470	$16,470	$16,470	$26,470	$26,470	$19,242	$19,242	$19,242	$225,954
Net Profit	-$16,470	-$41,684	-$16,470	-$43,402	-$43,402	-$16,470	-$16,470	-$26,470	-$26,470	$112,428	$112,428	$112,428	$89,978
Quarter Net Profit	-$74,624	-$103,273	-$69,409	$337,285	$89,978
Balance Sheet Budget
Current Assets
Cash		-$112,046
Accounts Receivable		$126,000
Fertiliser		$53,864	$67,818
Non-Current Assets
Land	$2,400,000
Buildings	$460,000
Equipment	$250,000
Accumulated Deprn- Equip	-$170,000		$2,940,000
Total Assets			$3,007,818
Current Liabilities
GST Payable	$7,928		$7,928
Net Assets			$2,999,890
Capital
Opening Captial	$3,088,670
Net Profit	$89,978		$3,178,648	-$178,758