HW#2



DSS Assignment

(Assignment 1 using EXCEL)

DUE DATE: March 15, 2011

DO NOT WAIT TO START THE PROJECT!!!!

(A Template for this assignment is posted in the course announcement in webTycho)

MUST WORD INDEPENDENTLY!!!!

Resources available:

SIMNET exercise

Module D (book)

Achievement and Learning Center (410-8375383)

Budget Planning and DSS System for

, is an Indian company with headquarters in New Delhi, India. Currently, they manufacture a single product, specialty curry sauce. They are interested in developing 12 MONTH (see template on course site) budget models and want to perform decision analysis on this model. has approached you for help.

The following information is available:

1. sells ONE product, specialty curry sauce, at a price of $11 a unit. The price will remain fixed for the next year (2012).

2. Sales will average 5500 units per month from Jan to May, 2012. Starting June 1 sales will increase by 5% each month thereafter.

3. Cost of production involves materials and labor costs. (based on units produced and NOT units sold)

o Material costs are $1.00/unit

o Direct labor costs are $ 0.50/unit until end of June and then will increase 5% (monthly) thereafter..

4. Production will be constant at 6000 units/month.

5. Inventory at the beginning of the year, January 1, 2012 will be 3000 units.

6. Inventory carrying costs are $4/end of the month inventory units., i.e., if there are 5000 units at the end of January, inventory carrying cost will be 5000*4=$20,000 and will be charged as inventory carrying cost for January. (this is an example, you need to calculate end of January inventory using following formula)

end of the month inventory= Beginning inventory of the month + Production for that month – sales for that month

(Note: Beginning inventory of a given month is the same as ending inventory of the previous month)

7. The following operating expenses are also projected:

• Advertising expense: $1000 the first month and 2.5% of the previous month’s GROSS SALES (unit price * monthly sales), in dollars thereafter.

• Administrative salaries are $15,000 per month

• Company has a lease on its factory in Beijing which costs $500 per month

8. expects tax rate to be 30% in 2012.

Note: All increases are incremental. i.e., if there is 10% increase in sales starting February and there after, then

February sales will be January sales + 10 % increase of January sales

March sales will be February sales + 10% increase of February sales

etc..

Requirements: In an EXCEL spreadsheet create the following

1. Develop a Profit/Loss model (see template on course site) for (Base Case). Do not manually enter data that is changing; use cell formulas to calculate data which is dependent on other cells.

a. Identify and format months (red color; see Module D) when end of the month inventory falls below 500. DO NOT MANUALLY COLOR THE CELL WRITE FORMULAS TO DO IT

Give reasons why this may be happening. You may want to draw graphs etc to support your answer.

b. Identify and format months (color blue) where profit falls below $25,000. DO NOT MANUALLY COLOR THE CELL WRITE FORMULAS TO DO IT

Give reasons why this may be happening. You may want to draw graphs etc to support your answer.

2. Give the total (a count) number of months that have profit of less than 25000 AND end of the month inventory less than 500 (if 6 months have profit below 25000 and 2 months have inventory below 500, then total count will be 8)

(MUST use COUNTIF function, you can add (+) two countIF functions)

In general format: COUNTIF(range, criteria)

(Can use EXCEL help function for further clarification)

3. Mr. Kumar, CEO of , is also interested in studying the

impact of the following:

What happens to profit/Loss if the prices go up by 10% and the demand goes down by 10% from the base case for each months?

Use a separate worksheet (call it What-IF) to see the effect of this policy and provide recommendations

4. , a US based company wants to take over . What are some of the factors they should consider and why?

TO BE SUBMITTED:

can submit (in class) hard copies of each part below OR ONE zip file in webTycho

(See Appendix B below on how to convert a group of files to ONE zip file)

• An executive report to Mr. Kumar summarizing your analysis.

• ONE EXCEL spreadsheet file with .xls extension.

(Note you MUST create all worksheets in ONE worksheet file. At the bottom of worksheet, there are several worksheets tabs available. As you click a new tab, say worksheet 2, a new worksheet opens up and you can use that for What-IF analysis

• Question 2 which will be part of the previous worksheet

• Discussion of question 1(a) and (b), 3, and 4

• Appendix A for grades

Appendix A: Grading Criteria

|Requirements |Maximum Points |Your Points |

|Executive Report |4 | |

|1. Worksheet (Spreadsheet) | | |

|Base case |25 | |

|1(a) rate of return |1 | |

|1(b) Inventory falls below 500 (red color) |2 | |

|1(c) profit falls below 25,000 (blue color) |1 | |

|Q2 COUNT |5 | |

|Q3 | | |

|what-IF worksheet & discussion |10 | |

| | | |

|Q4 Discussion |2 | |

|TOTAL |50 | |

Appendix B

(Creating a zip file)

• Go to the directory where your files are

• High light them

• Right click and

• Select Send to

• Select compressed(zipped) file

• This will save your document in the same directory (folder) as the files that you are zipping with first file name

• Right click on the file, select rename and change name

• Post this zip file in webTycho

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download