Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Download: http://solutionzip.com/downloads/mgt-325-module-6-spreadsheet-part-a-b-c/
- MGT 325 Module 6 Spreadsheet Part A B C
- MGT 325 Module 6 Part A
- You should prepare Part A first, following the instructions under the Required section of the worksheet below.
- Then, follow the instructions given for Parts B and C (sheets B and C below) on the separate attached worksheets for Parts B and C.
- 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.
- On each worksheet, calculating the correct Payback Period, Internal Rate of Return, NPV at 12%, and NPV at 16% is worth 2.5 points each (10 points each for Parts A, B, and C).
- The other 23 points of credit which can be earned in each worksheet (Parts A, B, and C) include credit for calculating and filling out correctly the following yellowed-out areas:
- â?¢the Initial Investment Amounts;
- â?¢the Total Operatiing Cash Inflow Amounts;
- â?¢the Net Proceeds on Salvage Value After Taxes;
- â?¢the correct amount for the Release of Working Capital Needs; and
- â?¢ the correct amounts in the rows Total Cash Inflows (Outflows) and Cumulative Cash Inflows (Outflows).
- Download: http://solutionzip.com/downloads/mgt-325-module-6-spreadsheet-part-a-b-c/
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement