# Excel Expert for @Risk Analysis

Posted 4 weeks ago Paid on delivery
Paid on delivery

1.

Construct a queueing model that replicates a week of collecting and processing DNA samples.

a. First develop a flow diagram of how the process works, from the reporting of a crime, to

the eventual match/no match on the DNA database.

b. Identify any conditional aspects of the process, for example if there are any stages of the

process where resources only become available once the previous sample has been

processed.

c. Second, write out the relevant equations associated with each step of the process, and

construct a formula for the overall time in system.

d. Use relevant notation and write out equations using Equation Editor in Word.

e. Note – the rule of thumb is that any reader can pick up your report and replicate your

results by following the methods.

2.

Use mean values of each parameter to construct an “average” solution.

a. Unit of interest – a DNA sample collected by a CSI. That is, each sample must be modelled

individually.

b. Assume both CSI and forensics lab operations work 24/7.

c. Model precisely ONE WEEK of sampling – any samples that are have not been completely

processed by one week are not included in your analysis.

d. The unit of time to use in the model is minutes.

e. There is one Rapid DNA sequencer, which is only run once all eight spaces in the cartridge

are filled with samples.

f. You MUST use Excel to model the process.

g. You MUST create, develop and save all your work in one file saved on your OneDrive

account. There are no exceptions to this. See Saving and Sharing instructions on

pages 10-12.

3.

Results to find from your “average” model solution:

a. To the nearest ten, how many samples will be processed by the end of the seven days?

How many of those samples will result in a DNA match?

b. What is the mean time to process a sample?

c. What percentage of samples meet the police force’s ambition to process samples in less

than one day?

d. Is there any stage of the process that looks under-resourced? Why?

Construct a queueing model that replicates a week of collecting and processing DNA samples.

a. First develop a flow diagram of how the process works, from the reporting of a crime, to

the eventual match/no match on the DNA database.

b. Identify any conditional aspects of the process, for example if there are any stages of the

process where resources only become available once the previous sample has been

processed.

c. Second, write out the relevant equations associated with each step of the process, and

construct a formula for the overall time in system.

d. Use relevant notation and write out equations using Equation Editor in Word.

e. Note – the rule of thumb is that any reader can pick up your report and replicate your

results by following the methods.

2.

Use mean values of each parameter to construct an “average” solution.

a. Unit of interest – a DNA sample collected by a CSI. That is, each sample must be modelled

individually.

b. Assume both CSI and forensics lab operations work 24/7.

c. Model precisely ONE WEEK of sampling – any samples that are have not been completely

processed by one week are not included in your analysis.

d. The unit of time to use in the model is minutes.

e. There is one Rapid DNA sequencer, which is only run once all eight spaces in the cartridge

are filled with samples.

f. You MUST use Excel to model the process.

g. You MUST create, develop and save all your work in one file saved on your OneDrive

account. There are no exceptions to this. See Saving and Sharing instructions on

pages 10-12.

3.

Results to find from your “average” model solution:

a. To the nearest ten, how many samples will be processed by the end of the seven days?

How many of those samples will result in a DNA match?

b. What is the mean time to process a sample?

c. What percentage of samples meet the police force’s ambition to process samples in less

than one day?

d. Is there any stage of the process that looks under-resourced? Why?

7

4.

Construct a simulation model using the information provided to you above, and your average

model as the starting point.

a. Unit of interest – a DNA sample collected by a CSI. That is, each sample must be modelled

individually.

b. Each iteration of the simulation is one week of processing samples.

c. Use the same assumptions as for the Task 2 model.

d. You MUST use @RISK. Do NOT save raw simulation data in the file.

e. You must save all your work in one file saved on your OneDrive account. There are no

exceptions to this. See Saving and Sharing instructions on pages 10-12.

f. You will need to determine the appropriate probability distribution for each input. g. Run

the model for 10,000 iterations.

h. Set the random number seed to the customer id.

4. Results to find for the simulation model:

a. Present all time units in minutes.

b. Summarise* the distribution for the mean time a sample waits in the queue for the: CSI

to arrive; for sample preparation; for DNA sequencing; and finally for validation.

c. Comment on any observations/insights from the queueing time distributions you have

plotted and summarised in Task 4a.

d. Summarise* the distribution for the mean number of samples processed, and the mean

number of samples matched to the DNA database, in one week.

e. Plot and summarise the distribution for the mean time to process a sample.

f. Plot and summarise the distribution for the percentage of samples that are processed in

under 24 hours (each iteration being one week of sampling).

g. How confident are we that at least half of all samples in one week will be processed in 24

hours or less?

h. Conduct an appropriate Sensitivity Analysis that shows the main influence on the time it

takes a sample to be processed.

i. Conduct any other relevant, ad-hoc analyses that you think will yield useful insights to

help reduce the time to process samples further.

5. What-if Scenario modelling

a. Taking your further analyses from Tasks 4f and 4g as the basis for improving the model,

construct 2-3 plausible scenarios that will improve the confidence that at least half of all

samples from one week are processed in 24 hours or less.

b. Modify at least two stages of the model (these must be reasonably unique – do not copy

scenarios from others).

c. Rerun simulation model with the same random number seed and compare results to that

d. By plotting and comparing relevant probability distributions, explain how your modified

scenario has reduced the time taken to process samples, and how it has impacted the

likelihood of processing samples in under one day.

6. Insights and recommendations

a. In your report, provide a short summary of your insights and recommendations for how

to improve the Rapid DNA process that was trialled over the past few months.

*Appropriate metrics to summarise a distribution are either mean(5th percentile;95th percentile) or

median(5th percentile;95th percentile), depending on the skewness of the distribution.

Project ID: #38027118

22 proposals Remote project Active 3 weeks ago

## 22 freelancers are bidding on average £137 for this job

schoudhary1553

Top 1% in Freelancer.com Hi, Greetings! ✅checked your project details: ✅Completed Time: In project deadline We have worked on 900 + Projects. I have 6 + years of the experience in same kind of projects. If you are look More

£180 GBP in 4 days
(627 Reviews)
8.6
snehamehta525

Hello, Greetings of the day! I have a vast experience of working in Advanced Excel and I have completed many projects based on Advanced Excel. I have read your requirements and I can complete your project within time More

£250 GBP in 5 days
(52 Reviews)
5.9
nycer847

Hello Dear, I hope you are doing great and are in good health. I see that you need a statistical data analyst. I am an expert data analyst and research consultant with a deep understanding of several financial models More

£150 GBP in 5 days
(10 Reviews)
5.4
GKEvans

Hello, Is this an assignment for a course? I can help with the Excel if you can write your own report and come up with the ideas .e.g. 'Conduct any other relevant, ad-hoc analyses that you think will yield useful insig More

£135 GBP in 7 days
(4 Reviews)
4.9
carlitanjuguna05

Hey! With over 5 years of dedicated experience as a writing professional, I bring a wealth of expertise across Data Processing, Excel, Mathematics, Visual Basic and Statistics . My extensive background in this field en More

£133 GBP in 2 days
(3 Reviews)
4.0
zubairmbafinance

Hello Sara, As an Excel Expert, I'm confident in my ability to complete this job with accuracy. If you haven't already started the project, please contact me so we can resume our work and bring it to completion. Looki More

£90 GBP in 2 days
(23 Reviews)
4.1
shahajalalbabu00

Hi there! As a professional and dedicated freelancer, I understand the value of accurate and efficient data entry. My expertise in Data Entry, Data Processing, and Excel will surely prove useful for this project. I hav More

£100 GBP in 2 days
(36 Reviews)
3.9
wilfrednjorog

Hi there, Good afternoon? I hope this proposal finds you well. As an experienced freelancer, I have honed my skills in Data Processing, Mathematics, Excel, Statistics and Visual Basic and am confident in delivering More

£150 GBP in 1 day
(4 Reviews)
3.8
Imtiwrites99

Hey Sara M., I have carefully read through the detailed project description for the Excel Expert for @Risk Analysis project. To begin, I will construct a queueing model that replicates a week of collecting and proces More

£50 GBP in 1 day
(2 Reviews)
3.0
TayyabDastgir

Hello, Kindly consider initiating a CHAT to transform your aspirations into tangible reality, as I am eager to delve into the intricacies of your project and collaborate effectively to achieve your desired outcome. For More

£30 GBP in 1 day
(5 Reviews)
3.0
durgesh8527

As a full-stack developer with over a decade of experience, my expertise in Excel would be a valuable asset to successfully construct the queueing model required for your DNA sample analysis. My proficient understandin More

£50 GBP in 7 days
(1 Review)
2.8

With over a decade of experience, I bring to the table a unique combination of expertise in data processing, Excel, mathematics, and Visual Basic skills, making me perfectly suited to tackle your project. I have a prov More

£31 GBP in 1 day
(5 Reviews)
1.9
laryssya

Hello, I'm reaching out because I'm really interested in the project you're working on. I've got solid experience with Excel and data organization, and I'm genuinely fascinated by forensics and true crime. I'm the ki More

£155 GBP in 7 days
(1 Review)
0.8
mustdeveu

Hi sir, As an experienced professional with outstanding mathematical capabilities and a proven expertise in software development, I believe I am the ideal candidate for this project. With over 38 years spent honing my More

£135 GBP in 7 days
(0 Reviews)
0.0
mudassarsharif24

As an IT solutions provider with extensive experience in data processing, Excel and statistics, I believe I am the perfect fit for your @Risk Analysis project. My team at Techfusion specializes in developing comprehens More

£30 GBP in 7 days
(0 Reviews)
0.0
prsergio

With a robust expertise of over 15 years in the realm of data science and analysis, I present myself as the perfect candidate for your @Risk Analysis project. My portfolio includes an extensive list of accomplishments More

£100 GBP in 2 days
(0 Reviews)
0.0