USING EXCEL:

Dubai Technologies Company approached you as a Business Consultant to assist them in preparing their 2021 Master Budget. You have been provided with the following data:

• The company is manufacturing and selling TWO models of a branded Smart Phone:

o XYZ Plus

o XYZs

• Expected number of units to be sold in 2021

o XYZ Plus: 150,000 units o XYZs: 100,000 units

• Expected selling Price: o XYZ Plus: AED1500 per unit

o XYZs: AED 1000 per unit

• Each model requires 5 components (raw materials):

o Camera (XYZ Plus has 4 Cameras, while XYZs has only 2 Cameras).

o Keypad o Screen o Frame

o Motherboard.

• Purchasing prices for the five components are as follows:

o Camera: AED 90 o Keypads: AED 60 o Screen: AED 120 o Frame: AED 18 o Motherboard: AED 72

• The management policy is no ending inventory of any component item or finished goods is required.
• Each Mobile model requires 5.5 hours of labor at a cost of AED 40.00 per hour to be manufactured.
• Manufacturing Overhead is allocated at a rate of AED 60 per Direct Labor Hour. 2
• Selling and Admin variable expense rates per unit of sales are sales commissions AED 5 and freight-out AED 2
• Selling and Admin fixed expenses include Depreciation AED 16,000, Salaries AED 20,000 and maintenance AED 8000 for each model
• Interest is AED 10,000 and Taxes are AED 50,000

Required: USING EXCEL

1. Prepare the following for 2021: o Sales Budget o Components (Direct Material Purchases) Budget o Direct Labor Budget o An overhead Budget o Selling and Administrative Budget
2. Prepare a budgeted income statement