Management Accounting. ( can answer be in spreadsheet format thank you

Question 1

The Marketing Director of VCC Limited is dissatisfied with the variances reported to him on the basis of actual costs being compared with the fixed budget for the costs attributed to the marketing function. As the management accountant, he is seeking your help in preparing a flexible budget for the calendar year 2021, based on the following information which is based on an estimated sales level of $10 million unless otherwise stated.

	$000
Fixed cost Salaries-sales representatives -Sales office	200 60
Salary-related costs	32
Rent	100
Depreciation: Furniture Cars	5 67
Insurance	20
Advertising	250
Variable costs Sales representatives commission Sales-related costs-12 1/2%	64 8
Sales representatives ordinary expreses	25
Bad Debts	100
Stationary and pastage	50
Agency fees	80
Semi-variable costs Telephone rentals Metered calls	2000 14,000
Sales representatives car expenses excluding depreciation: Fixed Variable	7,000 48,000

Sales promotions: the budget figures are to be based on the costs given below which relate to the years 2016 to 2019 and the estimates for 2020.

Year	Cost	Sales
	$000	$million
2016	384	4.2
2017	402	5.1
2018	368	3.4
2019	450	7.5
2020	478	8.9

Required: Prepare a flexible budget for sales levels of $9m, $10m and $11.5m.