SLP finance module 3 excell plus discussion
Jacksongnmodule_3_slp_finance.xlsx
Sheet1
Division A | ||||||
Current Situation | Proposal | |||||
No. of Units | Purchase Price | Total Purchases | No. of Units | Purchase Price | Total Purchases | |
Internal purchases | 3000 | 1000 | $ 3,000,000.00 | 2000 | 1000 | $ 2,000,000.00 |
External purchases | 1000 | 900 | $ 900,000.00 | 2000 | 900 | $ 1,800,000.00 |
Total cost for part 101 | $ 3,800,000.00 | |||||
Savings to Div. A | $ (3,800,000.00) | |||||
Division B | ||||||
Current situation | Proposal | |||||
No. of Units | Purchase Price | Total Purchases | No. of Units | Purchase Price | Total Purchases | |
Internal purchases | 1000 | 2000 | $ 2,000,000.00 | 500 | 2000 | $ 1,000,000.00 |
External purchases | 500 | 1900 | $ 950,000.00 | 1000 | 1900 | $ 1,900,000.00 |
Total cost for part 201 | $ 2,950,000.00 | $ 2,900,000.00 | ||||
Savings to Div. B | $ 50,000.00 | |||||
Division C | ||||||
Current situation | Propasal | |||||
No. of units | Transfer profit | Total | No. of units | Transfer profit | Total | |
part 101 | 3000 | 300 | $ 900,000.00 | 2000 | 300 | $ 600,000.00 |
Part 201 | 1000 | 800 | $ 800,000.00 | 500 | 800 | $ 400,000.00 |
total profit for C | $ 1,700,000.00 | $ 1,000,000.00 | ||||
Reduction in profits | $ 700,000.00 | |||||
Financial effect for each department and the company | ||||||
Savings to dipartment A | $ (3,800,000.00) | |||||
Savings to dipartment B | $ 50,000.00 | |||||
Total Savings | $ (3,750,000.00) | |||||
Reduction in Department C profits | $ 700,000.00 | |||||
overall reduction in profitability | $ 4,450,000.00 |