90.2K

Verified Solution

Link Copied!

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 Q1 Q2 $5,000,000 $1,200,000 $1,950,000 03 Q4 Credit Sales Revenue $1,650,000 $1,200,000 Cost of Sales Wages 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. Annual Q1 Budgeted Actual Variance $ Variance %6 F/U Credit Sales Revenue $6,000,000 $1,200,000 $1,125,000 Cost of Sales $38,750 $0 0% Wages Telephone Travel $2,500 6% Office Supplies $3,750 Bad Debts 7% Other Operating Expenses $0 0% Departmental Profit $1,350,000 $210,000 $173,125

Answer & Explanation Solved by verified expert

See Answer