| MGT 325 Module 6 Spreadsheet Exam - PART A | |||||||||
| COMPREHENSIVE CHAPTER 12 & 13 PROBLEMS | |||||||||
| MONARCH CORPORATION IS GOING TO START A NEW PRODUCT LINE OF PRODUCTS IN A WHOLE NEW MARKET. | |||||||||
| THE DATA FOR ANALYSIS IS PRESENTED BELOW: | |||||||||
| COST OF THE EQUIPMENT NEEDED | $ 200,000 | FIVE YEAR PROPERTY LIFE FOR TAX DEPRECIATION | |||||||
| NEW WORKING CAPITAL NEEDS | $ 50,000 | WILL BE RECOVERED AT THE END OF THE THIRD YEAR | |||||||
| PROJECTED NEW REVENUES: | |||||||||
| SALES | PROBABILITY | ||||||||
| $ 225,000 | 30% | ||||||||
| $ 350,000 | 50% | ||||||||
| $ 500,000 | 20% | ||||||||
| COST OF GOOD SOLD | 25% | OF SALES | |||||||
| VARIABLE CASH COSTS | 15% | OF SALES | |||||||
| ANNUAL FIXED CASH COSTS: | |||||||||
| RENT | $ 50,000 | ||||||||
| CLEANING | $ 20,000 | ||||||||
| MAINTENANCE & OTHER | $ 20,000 | ||||||||
| TOTAL FIXED COSTS | $ 90,000 | ||||||||
| EQUIPMENT DISPOSAL PROCEEDS | $ 20,000 | SALVAGE VALUE AT THE END OF YEAR 6 | |||||||
| FIRM'S COST OF CAPITAL | 9.00% | ||||||||
| TAX RATE | 30% | ||||||||
| NOTE - WHEN COMPUTING TAX A NET LOSS FOR THE YEAR A POSITIVE TAX SAVINGS IS CREATED | |||||||||
| SINCE THERE IS OTHER INCOME TAX ON OTHER INCOME TO OFFSET | |||||||||
| DEPRECIATION RATES FOR TAX PURPOSES: | |||||||||
| YEAR ONE | 20.00% | ||||||||
| YEAR TWO | 32.00% | ||||||||
| YEAR THREE | 19.20% | ||||||||
| YEAR FOUR | 11.50% | ||||||||
| YEAR FIVE | 11.50% | ||||||||
| YEAR SIX | 5.80% | ||||||||
| ASSUMPTIONS: | |||||||||
| ALL CASH FLOWS IN YEARS 1-6 OCCUR AT THE END OF THE YEAR. ALL INITIAL CASH INFLOWS OR | |||||||||
| OUTFLOWS OCCUR TODAY. | |||||||||
| REQUIRED: | |||||||||
| A. ASSUMING SALES ARE $225,000 COMPUTE THE PAYBACK, IRR AND NPV. FOR THE NPV COMPUTE | |||||||||
| AT BOTH THE FIRM'S DISCOUNT RATE AND 11%, WHICH IS A 2% PREMIUM ADDED TO THE RATE. | |||||||||
| B. COPY THE WHOLE WORKSHEET AND SOLUTIONS FOR PART A TO THE WORSHEET NAMED PART B, | |||||||||
| AND REDO THE COMPUTATIONS BY CHANGING THE ANNUAL SALES TO $350,000. | |||||||||
| C. COPY THE WHOLE WORKSHEET AND SOLUTIONS FOR PART A TO THE WORSHEET NAMED PART C, | |||||||||
| AND REDO THE COMPUTATIONS BY CHANGING THE ANNUAL SALES TO $500,000. | |||||||||
| Fill in all of the Cells below in Yellow using the information given above. | |||||||||
| PART A | |||||||||
| YEARS | 0 | 1 | 2 | 3 | 4 | 5 | 6 | ||
| INITIAL INVESTMENT (NO INCOME TAX AFFECTS) | |||||||||
| COST OF THE EQUIPMENT NEEDED | |||||||||
| WORKING CAPITAL NEEDS | |||||||||
| TOTAL INITIAL INVESTMENT | |||||||||
| ANNUAL OPERATING RECEIPTS | |||||||||
| SALES | |||||||||
| LESS COST OF GOODS SOLD | |||||||||
| GROSS PROFIT | |||||||||
| LESS VARIABLE COSTS | |||||||||
| LESS FIXED COSTS | |||||||||
| LESS DEPRECIATION | - | ||||||||
| PROFIT BEFORE TAX | |||||||||
| LESS INCOME TAX | |||||||||
| PROFIT AFTER TAX | |||||||||
| PLUS DEPRECIATION | |||||||||
| TOTAL OPERATING CASH FLOWS | |||||||||
| SALVAGE VALUE ON EQUIPMENT | |||||||||
| PROCEEDS | |||||||||
| LESS TAX BASIS OF EQUIPMENT: | |||||||||
| COST | |||||||||
| ACCUMULATED DEPRECIATION | |||||||||
| TAX BASIS | |||||||||
| GAIN ON SALVAGE | |||||||||
| LESS TAX ON SALVAGE GAIN | |||||||||
| NET PROCEEDS ON SALVAGE | |||||||||
| RELEASE OF WORKING CAPITAL (NO TAX AFFECT) | |||||||||
| TOTAL CASH FLOWS | - | - | - | - | - | - | - | ||
| CUMULATIVE CASH FLOWS | - | - | - | - | - | - | |||
| THREE METHODS OF EVALUATION | |||||||||
| PAYBACK | YEARS | ||||||||
| INTERNAL RATE OF RETURN | |||||||||
| NET PRESENT VALUE AT | 9.00% | ||||||||
| NET PRESENT VALUE AT | 11.00% | ||||||||
12 years ago
Perfect solution
NOT RATED
Purchase the answer to view it
- _mgt325_mod6_spreadsheet_exam_spring-2.xls