Hi, I need the formula for calculations on excel please. Thanks in advance
FNSACC412_AT2_PE_TQOL_V2.1.docx Mandatory requirement: Students are required to complete Budget on the provided Excel spreadsheet template. (For the purpose of Budget calculation in the assessment, ignore all GST calculation) Task 1 Below you are provided with details of two companies and their budgets. Prepare the budgets and complete the following tasks: A. From the following information relating to Marmax Consulting Services, prepare a budgeted Income Statement (Budget 1) for the period from January to December 2020, showing the profit level required by management. (Note: Please refer to the Extract of Policies & Procedures in Appendix A on page 8): a. The budgeted fees estimate is expected at 100 per cent b. Only 80 per cent of the fees budget estimate is achieved c. Nominate at least 1 Key Performance Indicator (KPI) in this budget The fees budget shows a budgeted level of 1000 consultancy hours per month at $100 per hour, of which the hourly rate has been set at a budgeted level at least 10% lower than the average market rate in the industry. Expenses are as follows: · Advertising is a fixed amount of $4,500 per annum plus 1.25 per cent of fees · Office wages $2,000 per month · Office supplies is fixed amount of $2,100 per annum plus 10 cents per consultancy hour · Staff salaries $8,500 per month · Staff commission 4 per cent of fees · Vehicle expenses $15,300 per annum plus 0.25 per cent of fees · Interest expense 15.5 per cent per annum of a loan of $20,000 · Legal fees $1,000 per month plus 0.7 per cent of fees. · Rates $12,900 per annum · Income tax 33 cents in the dollar · Fixed costs include: Office wages; Staff salaries; Rates. B. Prepare a performance report for the above income statement (in Question A) for the year ended December 2020, showing Income, total expenses, profit before tax, and all variances from the budget in dollars and percentages. C. Write a formal short report for the General Manager of Marmax Consulting Services on the performance of the actual budget. The report must be provided to the General Manager within one month of the close of End of Year. (Note: Follow the Oliver Trading short report format in Supplementary Resources) D. The General Manager John Jacobs at Marmax Consulting Services was not happy with the performance for the year 2020 because only 80% of fees income has been achieved, this represented a 20% gap comparing to the original 100% budgeted target. John realised that the original target might have been over-estimated and not achievable in current market share. However, John would like to improve the situation by reducing the gap from original target to less than 10% if possible, so hopefully this will increase the overall profit level. John has re-established a new estimated budget for the new year 2021, he has decided to increase the overall consultancy fees based on the actual income in year 2020 by 14%. John believes the total consultancy hours in the new year will remain at the same level as in year 2020. John also believes that some of the expenses in the new year would increase (ie. increase from year 2020’s actual expenses) as follows: · Advertising expense to increase by 6% without any fixed amount. · Staff Salaries to increase by 2% · Staff commission to increase by 1.5% · Vehicle expenses would increase by 4% without any fixed amount. · All other expenses remained the same as in year 2020’s actual expenses. · The consultancy service fees also increase and John has estimated the seasonal service fees breakdown in the four quarters in year 2021 to be as follows: Seasonal Variation Quarter 1 Quarter 2 Quarter 3 Quarter 4 100% of annual consultancy Fees 25% 20% 30% 25% E. Using the above new estimated budget prepared in task D for year 2021 determine the year’s budget objective and provide service fees and the service fees milestones for each quarter. The new year’s budget objective is___________________________________________________ ______________________________________________________________________________ Milestones per quarter for seasonal service fees changes are as follows: Quarter 1: ______________________________________________________________________ Quarter 2: ______________________________________________________________________ Quarter 3: ______________________________________________________________________ Quarter 4: ______________________________________________________________________ Task 2 From the following information relating to Bailey’s Manufacturing Pty Ltd: a Prepare the budgeted Income Statement (Budget 2) for the three months Quarter ending December 2020. Actuals for this budget have not been determined at this time. b Nominate at least 1 Key Performance Indicator (KPI) in this budget Baileys Manufacturing Pty Ltd Sales Budget Unit Sales Unit Price Sales revenue October 1,000 pcs. $150 $150,000 November 1,200 pcs. $150 $180,000 December 1,300 pcs. $180 $234,000 Baileys Manufacturing Pty Ltd Production Budget Unit Sales Closing Stock Total required Opening Stock Estimated Production October 1,000 pcs. 50 pcs. 1,050 pcs. 150 pcs. 900 pcs. November 1,200 pcs. 70 pcs. 1,270 pcs. 50 pcs. 1,220 pcs. December 1,300 pcs. 90 pcs. 1,390 pcs. 70 pcs. 1,320 pcs. The cost of production budget shows the manufacturing cost per unit as follows: October$88 November$90 December$95 Baileys Manufacturing Pty Ltd Operating expenses budget Expense October November December Total Selling $9,000 $9,500 $9,000 $27,500 Administration $1,000 $2,000 $1,500 $4,500 Financial $2,000 $2,000 $3,000 $7,000 Opening stock for the month of October only is valued at $88 per unit. The tax rate is 30 per cent Bailey’s Manufacturing Pty Ltd use Average cost for stock valuation Task 3 With reference to the two budgeted income statement budgets prepared in above task 1 & task 2, and from the Actual results presented in Budget 1 and quarterly reporting for budget 2 . What are the major differences between Budget 1: Marmax Consulting Services and Budget 2: Bailey’s Manufacturing Pty Ltd? Task 4 Research task: (All answers to be typed in a word document) Please use supplementary resources and other accredited library and internet tools. You are required to provide detailed responses including examples to support your explanations to questions below. One or two line responses will not be acceptable. 1. What is a variance? Describe favourable and unfavourable variances. What action would you take if there was a very large variance? 1. Statistical analysis is a way of looking at the correlation (relationships or trends) between two or more variables. How could statistical analysis be used in budget reporting? 1. Name and describe three forecasting techniques 1. How often should the budget be monitored? When must budget reports be prepared? When must financial statements be prepared? Reference the organisation’s policies and procedures. 1. In order for a budget to be accurately completed, it is important to be able to understand the terms “Cash”, “Revenue” and “Expenses”. Define these three terms. APPENDIX A EXTRACT – POLICIES & PROCEDURES Procedure for Financial Administration and Budgeting Policy Marmax Consultancy Services Company has implemented this policy to ensure that financial administration is completed in accordance with legal, ethical and accounting requirements. Processing of financial transactions All Employees must ensure financial transactions are recorded accurately and timely. Confidentiality Employees should perform their duties with honesty and integrity. Financial Reporting Financial statements should be prepared in accordance with the Australian Accounting Standards AASB101. Accrual basis All transactions should be prepared on an accruals basis and are based on historical costs. Budgets Marmax Consultancy Services Company budget process will include consultation and communication with all departmental managers. Quantitative forecasting methods are applied using statistical measures to project future outcomes and trends. Forecasting techniques also include internal/controllable factors and external /uncontrollable factors. Final results are purely affected by the numbers inputted and therefore, a high degree of accuracy of financial reporting is required by the Board of Directors Set Timelines Budgets will be monitored monthly to identify all Key Performance Indicators. All Budgets must be prepared 6 months prior to the start of the next financial year. Budgetary Control The Board of directors prepares a strategic plan for the future. This is the most important feature of a budgetary planning and control system. The plan enables the board of directors to: · Look ahead, set out detailed plans to achieve the targets for each department, operation and (ideally) each manager, anticipate and give the organisation purpose and direction. · Promote coordination and communication with the divisional mangers. · Clearly define areas of responsibility. So all managers responsible for the budget are made accountable to achieve budget targets for the operations under their personal control. The Board of Directors applies control techniques by comparing actual results with budgets. Variances from the budget should be investigated. The reasons for the differences can be divided into controllable and non-controllable factors and prompt remedial action should be taken for variances. The budgeting process is also used to motivate staff, improve resources allocation processes and time management by using the management by exception principle for Variance reporting. Performance review Significant performances (plus or minus 2.5%) and/or financial variances are to be reported monthly as a formal short report to the Board of directors. This report should include a description of the variance, the reasons behind it, the steps taken to remedy it (if required) and future steps to be taken to prevent the variance from recurring. From the monthly variance reports, all significant variances a will be sent to the key manager who can exercise, monitor and find the reasons for the variance and make revisions to the budgets with the approval of the Board of directors Key Performance Indicator (KPI), is another tool Marmax Consultancy Services Company Board of Directors use to keep track of a company's financial performance. KPIs are measures by which the Board can monitor its progress throughout a specified period of time, namely a financial year. The KPIs also help the directors to plan future spending. Focus in areas in the business that may need improvement, identify any opportunities and challenges and assess whether the customers are happy or not. The KPIs fixed to monitor financial performance are: Revenue Growth Revenue per customer segment (credit and Cash), Profitability: Gross profit margins & Net profit margins Working Capital Ratio: Current Assets, Current Liabilities 2:1 Debtors ageing Creditors ageing Net Cash Flow Exceptional Circumstances If circumstances arise internally/externally during the financial year, which may have a significant impact on current financial year budget estimates, a revised budget is to be prepared immediately for the Board of directors’ approval. Errors and Omissions All errors and omissions detected in financial reports must be referred to the company accountant or immediate supervisor before any corrections are attempted. 123 TMP C – Assessment Template – Portfolio of Evidence v1.3 (04/11/2020)Page 1 of 3 123 TMP C – Assessment Template - Portfolio of Evidence v1.3 (04/11/2020)Page 2 of 3 Task1 A & D (Task 1A)