• Home
  • Blog
  • IDS homework. 3 very short and simple excel worksheets.

IDS homework. 3 very short and simple excel worksheets.

0 comments

PLEASE INCLUDE FORMULAS IN THE EXCEL WORKSHEET!

EACH QUESTIONS, IN A DIFFERENT WORKSHEET.

 

Question Set 1. A store manager has tracked hours worked and orders processed as given below.

 

Hours Worked

Cashier

1-Feb

2-Feb

3-Feb

4-Feb

5-Feb

6-Feb

7-Feb

8-Feb

Kevin

 

 

8

7

7

 

 

8

Lisa

8

8

 

8

 

8

7

7

Mark

   

7

7

8

 

8

 

Naomi

8

8

 

 

7

7

 

 

Oscar

8

8

8

 

 

8

8

8

 

 

Orders Processed

Cashier

1-Feb

2-Feb

3-Feb

4-Feb

5-Feb

6-Feb

7-Feb

8-Feb

Kevin

 

 

94

76

73

 

 

102

Lisa

82

83

 

104

 

94

88

88

Mark

 

 

76

75

97

 

96

 

Naomi

107

107

 

 

91

88

 

 

Oscar

75

96

102

 

 

79

78

107

1. For each cashier, compute his or her overall productivity (in orders per hour) over all eight days. You should have one number per cashier. (3pts) (3pts)

2. For each cashier, compute his or her productivity (in orders per hour) over the period 2/1 through 2/4. Using the MIN function, find the lowest productivity for 2/1 – 2/4 among the cashiers. (5pts)

3. For each cashier, computer his or her productivity (in orders per hour) over the period 2/5 through 2/7. Using the MAX function, find the highest productivity for 2/5 – 2/7 among the cashiers. (5pts)

 

Question Set 2. For this question set, you will calculate labor productivity and multifactor productivity, both in tons of rock salt produced per dollar value of inputs.

 

The Delaware Salt Company employs 90 full-time line workers (each working 40 hours per week at a wage of $12/hr) and 6 managers, each earning a salary of $1,500/week. The company has capital expenses (plant, property, and equipment) of $24,000 per week and produces 180,000 tons of rock salt per week. The company recently installed new equipment that will increase weekly output by 9% and weekly capital expenses by 5%. You will need the following formulas:

 

Labor productivity =

 

Multifactor productivity =

 

After copying the table below into Excel, solve the following questions:

1. Calculate labor productivity using just the line workers (do not include the managers) for the old and new systems, in tons of output per dollar of input. (4pts)

2. Calculate labor productivity including all employees (line workers & managers) for the old and new systems, in tons of output per dollar of input. (4pts)

3. Calculate the multifactor productivity (including line workers, managers, and capital expenses) for the old and new systems, in tons of output per dollar of input. (4pts)

4. Calculate the percentage changes in labor and multifactor productivity. (3pts)

 

 

Labor Productivity

(excluding managers)

Labor Productivity

(including managers)

Multifactor Productivity

Old System

 

 

 

New System

 

 

 

Percentage Change

 

 

 

 

Question Set 3.  A small manufacturing plant produces specialized stainless steel valves for high-pressure steam systems. Each valve costs $3000 to produce. The plant incurs $2,700,000 in fixed annual costs. The plant sells the valves directly to power plants for $5400 each. For this question set, use the following formulas:

 

[Total Profit] = [Total Revenue] – [Total Cost]

[Total Revenue] = [Production] x [Unit Revenue]

[Total Cost] = [Production] x [Variable Unit Cost] + [Fixed Costs]

 

1. Create a data table (as demonstrated during lab exercise 2), that shows what total profit would be if the company produced 1500 to 2500 valves, in increments of 50. You must use a data table structure to receive credit for this problem.(6pts)

2. Create a scatter chart that displays the variable total profit (and no other variables) as a function of the number of valves produced and sold. At low production quantities, total profit will be negative but should still be displayed. Label your chart axes appropriately. (6pts)

 

About the Author

Follow me


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}