Assessment Task 2: Part A – Spreadsheet Design And Functionality
- December 22, 2023/ Uncategorized
| Sales Details | |||||||
| Azu | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |||
| Number of Units | 6000 | 7000 | 8000 | 9000 | |||
| Selling Price per Unit | $ 125.00 | $ 125.00 | $ 135.00 | $ 135.00 | |||
| Ranna | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |||
| Number of Units | 5000 | 5500 | 6200 | 6800 | |||
| Selling Price per Unit | $ 160.00 | $ 160.00 | $ 170.00 | $ 170.00 | |||
| Inventory Details | |||||||
| Azu | |||||||
| Opening Inventory Units | 4000 | ||||||
| Closing Inventory Units | 5500 | ||||||
| Ranna | |||||||
| Opening Inventory Units | 3000 | ||||||
| Closing Inventory Units | 4500 | ||||||
| Direct Material Used Per Unit | |||||||
| Direct Material | Unit Usage | Azu | Ranna | ||||
| A | Kg | 4 | 5 | ||||
| B | Kg | 2 | 3 | ||||
| C | Each | 0 | 1 | ||||
| Direct Material | Purchase Price per kg | Opening Inventory Units | Closing Inventory Units | ||||
| A | $ 8.00 | 23000 | 25500 | ||||
| B | $ 4.25 | 1900 | 1800 | ||||
| C | $ 2.80 | 4000 | 4600 | ||||
| Product | Hours per unit | Rate per hour | |||||
| Azu | 2.25 | $ 14.55 | |||||
| Ranna | 3.5 | $ 16.75 | |||||
| Time per unit | 2 Hours 15 Minutes | ||||||
| Total hours per unit | 2.25 | ||||||
| Time per unit | 3 hours 30 minutes | ||||||
| Total hours per unit | 3.5 | ||||||
| Manufacturing Overhead | |||||||
| Manufacturing Overhead per labour hour | $ 15.00 | ||||||
| SALES BUDGET | |||||||
| Particulars | Reference Column | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | Total | |
| AZU | |||||||
| Number of Units Sold | 6000 | 7000 | 8000 | 9000 | 30000 | ||
| Selling Price Per Unit | $ 125.00 | $ 125.00 | $ 135.00 | $ 135.00 | |||
| Sales | a | $ 7,50,000.00 | $ 8,75,000.00 | $ 10,80,000.00 | $ 12,15,000.00 | $ 39,20,000.00 | |
| RANNA | |||||||
| Number of Units Sold | 5000 | 5500 | 6200 | 6800 | 23500 | ||
| Selling Price Per Unit | $ 160.00 | $ 160.00 | $ 170.00 | $ 170.00 | |||
| Sales | $ 8,00,000.00 | $ 8,80,000.00 | $ 10,54,000.00 | $ 11,56,000.00 | $ 38,90,000.00 | ||
| Total Sales | b | $ 15,50,000.00 | $ 17,55,000.00 | $ 21,34,000.00 | $ 23,71,000.00 | $ 78,10,000.00 | |
| a | 60000*125=750000 | ||||||
| b | 3920000+3890000=7810000 | ||||||
| Particulars | Reference Column | AZU | RANNA | ||||
| Sales Units | 30000 | 23500 | |||||
| Add: Closing Inventory | 5500 | 4500 | |||||
| Less: Opening Inventory | 4000 | 3000 | |||||
| Production Units | a | 31500 | 25000 | ||||
| DIRECT MATERIAL PURCHASE BUDGET (KGS) | |||||||
| Particulars | Reference Column | A (kgs) | B (kgs) | C (units) | |||
| Units to be Produced | 31500 | 31500 | 31500 | ||||
| Materials required per unit | 4 | 2 | 0 | ||||
| Direct materials to be purchased for Azu | a | 126000 | 63000 | 0 | |||
| Units Produced | 125000 | 75000 | 25000 | ||||
| Materials required per unit | 5 | 3 | 1 | ||||
| Total direct materials to be purchased to be Ranna | b | 625000 | 225000 | 25000 | |||
| Total Direct Material to be Purchased | c | 751000 | 288000 | 25000 | |||
| Add: Closing Inventory Units | 25500 | 1800 | 4600 | ||||
| less: Opening Inventory Units | 23000 | 1900 | 4000 | ||||
| Direct Material to be Purchased | d | 753500 | 287900 | 25600 | |||
| a | 31500*4=126000 | ||||||
| b | 125000*5=625000 | ||||||
| c | 125000+625000=751000 | ||||||
| d | 751000+25500-23000 | ||||||
| Particulars | Reference Column | A (kgs) | B (kgs) | C (units) | |||
| Direct Material to be Purchased | 753500 | 287900 | 25600 | ||||
| Estimated purchase price | $ 8.00 | $ 4.25 | $ 2.80 | ||||
| Total cost of Material to be purchased | a | $ 60,28,000.00 | $ 12,23,575.00 | $ 71,680.00 | |||
| Direct Material | A | B | C | ||||
| Azu | b | $ 10,08,000.00 | $ 2,67,750.00 | $ – | |||
| Ranna | c | $ 50,00,000.00 | $ 9,56,250.00 | $ 70,000.00 | |||
| Total | $ 60,08,000.00 | $ 12,24,000.00 | $ 70,000.00 | ||||
| a | 753500*8=6028000 | ||||||
| b | 126000*8=108000 | ||||||
| c | 625000*8=5000000 |
