Please Read Carefully Before start answering this question. Please follow the instructions.
This Question is from'BSBFIM501 Manage budgets and financial plans'course. There are no parts missing for this Question; guaranteed!. This is the original Screenshot direct from the question. Therefore, there are nothing any further information can be provided.Thanks for your understanding and Cooperation.
Part 2: Monitor expenditure and costs You are the Sales Manager for the Wholesale Department at Perfect Plants Pty Ltd. You have a team of nine sales people and one horticulturalist who assists in the filling of specialist orders. You have been given the following departmental budget to follow for the 2017/18 financial year. Wholesale Department Budget 2017/18 Credit Sales Revenue $6,000,000 Cost of Sales ($3,100,000) Wages ($800,000) Telephone ($50,000) Travel ($350,000) Office Supplies ($75,000) Bad Debts ($150,000) Other Operating Expenses ($125,000) Departmental Profit $1,350,000 Due to the seasonality of your Sales Revenue, Cost of Sales and Travel Expenses are expected to fluctuate throughout the year. In the first and fourth quarter of the financial year (Winter, late Autumn and early Spring) you expect a 20% drop from the average. In the second quarter, heading towards Christmas, you expect an increase of 30% and the third quarter an increase of 10% from the average. Question 1 Using the above information, implement a process to monitor actual expenditure and control of costs by constructing a cyclical quarterly budget of your department using Excel. Equation functions must be used for all calculations. Please provide a copy of your excel worksheet and include a reference to the question number. Alternatively attach a copy of your excel worksheet showing your final budget and a second copy showing your formulas.Wholesale Department Budget 2017/18 Annual 01 02 04 Credit Sales Revenue $6,000,000 $1,200,000 $1,950,000 $1,650,000 $1,200,000 Cost of Sales Wager Telephone Travel Office Supplies Bad Debts Other Operating Expenses Departmental Profit $1,350,000 $210,000 $528,750 $401,250 $210,000 Question 2 Using the excel spreadsheet you created in question 1, monitor expenditure and costs on a cyclical basis (in this case quarterly) and update the table to include comparisons of the budgeted figures for the first quarter to the actual figures. Identify variations and cost overruns by adding additional columns to calculate variances and show where these variances are favourable or unfavourable Please provide a copy of your excel worksheet and include a reference to the question number. Alternatively attach a copy of your excel worksheet showing your final budget and a second copy showing your formulas. Variance S Variance 6 F/U Credit Sales Revenue Cost of Sales 538,750 Wages 096 Telephone $2,500 Trave 6% Office Supplies $3,750 Bad Debts 79% Other Operating Expenses SO 09% Departmental Profit $1,350,000 $210,000 $173,125
