Business Analytics II Project

ISDS 361B

Dr. Newby

Page 1

ISDS 361B - Simulation Model Project: Cost-Benefit Analysis

Due date: Check the course website for due date and time.

Contribution: 25 points (4% of the course grade)

The goal of this group project is for you to apply the concepts of spreadsheet modelling and

simulation that you have learned in class and apply them to a cost analysis for Bixie Manufacturing

company. From the results, your team will write a report with recommendations to the operation

manager, Mr. Humphrey Appleby.

This project will be carried out in teams of 2-3 members in a group.

Assignment Brief

You have been appointed as a business analyst team at Bixie Manufacturing. Your first project is to

provide an analysis of labor cost and make appropriate recommendations in relation to a hiring plan

with cost analysis.

A. Background Bixie Manufacturing has two manufacturing plants located in Tucson, Arizona. One of the

company’s products is the high-tech remote controlled monster truck series. The toy comes in four

different models: one for beginner (Type 1), one for intermediate users (Type 2), one for advanced

users (Type 3) and one for tech-savvy users (Type 4).

The company has been very successful in advertising the new toys, which has resulted in a

substantial increase in the product demand. A new work shift has been added to increase production

volume, but there is an increased concern over the quality of this highly popular product line. The

key component of the toy is its remote sensor. Quality control is responsible for thoroughly

inspecting and testing this part before releasing it to shipping. Every time a unit is found to be

defective, it is rejected and sent back to production for correction. Lately, Mr. Appleby noticed that

a large number of batches have been sent back to production for quality defects. If this trend

continues, the company will not be able to keep up with the demand, will suffer cost increases and

may end up losing their profits.

The management needs to evaluate their current cost situation and will strategically decide on what

to do next.

A sample of 100 items were selected across four different models. Time spent on functional tests

and sensor tests were recorded in minutes. The data can be found in the

dataForCostAnalysisProject.xlsx file. Using historical data, the average of labor cost is $37.00 per

hour. The data also shows, on average, between 1% to 4% of toys fail the remote sensor test during

inspection. The additional cost incurred if remote sensor fails after purchase is on an average $27.95

with a standard deviation of $12.50.

ISDS 361B

Dr. Newby

Page 2

B. Project Requirements 1. Using the information given in the background section, you are to conduct a cost-benefit analysis

by developing a spreadsheet model using Microsoft Excel that can be used to help the strategic

direction of the company in dealing with the remote sensor problem. (15 points).

The model should accurately represent the current situation and allow you to evaluate the

different costs, labor costs versus the return costs, and provide information to assist Mr. Appleby

to address the issues below:

a. Perform descriptive statistics analysis for the given data. b. Provide the total labor cost from the sample? (Hint: you are to calculate the labor cost for

each item first).

c. In a summary table, provide the total number of each model, average functional test for each model, average sensor test for each model and the average labor cost for each model (Hint:

you are to use AVERAGEIF function here).

d. Using the results from the historical data given in the background, you are to develop an Excel worksheet that will simulate the cost of not inspecting the remote sensor for a sample

of 200 trials for the criteria given below:

Trial Type Return cost

Probability of

failure Probable cost

Trial = number of trials simulated

Type = randomly generate numbers between Type 1 and Type 4

Return cost = calculation based on mean = $27.95, standard deviation = $12.50.

Probability of failure = calculation based on failure rate between 1% and 4%.

Probable cost = product of return cost and probability of failure rate

e. From the simulation results, in a table, report the probable cost for each model plus the total probable cost.

NOTE:

• Your model must follow the concepts: clear, simple and concise structure with clear distinction between inputs and outputs.

• Since this is an Excel Project, Excel functions must be used where appropriate.

2. From your developed model(s), you are to produce one report (up to 700 words) summarizing your cost-benefit analysis for the company (10 points)

• The report must address the questions (a-e) above.

• Include comments on your descriptive statistics.

• Describe how you developed your model

• What conclusions can you derive from your results regarding the labor cost from the sample and the simulated probable return cost?

• What recommendations do you have for the company based on your conclusions above?

• Assume that the labor cost can be reduced to $18 per hour, what is the revised cost for inspect and will the new results affect your cost-benefit analysis?

ISDS 361B

Dr. Newby

Page 3

• If the total labor cost from the sample is $100, what is the expected average labor cost for Bixie? (Hint: use Goal Seek for this).

• The report must be written in a narrative style. Do not use bullet points throughout to answer the listed questions (points will be deducted if you do so).

NOTE. All recommendations must be evaluated; use tables and figures to support your claims.

Submission requirements:

Using the project submission section on Titanium, submit 2 files: (a) a copy of your Excel file which

must include the model(s) you developed and (b) your report in Microsoft Word (or PDF).

C. Assessment Measures:

Points Assessments

15 Correctness:

• Use appropriate Excel functions in your model

• Interpret the results to make suitable business recommendations

10 Format and flow:

• The report is well organized, coherent, and looks professional

Important Notes on Report:

Report correctness, flow, and format:

Your report should be flawless, that is, error-free and professional looking. Each report must have the

following:

• A short introduction paragraph that gives the background to your study and a brief summary of what you have done in the report

• A discussion section covering the different analyses you have carried out and comments/evaluations on the results (you can have multiple paragraphs for this section)

• A conclusion section that wraps up and gives recommendations based on your analyses.

The report, including tables and figures/graphs, should not be more than seven (7) pages in length.

Quantity is no indicator of quality, however. A short but well-focused report is preferable to a long

and rambling one.

All pages must be numbered appropriately. Margins need to be 1 inch all around. Use Times New

Roman font, size 12. If possible, use headings for better readability. Do not indent paragraphs. Keep

them short (no more than 5-6 lines). Single space between lines but double space between

paragraphs.

Hint: Plan your report; if necessary outline what you will say. Respond to the task fully. Before

writing, analyze the context of the assignment by considering the requirements of the report and

address the following questions:

ISDS 361B

Dr. Newby

Page 4

• What is the purpose of the document to be written?

• What result do you hope to achieve by writing it?

• Who are your readers and what do they want from your document?

• What method of organization of your information is most useful?

Notes:

Be specific and give justification. Commonplaces and clichés should not be used. Give clear and

well-illustrated reasons with supportive evidence/analysis results (use charts, graphs and tables

where necessary to support your evaluation). Do not reference your Excel file in your report. Points

will be deducted if you do so. Arrange your argument/narrative/interpretation logically and

coherently. I will evaluate both form and content of your report.

Sample report

Below is a sample report taken from Guffey and Loewy (2018).