MGMT 472 HW2

pdf

School

Purdue University *

*We aren’t endorsed by this school

Course

472

Subject

Management

Date

Apr 3, 2024

Type

pdf

Pages

Uploaded by SargentHawk4647

MGMT 47200: Advanced Spreadsheet Modeling and Simulation HOMEWORK 2 Exercises must be prepared individually. A report and the supporting electronic spreadsheet work must be submitted online. The report should contain a summary of your results, brief explanation and justification of your statements and conclusions. All documents must be submitted on Bright space by one minute before midnight (11:59pm) of the due date. Question 1. Suppose a company is about to start the following project, where all the dollar figures are in thousands of dollars. In year 0, the project requires a fixed cost of $12,000. The fixed costs is depreciated on the straight-line basis over five years, and there is a salvage value of $1,500 in year 5. Fixed cost per year is $1,000. The sales generated in years 1-5 are estimated to be 2,500 units, 3,200 units, 5,100 units, 3,400 units and 1,200 units. The costs of capital in year 1 is forecast to be 8.5% and decreases linearly by 0.2% for years 2 to 5, ending with 7.7% in year 5. The tax rate is forecast to be a constant 35.0%. Sales revenue per unit is forecast to be $8.50. Variable cost per unit is forecast to be $6.30. What is the project NPV? Develop your financial spreadsheet model with frozen panes for detailed calculations and name the worksheet “Project NPV”. Project NPV = $69,579.92 At the end of year 5, you have the opportunity to continue the project for the next 3 years with additional fixed investment of $7,000 without any salvage value. Assuming all the parameters (sales, cost of capital, tax, variable cost) remain the same as year 5 for the next 3 years. Would you continue the project? Why? No, I wouldn’t continue the project. This is because the NPV of the extended project is - $661.62, which is negative indicating it is not worth it.

Question 2. The XYZ Company produces two products. The total revenue achieved from these products is described by the following equation: Every 1,000 units of X1 requires 1 hour of time in the shipping department, and every 1,000 units of X2 requires 30 minutes in the shipping department. Each unit of each product requires 2 pounds of a special ingredient, the cost of special ingredient is $1 per 1,000 units. Additionally, each hours of shipping labor costs $1. Demand for X1 and X2 is unlimited. Range X1, X2 between 10 and 15 with 0.1 increment. Use 2 way data table to find the optimal quantities to maximize profit. I couldn’t get it to work.

Question 3. Acme manufacturing makes a variety of household appliances at a single manufacturing facility. The expected demand for one of these appliances during the next four months is shown in the following table along with the expected production costs and the expected capacity for producing these items. 1 2 3 4 Demand 420 580 310 540 Production cost $49 $45 $46 $47 Production capacity 500 520 450 550 ACME estimates that it costs $1.50 per month for each unit of this appliance carried in inventory (estimated by the beginning and ending inventory levels each month). Currently ACME has 120 units in inventory on hand. To maintain a level of workforce, the company wants to produce at least 400 units per month, it also wants to maintain a safety stock of at least 50 units per month. ACME wants to determine how many to manufacture during each of the next 4 months to meet the expected demand at the lowest cost. A. Formulate this and solve this problem using solver. What is the optimal solution? a. The optimal solution would be to manufacture the following amounts during each month: B. How much money could ACME save if they are willing to drop the restriction about producing at least 400 units per month? They could save $280 is they drop the constraint of at least 400 units per month. This is found by subtracting the original total cost from the solver solution total cost without the min 400 units/month constraint and coming to the difference of $280. month production qu 1 410 2 520 3 450 4 400

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Related Documents

Summative Assessment Supply Chain Management - Copy.docx

MGMT 503 HBS Case HW.pdf

reflection 3.docx

Mid Term Studies.docx

MGMT 361 HW 4.pdf

Lesson 1 Progress Check_ AIR-602S Air and Space in Joint Operations.pdf

Leadership Vignette - Ethical Leadership.docx

OGL 300 Theory Matrix 1.docx

Recommended textbooks for you

Understanding Business

Management

ISBN:9781259929434

Author:William Nickels

Publisher:McGraw-Hill Education

Management (14th Edition)

Management

ISBN:9780134527604

Author:Stephen P. Robbins, Mary A. Coulter

Publisher:PEARSON

Spreadsheet Modeling & Decision Analysis: A Pract...

Management

ISBN:9781305947412

Author:Cliff Ragsdale

Publisher:Cengage Learning

Management Information Systems: Managing The Digi...

Management

ISBN:9780135191798

Author:Kenneth C. Laudon, Jane P. Laudon

Publisher:PEARSON

Business Essentials (12th Edition) (What's New in...

Management

ISBN:9780134728391

Author:Ronald J. Ebert, Ricky W. Griffin

Publisher:PEARSON

Fundamentals of Management (10th Edition)

Management

ISBN:9780134237473

Author:Stephen P. Robbins, Mary A. Coulter, David A. De Cenzo

Publisher:PEARSON

SEE MORE TEXTBOOKS

Recommended textbooks for you

Understanding Business
Management
ISBN:9781259929434
Author:William Nickels
Publisher:McGraw-Hill Education
Management (14th Edition)
Management
ISBN:9780134527604
Author:Stephen P. Robbins, Mary A. Coulter
Publisher:PEARSON
Spreadsheet Modeling & Decision Analysis: A Pract...
Management
ISBN:9781305947412
Author:Cliff Ragsdale
Publisher:Cengage Learning
Management Information Systems: Managing The Digi...
Management
ISBN:9780135191798
Author:Kenneth C. Laudon, Jane P. Laudon
Publisher:PEARSON
Business Essentials (12th Edition) (What's New in...
Management
ISBN:9780134728391
Author:Ronald J. Ebert, Ricky W. Griffin
Publisher:PEARSON
Fundamentals of Management (10th Edition)
Management
ISBN:9780134237473
Author:Stephen P. Robbins, Mary A. Coulter, David A. De Cenzo
Publisher:PEARSON