Establish a Budget and Allocate Funds

Activity 2 –Establish a Budget and Allocate Funds John Smith, the CEO, has asked you to prepare some projected financial budgets for the 2017/2018 financial year as a preliminary overview of the financial year ahead. He asked you to first prepare a 12 months budget and then break it up over the four quarters. The areas he is particularly interested in seeing is: Forecasted sales budget for 2017/2018 by year and quarter. (Statement of Financial Performance Aka Profit & Loss report)Forecasted Statement of Financial Position Aka Balance Sheet for Year 2017/2018A cash flow budget. The cash flow result per quarter of the GST after adjusting the GST collected by the allowable GST tax credits. (GST Paid)The anticipated aged debtor’s summary report – yearly and at the end of each quarter. Mr Singh has provided you with access to the previous year’s financial data to analyse. Using this data as well as the business plan summary you are required to prepare a budget for the year and then a quarterly budget. You will need to follow the same format as the previous year’s financial reports. Manual calculations will assist your accounting understanding – although you may like to use excel with formulas if you feel confident. Use the provided table below to complete this assessment. Student Tip: To use excel formulasEnter formulas by = Sum (cell number + cell number) press enter Multiplication is * Divide is: / To assist with increasing by a percentage use the formula = cell number + (cell number * percentage amount and percent symbol)E.g. = D4 + (D4*20%)This will calculate for you any increase for a base amount by a set percentage to give you the new calculation figure. Previous Years Financial Data (Statement of Financial Performance/Profit & Loss Report) Statement of Financial Performance (Profit & Loss Report) -Pax Hotel For 12 months ended 20132013/142014/152015/162016/172017/2018RevenueSales12,474,33613,472,31514,550,10015,714,108Cost of Goods Sold6,860,9017,409,7738,002,5558,799,900Gross Profit5,613,4656,062,5426,547,5456,914,208ExpensesAccounting Fees5,5006,5008,5009,000Interest Expense45,00065,00096,50890,508Bank Charges1,2001,3001,5801,600Depreciation170,000170,000170,000170,000Insurance12,50012,50012,50012,875Advertising50,000100,000280,000280,000Cleaning12,56015,65218,70019,261Repair /Maintenance40,25052,60060,00061,800 Expenses Cont’d Rent2,465,0002,465,0002,465,0002,538,950Telephone9,86212,52314,00014,420Electricity Expense22,50023,65825,00025,750Luxury Car Tax12,400Fringe Benefits Tax26,00026,00026,00028,000Superannuation148,500160,737166,500171,495Wages & Salaries1,649,9981,785,9651,850,0001,905,500Payroll Tax78,37584,83387,87590,511Workers’ Compensation33,00035,71937,00038,110Total Expenses4,770,2455,017,9875,331,5635,457,780Net Profit (Before Company Tax)843,2201,044,5541,215,9821,456,428Company/Income Tax252,966313,366364,795436,928Net Profit after tax590,254731,188851,1881,019,499 Statement of Financial Position/ Balance Sheet Pax Hotel Statement of Financial Position As at 30 June2015/162016/172017/2018AssetsCurrent AssetsCash on Hand50,00055,000Cheque Account144,842160,314Deposits Paid950,000950,000Trade Debtors850,000975,000Merchandise Inventory1,530,0001,430,000Total Current AssetsFixed AssetsMotor Vehicles at Cost500,000500,000Motor Vehicles Accum Dep(100,000)(125,000)Furniture & Fixtures at Cost1,950,0002,250,000Furniture & Fixtures Accum Dep(650,000)(770,000)Office Equip at Cost400,000400,000Office Equip Accum Dep(90,000)(115,000)Total Fixed Assets2,010,0002,140,000Total Assets5,534,8425,710,314 LiabilitiesCurrent LiabilitiesMasterCard17,80014,860Trade Creditors780,000679,000GST Collected1,455,0101,571,411GST Paid(943,125)(987,626)Superannuation Payable100,000120,000Luxury Car Tax Payable20,920–income Tax Payable364,795436,928PAYG Withholding Payable65,00044,872Total Current Liabilities1,860,4001,879,445Long-Term Liabilities––Bank Loans1,608,4591,508,459Total Liabilities3,468,8593,387,904EquityOwner/Shareholder’s Equity500,000500,000Retained Earnings850,0001,565,982Dividends Paid(500,000)(1,200,000)Current Year Earnings1,215,9821,456,428Total Equity2,065,9822,322,410 Student Declaration: I declare that this is my own work, and it has not been copied from any person or source. I am also aware of my appeal rights.Name:________________________ Signature:________________________ Date: ____/_____/_____ Assessor Use Only CommentsResult (Please Circle)SatisfactoryNot SatisfactoryAssessor: I declare that I have conducted a fair, valid, reliable and flexible assessment with this student, and I have provided appropriate feedbackAssessor: ____________________________ Signature: ____________________________ Date: ____/_____/_____