MGT 325 Module 6 Spreadsheet Exam - PART A COMPREHENSIVE CHAPTER 12 & 13 PROBLEM
ID: 2677705 • Letter: M
Question
MGT 325 Module 6 Spreadsheet Exam - PART ACOMPREHENSIVE 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%:
Explanation / Answer
TOTAL CASH FLOWS $560000 CUMULATIVE CASH FLOWS $800000
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.