DSS Assignment
(Assignment 1)
GROUP

DUE DATE: March 13, 2015
Peer Evaluation: Each group member will be grading other team members. A student’s score will be an average of the group member’s rating times the overall assignment score.
For example if you get an average of 80/100 in peer evaluation from your group and 90 in the assignment, your INDIVIDUAL score would be 90*.8 = 72/100.

If there is a problem with your group members, please e-mail me right away!!! PEER Evaluations are posted in the ASSIGNMENT section under PEER_EVAL_assignment1

Once the project is submitted, fill it out and post it in SAKAI under peereval1
Peer Evaluation has TWO parts * Rubric part (one for each group member) * Text Part (only once)
If your team has three/four members, you will be submitting two/three rubric parts by copying and pasting rubric parts as needed (one for each group member; do not evaluate yourself) and ONE text part

COMBINE all in ONE file. Do not submit separate files for each group member.

SUBMIT only one rubric file with all evaluation!!!!!

Budget Planning and DSS System for Pierogi_rus.com

COMPANY HISTORY

Pierogi, Inc. was started by Mr. Polanki, Sr. in 1962 in the basement of his home in Warsaw, Poland. He and his wife worked endless hours experimenting and promoting their product. Eventually, their product became known as the specialty Pierogi in supermarkets, restaurants and food circles. The sales increased almost 10 percent per year and the company was enjoying tremendous growth. However, in 1982, the Polankis moved their operations to Buffalo, New York, where it took them almost two years to establish a viable operation. In 2002, their son, Samuel Polanki, a graduate of Rice University, took over his parents’ business and started modernizing Pierogi’s operations. Mr. Polanski followed the traditional business model and developed a functional organization with departments in Finance, Human Resources, Production, Marketing and Information Systems. Pierogi went public in 2003, but the Polanki family still controlled 51 percent of the company shares. The company was having tremendous growth and some global demand for the specialty pierogi was emerging. In 2005, Mr. Polanki looking at the e-commerce boom decided to make pierogi an on-line business and renamed it as Pierogi_rus.com. This brought in major changes and employees were retrained to work in the Internet environment. In addition, a web master was hired to develop and maintain the website. Investors initially did not like the changes and the stock price suffered. However, continued growth helped stock prices to recover back to its 2005 level. Pirogi_rus.com was beginning to get more revenues from overseas, especially Poland and surrounding countries. (see figure 1). Though their overseas sales were increasing, the bulk of their sales (90 percent) were still from the U.S.

Figure 1:

Growth continued until 2007, when the economic downturn started to hurt Perogi_rus.com’s sales.

CURRENT SITUATION
In the U.S., Pierogi_rus.com is known as a specialty Polish-American company with headquarters in Buffalo, NY. It is known as “Tiffany” of Pierogi. The economic downturn is affecting its sales and the bottom line. Pierogi sales are seasonal and sensitive to the economy. The company’s CEO and President, Mr. Polanki, is worried about sales projections that point to declining sales (5% per year until 2015) and profits in the coming years. In addition, shareholders are revolting and want to change the board. On March 21, 2012, Mr. Polanki called a meeting of his senior advisors to confront this upheaval and the issues of declining sales and diminishing shareholder value. The CEO, CIO, and production and marketing managers attended the meeting.

There was much discussion about strategy to revamp the company, but no consensus emerged. The production department manager, Ms. Chung, wanted to upgrade the IT systems like Supply-Chain Management (SCM) to cut shipment time to customers. The marketing department manager, Ms. Leila, wanted a Customer Relationship Management (CRM) system that would identify and provide better response to “paying” customers. The CIO, Mr. Kawaski, wanted to streamline operations and automate the whole business process from SCM to CRM. Mr. Kawasaki also suggested cloud computing/service migration to cut costs. However nobody is familiar with cloud computing and it would require a major effort to educate everybody and may not be beneficial in the short run. In addition, Mr. Polanki, however, was in no mood to commit money and was looking for short term solutions to weather the economic storm. Ms. Chung proposed that they hire a team of experts who would study the budgeting process, the various “what-if” analysis scenarios and develop recommendations. The group reluctantly agreed with this recommendation and decided to wait for the expert’s report to become available on March 13, 2015.

Pierogi_rus.com has the following business policies that must be adhered to:

* Specialty Pierogi end of the month inventory should not fall below 500 units in any month; (any deviations must be noted) * Monthly profit before taxes should always be more than $100,000; any deviations must be noted * The next contract for management and other employees (clerks, data processors etc..) has already been negotiated, which provides a one time 3% salary hike to managers and 5% hike to all other employees starting July 1, 2015. (Employees are classified as direct labor, management and other employees). Direct labor costs are direct costs that are incurred in producing Pierogi. Their costs are going to increase starting July 1, 2015.See section 4 below. * Costs estimates are based on units produced (as opposed to units sold). For example, if 120 units are produced in May and each unit costs $10, then unit production cost for May would be $1200 (120*$10). * Traditionally, the company likes to see a 25% rate of return, which they define as net profit after taxes /gross sales * In case end of the month quantity is less than the order amount, orders are filled by the amount of inventory at hand and rest is back ordered. For example, if inventory at hand is 500 and the order is for 1000 units 500 units are filled and rest are backordered and filled the following month.

Mr. Kawasaki, the CIO, has approached your group for help in developing financial statement for 2015 and resolving some of issues outlined below for the coming year 2015 and beyond.

The following information is available:

1. Last thirteen year profits are shown in the table below

2. Pierogi_rus.com sells ONE product, specialty Pierogi, at a price of $120 a unit. The price will remain fixed for the next year (2015).

3. Sales will average 2,800 units per month. However, past experience indicates that there is a seasonality factor (fraction by which sales vary by month). The projected seasonal indices are as follows:

JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC | .43 | .61 | .73 | .85 | .94 | 1.05 | 1.74 | 1.89 | 1.60 | 1.19 | .66 | .49 |

4. Cost of production involves materials and direct labor costs. * Material costs are $10/unit and are expected to start increasing at a rate of 0.50% per month starting May 2015. * Direct labor costs are as follows: * 1 unit of Pierogi incurs following direct labor costs:

Pierogi 1 | Dough Kneader | Roller | Filler/Shaper | Baker | Wrapper | Labor cost/Puerogi | $5 | $3 | $4 | $5 | $3 |

Starting July 1, 2015 direct labor costs/Pireogi will change as follows:

Labor cost/Puerogi | $5 | $3 | $4 | $5 | $3 | % increase | 0.25% | 0.5% | 0.75% | 1.25% | 0.25% |

5. Production will be constant at 2,500 units/month until end of August, then will increase to 4,000 units from September to December 2015.

6. Inventory carrying costs are $3/end of the month units., i.e., if there are 5,000 units at the end of January, inventory carrying cost will be 5000*3=$15,000, which will be charged as inventory carrying cost for January.

End of the month inventory=beginning inventory for that month - Sales that month + production that month

7. Inventory at the start of the year, January 1, 2015, will be 1,500 units.

8. The following operating expenses are also projected: * Advertising expense: $4,500 in the first month and then 2.5% of the previous month’s GROSS SALES (unit price * units sold) in dollars, thereafter. * Employee expense: Managers salaries total $30,000/month and other employees salaries total $20,000/month. (note that these do not include salary hikes) * Company has a lease on its factory in Newark, NJ that costs $1,500 per month

9. Pierogi_rus.com expects the tax rate to be 25% in 2015.

10. All increments are cumulative.

Requirements
In an EXCEL spreadsheet create the following:

1. There will be one worksheet with several worksheets in it. Put all the data in one worksheet. Call this worksheet, Data sheet.

2. Develop a Budget model; link the data from the first (data) sheet into the second (base model) sheet. There should be 12 months on the worksheet. Call the second sheet Base Model. (A basic template is provided template (ctrl+ click on it ). Also posted with your assignment 1 in SAKAI. You may modify it as necessary.

On this sheet--

a. Show the rate of return (net profit/gross sales *100) for each month

b. All fields must be properly formatted

c. Identify and format months (red color) when end of the month inventory falls below 500. Do not individually color each cell that falls below 500, but instead use formulas that will automatically color each cell that falls below 500. d. Identify and format months (red color) when the net profit after taxes falls below $100,000 or you have a loss. Do not individually color each cell that falls below $100,000, but instead use formulas that will automatically color each cell that falls below $100,000. Also provide a count of number of months this happens.

e. Using appropriate formula calculate the number of months that have profits after taxes below $100,000

3. Mr. Polanki, CEO of Pierogi_rus.com, is also looking for alternatives. He is interested in studying the impact of the following:

a. What happens to profit/Loss if the prices go up by 20% and the demand goes down by 20%? Create a separate worksheet to see the effect of this policy, discuss the results and provide recommendations.

b. Goal seeking: What variables can Pierogi_rus.com adjust to obtain a 15% return on revenues (ROR) (profit after taxes/ sales*100) in December 2015? Create a separate worksheet to show this information and discuss the results and provide recommendations. Use appropriate EXCEL tools to analyze this.

4. Provide any additional analysis your group wants to perform to support your analysis.

Submission * One submission per group in Sakai. Name your file as assignment1_your groupname, i.e. if your group is “A”, then name your file as assignment1_GroupA.

* Include Group NAME and names of group members who participated on the first page of WORD file.

* Submit all files as ONE zip file. See Appendix B below on how to convert a group of files as ONE zip file. You will have TWO files-- one file for an executive report and discussion questions in WORD, and the other file with a worksheet in EXCEL * Discuss EACH of the following submission parts (1,3,4,5,6,7,8) as a separate question in the WORD (Do not combine answers to question)

1. Executive report (in WORD file)
(Use proper format: You may want to visit the Internet to look for sample executive reports.) At a minimum, it should include a summary of your analysis and recommended solutions (alternatives). (name this file as: assignment1_Groupname

2. Discuss the three components (mode, data and user interface) of this DSS and how following manager(s) can use this DSS for decision making * Sales Manager * Production Manager * CEO

3. Spreadsheet file (requirement 1) with .xlsx (2007 or 2010) containing one worksheet with: a. Data worksheet b. Base Model worksheet c. What-If worksheet d. Goal Seeking worksheet e. Count of months below $100,000 profit after taxes f. Any additional analysis

Continue in WORD file with executive report

4. Discussion of questions 2 and 3 of the requirements (What-If , Goal Seeking Analysis)

5. Additional analysis to support your decision/discussions.

6. Foods_rUS.com, an Italian company has made a friendly bid for Pierogi_rus.com. What are some of the factors Pirogi_rus.com should consider and why? (you may want to refer to Chapter 2 & 15).

7. Discuss the following short/Long term issues (make sure to relate them to concepts in the book and other sources)

IMMEDIATE (SHORT TERM) ISSUES (from the perspective of Mr. Polanki’s CEO)

* What strategies can be used in the short term, during the economic slowdown, to comply with business policies? * How can IT be used for immediate advantage?

LONG TERM ISSUES (from the perspective of Mr. Polanki’s CEO)

* As global sales increase, Pierogi_rus is planning to acquire a distribution company either in the U.S. or overseas (namely Poland). Should they follow this expansion strategy?

* Should the company diversify into other foods? Why or why not?

8. Using Porter’s five force model and value chain, discuss how IT can be leveraged to meet the long term challenges (maybe even survival) of the company.

9. Cloud computing is becoming an important way for small businesses to cut costs. Discuss pros and cons of Pierogi_rus.com’s move to Cloud computing.

10. Appendix A

Appendix A: Grading Criteria

Submissions | Maximum Points | Your Points | Part 1:Executive Report | 3 | | Part 2: DSS components | 5 | | Part 3:Worksheet (Spreadsheet):Data file, Base case, What-If, & Goal seeking, month count | 22 | | Part 4 – Goal seeking and What-if analysis discussion | 5 | | Part 5 –Additional Analysis | 5 | | Part 6:Bid by Food_r_us.com | 5 | | Part 7: Short/Long term Issues | 6 | | Part 8:Value Chain & Porter’s Model | 5 | | Part 9:Cloud computing | 4 | | | | | TOTAL | 60 | | | | | | | | | | |

(Appendix B is on the following Page)

Appendix B
(Creating a zip file)

* Go to the directory where your files are located; * Highlight them; * Right click; * Select Send to; and * 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 Sakai