Mathematics

You can download the solution to the following question for free. For further assistance in Mathematics assignments please check our offerings in Accounting assignment solutions. Our subject-matter experts provide online assignment help to Accounting students from across the world and deliver **plagiarism free solution** with **free Grammarly report** with every solution.

(ExpertAssignmentHelp does not recommend anyone to use this sample as their own work.)

**Use appropriate Excel formula to answer the following:**

You should **include all your calculations** and **provide a narrative** that **explains the formulae** you have used within the Excel spreadsheet for submission.

You have been asked to review the costs of operations at a production plant that employs 25 staff members. Some of these are permanent staff and some are temporary staff. Since 2009, there have always been 25 staff employed in the production plant, but the ratio of permanent staff to temporary staff has varied. All staff work a 40 hour week.

You also have data available since 2009, that relates to the costs of 'defects'.

A 'defect' is a product that is not made to a satisfactory standard, and needs to be recalled and replaced.

This data is shown in the Excel spreadsheet as follows:

- Staff costs

- The number of permanent staff employed in a given quarter (13 weeks)
- The number of temporary staff employed in a given quarter (13 weeks)
- The costs/hour of permanent staff
- The additional pensions costs for permanent staff
- The costs/hour of temporary staff
- The additional agency fees for temporary staff

This data allows you to work out the staffing costs for each quarter.

- Costs of defective products

You know how many 'defects' there were in each quarter, and also the mean costs for each 'defect' in that period.

This data allows you to work out the costs of the 'defects' for each quarter.

The data relating to staff employed each quarter, with the associated costs, and the 'defects' in each quarteris presented in the Excel spreadsheet.

**Tasks**

NB Marks are awarded for accuracy and clarity of presentation.

Use a relevant Excel formula to calculate:

- The costs of permanent staff for each quarter.
- The costs of temporary staff for each quarter.
- The total defect costs for each quarter.
- The total costs (staff and defect costs) for each quarter.
- The mean number of defects per quarter over the whole period.

**You should include all your calculations and provide a narrative that explains the formulae you have used within the Excel spreadsheet for submission.**

- Review the above data and comment on the relationship between staff costs and the costs of defects.
**(2 Marks)**

You are now considering the impact of how the TOTAL COSTS are affected by the PERCENTAGE OF PERMANENT STAFF EMPLOYED (as a percentage of total staff employed).

So for example, in 2009:Q1, 8 permanent staff were employed out of the 25 total staff = 32%

Use a relevant Excel formula to calculate:

- Thepercentage of permanent staff employed in each quarter:

- The median percentage of permanent staff employed [in each quarter]

- Create a â€˜scatter diagramâ€™ of the PERCENTAGE OF PERMANENT STAFF EMPLOYED against TOTAL COSTS (staff and defect costs).

- Identify what percentage of permanent staff gives the lowest total costs.

- Explain what contributes to the shape of the curve in the diagram
- Based on the analysis of data, explain:

- What the business should consider doing to improve effectiveness
- What further investigations would help to inform decision making for the future

Presentation Marks for clarity of Data Table

Assignment Understanding Brief

Review your requirements with our FREE Assignment Understanding Brief and avoid last minute chaos.

Global PhD Experts

We provide you services from PhD experts from well known universities across the globe.

Free Grammarly Report

No more plagiarism worries. We give you a FREE Grammarly report with every assignment.

Delivery Before Deadline

Our experts work round the clock to provide you with solutions before the scheduled deadline.

**Answer 1.1.** Calculated in the excel sheet

**Answer 1.2.**Calculated in the excel sheet

**Answer 1.3.**Calculated in the excel sheet

**Answer 1.4.**Calculated in the excel sheet

**Answer 1.5.**Calculated in the excel sheet

**Answer 1.6.**

In review of the data related to staff costs and total defect costs, it can be easily said that the total defect costs are directly proportional to the costs of temporary staff and indirectly proportional to the costs of permanent staff.

**Answer 1.7.** Calculated in the excel sheet

**Answer 1.8. **Calculated in the excel sheet

**Answer 1.9. **Depicted in the excel sheet

**Answer 1.10.** Calculated in the excel sheet

**Answer 1.11.**

The curve is explained by the indirect proportionality existing between the percentage of permanent staffs and total costs, as we can see the median value of total costs is around the higher quartile of the percentage of permanent staffs.