AI Homework Help

Upload for Unlocks

Expert Help

Study Resources

2

L

Waiting for tutor's clarification

Question received

Finding tutors

Tutors are reviewing your question

Your tutor is working on your answer

Waiting for clarification

Your question:

Course: ACC FNS50217

This business is registered for GST and reports GST on the accruals basis to the ATO each quarter. The Hourly rate charged to customers is 165 (incl GST). Bettys busiest during the 4 months, (July - Oct) and earns more than 50% of the annual income in this period. From July to October Betty employees staff. Bettys hrs in July 150 . August 200 and September 180. In the second quarter Betty expects October's hours to be the same as in August but then things start to quieten down in November and December. The hours will drop from November onwards and Bettys expects November's hrs to be 50% less than the hrs in Oct and Dec's hrs to be 50% less than the hours in November. Betty takes 1 month off over Dec and Jan for christmas and the hrs in Jan are the same as the hrs in Dec. The hrs each month from Jan onwards are very similar expect for the two months when the BAS is due for her clients. Feb & June. Usually charges are 50 hrs per month for the third and fourth quarter, with the hrs in Feb and June being 100% more the other months. Collection rates for accounts rec are 50% in the current month charged. 40% in the following month and 10% in the second month. The revenue for May was 7700 and June 11000 including GST. closing balance is not to fall below $25000. Following figures are for the year. Advertising 9975. Depreciation 14000 15% diminishing but on last years balance sheet -28600 . Insurance 5974 . Utilities 4104. Rent 40392. Wages 17100. Office Expenses 5760. Bank Fees 1176. Interest on loan 26400. revenue, accounts receivable collection schedule, operating, GST and cash budget, income statement and balance sheet.

I need a budget done for each month for the whole year in excel template

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
Hours	150	200	180	200	100	50	50	100	50	50	50	50	1,230
Price	$150	$150	$150	$150	$150	$150	$150	$150	$150	$150	$150	$150
Revenue	$22,500	$30,000	$27,000	$30,000	$15,000	$7,500	$7,500	$15,000	$7,500	$7,500	$7,500	$7,500	184,500
Quarter Total	$79,500	$52,500	$30,000	$22,500	184,500
Revenue / Sales	May	June
	7700	11000
Accounts Receivable Collection Schedule
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Revenue	$24,750	$33,000	$29,700	$33,000	$16,500	$8,250	$8,250	$16,500	$8,250	$8,250	$8,250	$8,250	$202,950
GST	$2,250	$3,000	$2,700	$3,000	$1,500	$750	$750	$1,500	$750	$750	$750	$750	$18,450
Collections @ 50%	$12,375	$16,500	$14,850	$16,500	$8,250	$4,125	$4,125	$8,250	$4,125	$4,125	$4,125	$4,125	$101,475
Collections @ 40%	$4,400	$9,900	$13,200	$11,880	$13,200	$6,600	$3,300	$3,300	$6,600	$3,300	$3,300	$3,300	$82,280
Collections @ 10%	$770	$1,100	$2,475	$3,300	$2,970	$3,300	$1,650	$825	$825	$1,650	$825	$825	$20,515
Total cash collected	$17,545	$27,500	$30,525	$31,680	$24,420	$14,025	$9,075	$12,375	$11,550	$9,075	$8,250	$8,250	$204,270
Quarter Collections	$75,570	$70,125	$33,000	$25,575	$204,270
Operating Expenses Budget
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Selling Expenses
Advertising	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$11,025
Total Selling	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$11,025
Admin Expenses
Depreciation	$993	$993	$993	$993	$993	$993	$993	$993	$993	$993	$993	$993	$11,916
Insurance	$498	$498	$498	$498	$498	$498	$498	$498	$498	$498	$498	$498	$5,974
Utilities	$342	$342	$342	$342	$342	$342	$342	$342	$342	$342	$342	$342	$4,104
Rent	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$40,392
Wages	$3,135	$3,135	$3,135	$3,135									$12,540
Office expenses	$461	$461	$461	$461	$461	$461	$461	$461	$461	$461	$461	$461	$5,535
Total Admin	$7,802	$7,802	$7,802	$7,802	$4,667	$4,667	$4,667	$4,667	$4,667	$4,667	$4,667	$4,667	$68,545
Financial Expenses
Bank Fees	$102	$102	$102	$102	$102	$102	$102	$102	$102	$102	$102	$102	$1,224
Interest on loan	$2,200	$26,400	$26,400	$26,400	$26,400	$26,400	$26,400	$26,400	$26,400	$26,400	$26,400	$26,400	$292,600
Total Financial	$2,302	$2,302	$2,302	$2,302	$2,302	$2,302	$2,302	$2,302	$2,302	$2,302	$2,302	$2,302	$27,624
Total	$11,023	$11,023	$11,023	$11,023	$7,888	$7,888	$7,888	$7,888	$7,888	$7,888	$7,888	$7,888	$107,194
Quarter Total	$33,069	$26,799	$23,664	$23,664	$107,194
Cash Budget
									Income Statement
																Revenue		$160,000
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				Less Operating Expenses
Cash Inflow																	Selling Expenses
Accounts Receivable	$17,545	$27,500	$30,525	$31,680	$24,420	$14,025	$9,075	$12,375	$11,550	$9,075	$8,250	$8,250	$204,270
Total Inflow	$17,545	$27,500	$30,525	$31,680	$24,420	$14,025	$9,075	$12,375	$11,550	$9,075	$8,250	$8,250	$204,270
Cash Outflow
Advertising	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$919	$11,025				Advertising	$10,500	$10,500
Insurance	$498	$498	$498	$498	$498	$498	$498	$498	$498	$498	$498	$498	$5,974				Administration Expenses
Utilities	$342	$342	$342	$342	$342	$342	$342	$342	$342	$342	$342	$342	$4,104				Depreciation	$14,000
Rent	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$3,366	$40,392				Insurance	$5,800
Wages	$3,135	$3,135	$3,135	$3,135	$0	$0	$0	$0	$0	$0	$0	$0	$12,540				Utilities	$3,800
Office expenses	$461	$461	$461	$461	$461	$461	$461	$461	$461	$461	$461	$461	$5,535				Rent	$39,600
Bank Fees	$102	$102	$102	$102	$102	$102	$102	$102	$102	$102	$102	$102	$1,224				Wages	$18,000
Interest on loan	$2,200	$2,200	$2,200	$2,200	$2,200	$2,200	$2,200	$2,200	$2,200	$2,200	$2,200	$2,200	$26,400				Office expenses	$15,261	$96,461
GST payable	$2,850	$1,738	$2,949	$2,649	$2,949	$1,449	$699	$699	$1,449	$699	$699	$699	$19,525				Financial Expenses
Total Outflow	$13,873	$12,760	$13,972	$13,672	$10,837	$9,337	$8,587	$8,587	$9,337	$8,587	$8,587	$8,587	$126,719
Net Inflow/Outflow	$3,672	$14,740	$16,553	$18,008	$13,583	$4,688	$488	$3,788	$2,213	$488	-$337	-$337	$77,551				Bank fees	$1,200
Quarter Inflow/Outflow	$34,965	$36,280	$6,490	-$185	$77,551
Opening Cash	$50,000	$53,672	$68,412	$84,965	$102,974	$116,557	$121,245	$121,734	$125,522	$127,736	$128,224	$127,888	$50,000				Interest on loan	$26,400	$27,600
Net Inflow/Outflow	$3,672	$14,740	$16,553	$18,008	$13,583	$4,688	$488	$3,788	$2,213	$488	-$337	-$337	$77,551				Net Profit		$25,439
Closing Cash	$53,672	$68,412	$84,965	$102,974	$116,557	$121,245	$121,734	$125,522	$127,736	$128,224	$127,888	$127,551	$127,551
																	Balance Sheet:
																	Balance Sheet
																	Current Assets
																	Cash		$22,619
GST Budget																	Accounts Receivable		$6,270	$28,889
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total				Non-Current Assets
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun				Equipment	$108,000
GST collected	$2,250	$3,000	$2,700	$3,000	$1,500	$750	$750	$1,500	$750	$750	$750	$750	$18,450				Accumulated Depreciation Equipment	-$28,600	$79,400
Less GST Paid on:																	Total Assets			$108,289
Advertising	$92	$92	$92	$92	$92	$92	$92	$92	$92	$92	$92	$92	$1,103				Current Liabilities
Insurance	$50	$50	$50	$50	$50	$50	$50	$50	$50	$50	$50	$50	$597				GST Payable		$2,850
Utilities	$34	$34	$34	$34	$34	$34	$34	$34	$34	$34	$34	$34	$410				Non-Current Liabilities		$30,000
Rent	$337	$337	$337	$337	$337	$337	$337	$337	$337	$337	$337	$337	$4,039				Loan
Total GST paid	$512	$51	$51	$51	$51	$51	$51	$51	$51	$51	$51	$51	$1,076				Total Liabilities			$32,850
Total GST Payable	$1,738	$2,949	$2,649	$2,949	$1,449	$699	$699	$1,449	$699	$699	$699	$699	$17,374				Net Assets			$75,439
Quarter GST Paid	$615	$154	$154	$154	$1,076
																	Capital
																	Opening Capital		$50,000
Income Statement Budget																	Net Profit		25439	$75,439
Quarter	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
Month	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Revenue	$22,500	$30,000	$27,000	$30,000	$15,000	$7,500	$7,500	$15,000	$7,500	$7,500	$7,500	$7,500	$184,500
Less Operating Expenses	$11,023	$11,023	$11,023	$11,023	$7,888	$7,888	$7,888	$7,888	$7,888	$7,888	$7,888	$7,888	$107,194
Net Profit	$11,477	$18,977	$15,977	$18,977	$7,112	-$388	-$388	$7,112	-$388	-$388	-$388	-$388	$77,306
Quarter Net Profit	$46,432	$25,702	$6,337	-$1,164	$77,306
			Balance Sheet
						Current Assets
						Cash		$127,551
						Accounts Receivable	5178	$132,729
						Non-Current Assets
						Equipment	$108,000
						Accumulated Depreciation Equipment	-$40,516	$67,484
						Total Assets			$200,213
						Current Liabilities
						GST Payable		$17,374
						Non-Current Liabilities		$30,000
						Loan
						Total Liabilities			$47,374
						Net Assets			$152,839
						Capital
						Opening Capital	$75,439
						Net Profit		$77,306	$152,745	$94

The figures in this one do not calcuate.