| 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 | | $ 194,000 | FIVE YEAR PROPERTY FOR TAX DEPRECIATION |
| NEW WORKING CAPITAL NEEDS | | $ 50,000 | WILL BE RECOVERED AT THE END OF THE THIRD YEAR |
| PROJECTED NEW REVENUES: |
| SALES | PROBABILITY |
| $ 200,000 | 30% |
| $ 250,000 | 50% |
| $ 300,000 | 20% |
| COST OF GOOD SOLD | | 30% | OF SALES |
| VARIABLE CASH COSTS | | 10% | OF SALES |
| ANNUAL FIXED CASH COSTS: |
| RENT | $ 50,000 |
| CLEANING | $ 20,000 |
| MAINTENANCE & OTHER | $ 10,000 |
| TOTAL FIXED COSTS | $ 80,000 |
| EQUIPMENT DISPOSAL PROCEEDS | | $ 19,400 | SALVAGE VALUE AT THE END OF YEAR 6 |
| FIRM'S COST OF CAPITAL | | 12.00% |
| TAX RATE | | 35% |
| NOTE - WHEN COMPUTING TAX, A NET LOSS FOR THE YEAR MEANS 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 $200,000 COMPUTE THE PAYBACK, IRR AND NPV. FOR THE NPV, COMPUTE |
| AT BOTH THE FIRM'S DISCOUNT RATE AND 16%, WHICH IS A 4% PREMIUM ADDED TO THE RATE. |
| B. COPY THE WHOLE WORKSHEET AND SOLUTIONS FOR PART A TO THE WORKSHEET NAMED PART B, |
| AND REDO THE COMPUTATIONS BY CHANGING THE ANNUAL SALES TO $250,000. |
| C. COPY THE WHOLE WORKSHEET AND SOLUTIONS FOR PART A TO THE WORKSHEET NAMED PART C, |
| AND REDO THE COMPUTATIONS BY CHANGING THE ANNUAL SALES TO $300,000. |
| You should place your answers in each of the boxes shown below color-coded in Yellow color. |
| 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: | (using the information given above, fill in the blanks below to determine each year's operating cash flow): |
| SALES | | $ | $ | $ | $ | $ | $ |
| LESS COST OF GOODS SOLD |
| GROSS PROFIT |
| LESS VARIABLE COSTS |
| LESS FIXED COSTS |
| LESS DEPRECIATION EXPENSE |
| PROFIT (LOSS) BEFORE TAX |
| LESS INCOME TAX EXPENSE (BENEFIT) |
| PROFIT (LOSS) AFTER TAX |
| PLUS DEPRECIATION EXPENSE |
| TOTAL OPERATING CASH INFLOWS | | $ | $ | $ | $ | $ | $ |
| SALVAGE VALUE ON EQUIPMENT: | (figure out the salvage value of the equipment for tax purposes): |
| PROCEEDS | | | | | | | $ |
| LESS TAX BASIS OF EQUIPMENT: |
| COST |
| ACCUMULATED DEPRECIATION |
| TAX BASIS |
| GAIN ON SALVAGE |
| LESS INCOME TAX ON SALVAGE GAIN |
| NET PROCEEDS ON SALVAGE AFTER TAXES | | | | | | | $ |
| RELEASE OF ORIGINAL WORKING CAPITAL NEEDS (NO TAX AFFECT) | | | | $ |
| TOTAL CASH INFLOWS (OUTFLOWS) |
| CUMULATIVE CASH INFLOWS (OUTFLOWS) |
| THREE METHODS OF EVALUATION: |
| PAYBACK PERIOD | | | YEARS (round to 2 decimal places). |
| INTERNAL RATE OF RETURN | | | Answer is in %-2 decimal places please. |
| NET PRESENT VALUE AT | 12.00% | | Answer is in $-round answer to nearest dollar. |
| NET PRESENT VALUE AT | 16.00% | | Answer is in $-round answer to nearest dollar. |
| Note: Pages 375-380 in your course textbook show you how to calculate the Payback Period, the Internal Rate of Return, and the NPV. |
TheVirgo757
excel_spreadsheet_2.xls
