supply chain management on excel

Group Study Project Guidelines

Objective: The group project concludes the entire semester’s learning of Excel with analysis of a real business problem. It is interactive in that students start with a problem, then ask instructor for data/information, analyze the data/information then ask for more data/info, and instructor provides data/information available, and the process repeats a few rounds. In the end, students are able to provide solutions.

The final project consists of a working computer model (Excel) and a write-up (Word) covering the following:

· problem definition and background information,

· model description and setup,

· model assumptions and formulas,

· major functions and features,

· user instructions (internal with the spreadsheet and external in a separate file/worksheet), and

· results discussion and future expansion (what it all means and how the model can be better).

Stage 0: Prime Line Products is a company that specializes in doors and windows hardware and accessories. In 2016, it hired a new operations manager Jim to oversee its operations. Jim quickly discovered that the operations were basically divided into two parts in the plant: customized and general items. For general items, Prime Line imports products from abroad (mostly China), stores them in their warehouse, then ships them out to business and consumer customers. For customized items, Prime Line builds “kits” that contain specific hardware for clients. Because Prime Line has its operations in Redlands, it is able to customize products quickly then ship them out to customers who receive them usually within a week. These customized kits have less competition and higher profit margins, even though they are built by more expensive American labor. However, Jim soon learned that the customized kits department uses a fixed wage system where everyone receives the same hourly pay regardless of their ability or productivity. He worried that such a system may see costs rise sharply as California raises basic wages. He studied various compensation system over the weekend and decided that Prime Line should switch to a more competitive compensation system that rewards workers for high productivity and weed out the low performers. The new piece work (or piecework) system is a type of employment in which a worker is paid a fixed piece rate for each unit he/she completed, regardless of time. Jim discusses this conversion with Ron, the company president, and receives Ron’s support. Jim is interested in making the conversion before the end of the year, if the new system can

1. keep the labor costs at current level or lower for the same production rate after conversion

2. reward faster workers with higher pay and gradually re-train or re-assign slower workers

3. hire and retain workers only when they can perform at average plus/minus a standard deviation.

Jim knows it’s not going to be easy but he is confident it will be done satisfactorily because he has asked a few bright kids at CSU to help out. So he emails the students and asks them what data they need to get the project going.

Stage 1: Use Group Blog to discuss what data are needed to convert to piece work compensation? Be as specific and exhaustive as you can. Prime Line keeps everything for the past nine months from Jan. 1.

Stage 2: Analyze the data given to see how you can convert the hourly compensation to a piecework system while maintaining labor costs at same level. The spreadsheet shows the work completed in September where workers assemble three different kits with different parts. Most of the days, only two types of kits are assembled but on a few occasions, all three are done. There are mistakes made and they have to be excluded from being counted towards final output. There are also days people work overtime and the workers are paid extra for hours above 8 per day. Discuss among team members to see how data can be analyzed.

Stage 3: Develop formulas so that the following goals are met:

Goal 1: Labor costs after conversion should remain about the same.

Goal 2: Each worker should make about the same money. Their piecework rates should resemble their hourly wages and seniority.

Goal 3: What’s the standard rate of production for each of the three kits for an average guy who just joined? In other words, what is the standard production rate (numbers assembled per hours) for each kit? What would be the range of hourly output where most (90%) people could comfortably perform?

Bonus: Who is the most productive among all the workers, both in terms of highest output per hour and most money earned?

Stage 3: Develop a spreadsheet model to capture all the information above in a page with visuals and explanations (internal doc).

2