Please Include Formulas In The Excel Worksheet ✓ Solved

```html

Please Include Formulas In The Excel Workshee

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. For each cashier, compute his or her overall productivity (in orders per hour) over all eight days. You should have one number per cashier.

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.

For each cashier, compute 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.

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: 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. Calculate labor productivity including all employees (line workers & managers) for the old and new systems, in tons of output per dollar of input. 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. Calculate the percentage changes in labor and multifactor productivity.

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]

Create a data table 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.

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.

Paper For Above Instructions

Introduction

This paper details the calculations required for various productivity metrics and profit analysis for the Delaware Salt Company and a stainless steel valve manufacturing plant. The undertakings include calculating labor productivity, multifactor productivity, and total profit based on the production output of the respective companies using Excel worksheets.

Question Set 1 Analysis

For the first set of questions, we need to compute the productivity of cashiers based on hours worked and orders processed across various days. The overall productivity can be calculated using the formula:

Productivity = Total Orders Processed / Total Hours Worked

For example, let's say the data for cashier 'Kevin' shows he worked 40 hours and processed 160 orders. His overall productivity would be:

Productivity = 160 / 40 = 4 orders/hour

In Excel, one would input the hours and orders for each cashier and apply the formula to calculate productivity. We would also compute productivity for the period from 2/1 to 2/4 using the formula:

Productivity = Total Orders from 2/1 to 2/4 / Total Hours from 2/1 to 2/4

Utilizing the MIN function, we find the cashier with the lowest productivity during this period, which corresponds to the least efficient operation.

For the following period from 2/5 to 2/7, the MAX function will help us identify the highest productivity value among cashiers, highlighting the most efficient worker in that span.

Question Set 2 Analysis

This sets the premise for calculating labor productivity using workforce and capital expense input parameters. Labor productivity can be computed as:

Labor Productivity = Total Output / Total Labor Cost

Given that the Delaware Salt Company has 90 workers earning $12/hour, the weekly labor cost would be:

Total Labor Cost = (90 Workers 40 hours/week $12/hour) + (6 Managers * $1,500/week) + Capital Expenses.

The new productivity metrics will account for increased output, which is 9% higher due to new equipment, while operational costs will increase by 5%. Calculate the new values accordingly and derive the updated productivity rates for both labor and multifactor productivity using total dollar expenditures as inputs.

Question Set 3 Analysis

For the valve manufacturing plant, we focus on the profit margins from valve production. We'll calculate the profit using:

Total Profit = Total Revenue - Total Cost

Where:

Total Revenue = Production Quantity * Selling Price per Valve

Total Cost = (Production Quantity * Variable Cost per Valve) + Fixed Costs.

Using a data table in Excel, we can evaluate profits iteratively across the production quantities from 1500 to 2500. Constructing a scatter plot will visually depict the relationship between production volume and total profit.

Conclusion

In conclusion, the above productivity measures and profit calculations provide critical insights into operational efficiencies and economic viability for the Delaware Salt Company and the manufacturing plant. Utilizing Excel functions effectively assists in processing these comprehensive datasets in a user-friendly manner.

References

  • Heizer, J., & Render, B. (2016). Operations Management. Pearson.
  • Krajewski, L. J., Ritzman, L. P., & Malhotra, M. K. (2013). Operations Management: Processes and Supply Chains. Pearson.
  • Harrison, A., & van Hoek, R. (2011). Logistics Management and Strategy. Pearson.
  • Mahajan, V. (2018). Data Analysis and Decision Making. Cengage Learning.
  • Chase, R. B., Jacobs, F. R., & Aquilano, N. J. (2010). Operations Management. McGraw-Hill.
  • Stevenson, W. J. (2015). Operations Management. McGraw-Hill.
  • Russell, R. S., & Taylor, B. W. (2014). Operations Management. Wiley.
  • Tamimi, N. (2006). Operations Management in the Supply Chain: Decisions and Cases. Prentice Hall.
  • Schroeder, R. G., & Goldstein, S. M. (2015). Operations Management in the 21st Century. Thomson South-Western.
  • Slack, N., Chambers, S., & Johnston, R. (2010). Operations Management. Pearson.

```

Related Assignments