Accounting Information Systems
Essay by Ken Lai • May 18, 2017 • Research Paper • 1,188 Words (5 Pages) • 1,717 Views
First, all datas are imported onto the new spreadsheet. With the data remained unchanged, the machine hours for each department to produce EX1001 and EX1002 are multiplied with their quarterly output. This gives us a total machine hours required by each department. For labour hours, the same procedure applies and derive at total labour hours required by each department. Then, the total machine hours and labour hours are used to multiply their costs per hour respectively for all departments. In summary, these are the original data provided by the company.
To obtain the new optimized values, a solver is applied. Solver is a built in add on for Microsoft Excel where you could enter all your data and it will calculate a new optimized value. Hence, it is calculated that the new quarterly output should be increased from 2500 to 3000. COMPARISON OF TOTAL MACHINE HOURS
Model | Quarterly Output (Before Solver) | Quarterly Output (After Solver) |
EX1001 | 1000 | 2000 |
EX1002 | 1500 | 1000 |
Total | 2500 | 3000 |
Table 1.1. Total quarterly output (before) and (after) solver.
Before Solver |
|
|
|
|
Engine Assembly | 1000 | 3000 | 4000 | 4000 |
Metal Stamping | 2000 | 3000 | 5000 | 6000 |
Model EX1001 | 2000 | 0 | 2000 | 5000 |
Model EX1002 | 0 | 4500 | 4500 | 4500 |
5000 | 10500 | 15500 | 19500 | |
After Solver |
|
|
|
|
Engine Assembly | 2000 | 2000 | 4000 | 4000 |
Metal Stamping | 4000 | 2000 | 6000 | 6000 |
Model EX1001 | 4000 | 0 | 4000 | 5000 |
Model EX1002 | 0 | 3000 | 3000 | 4500 |
10000 | 7000 | 17000 | 19500 |
Table 1.2. Total machine hours (before) and (after) solver.
By comparing the tables above, we could conclude that there is a total of 19500 machine hours available. However, with the current production plan, Forley is only engaging a total of 15500 machine hours, leaving a redundancy of 4000 hours not utilized.
Q1 (b) With reference to the “New Output version 1” table and “New Output version2” table, Forley’s Hightech can simply change their new production schedule to 2000 units of EX1001 and 1000 units of EX1002, or 2500 units of EX1001 and 500 units of EX1002. Despite the increase in total costs in contrast with the current production scheme, the increase in profit further suggest that Furley should definitely change their production scheme. However, it is evident that version 1 is more profitable than version 2 by 125000. Therefore, it is mandatory for Furley to change their production scheme to to produce 2000 units of EX1001 and 1000 units of EX1002, which utilizes the resources as well as more profitable.
Model | Quarterly Output (Before Solver) | Quarterly Output (After Solver) |
EX1001 | 1000 | 2500 |
EX1002 | 1500 | 500 |
Total | 2500 | 3000 |
Table 1.3. New total output (before) and (after) solver
Solver Output Version 1 | Total Variable Cost | Total Fixed Cost | Total Revenue (£) | Profit |
Model EX1001 | 60,300,000 | 25,000,000 | 80,000,000 | 4,800,000 |
Model EX1002 | 27,900,000 | 38,000,000 | ||
Solver Output Version 2 | Total Variable Cost | Total Fixed Cost | Total Revenue (£) | Profit |
Model EX1001 | 73,375,000 | 25,000,000 | 100,000,000 | 4,675,000 |
Model EX1002 | 13,950,000 | 19,000,000 |
Table 1.4. Profit comparison for Solver Output Version 1 and Version 2
...
...