MSCI 242 Spreadsheet Modelling for Management
管理电子表格建模代写 OPEN BOOK EXAMINATION (24 HOURS) This exam contains three sections. You must answer all of the questions in each section.
OPEN BOOK EXAMINATION (24 HOURS)
This exam contains three sections. You must answer all of the questions in each section.
Section A contains short questions, and is worth 25% of the total marks.
Section B contains longer questions, and is worth 50% of the total marks.
Section C contains a VBA question worth 25% of the total marks
Upload the completed document to moodle, either as a Word document or pdf.
SECTION A (25% of marks) – Excel Functions and Formulas 管理电子表格建模代写
Use the dataset below for Sections A, B and C
The datablock below shows the data for 3,050 speed cameras in the UK.
Every row in the datablock is a different camera. For each camera there is the following data:
| Component | Description | column |
| ID | a number from 1 to 3,050 | A |
| Partnership | The police district which operates the cameras. There are 23 different partnerships in the datablock, listed alphabetically | B |
| Installation Type | Cameras installed prior to 2002 are listed as ‘Pre’ and those from 2002 onwards are listed as ‘New’ | C |
| Camera Type | There are three types of camera; ‘Fixed’, ‘Mobile’ or ‘Red Light’ (meaning at a set of traffic lights) | D |
| Location | A brief description of where the camera is located, usually in terms of a road and town | E |
| Speed Limit | The speed limit, in mph, of the road where the camera is located. Most cameras are either 20, 30, 40, 50 , 60 or 70 mph but some cameras are located at junctions so have dual speeds, such as ‘30/40’ | F |
| Date Installed | When the camera was installed | G |
| KSI (3yr average) | The number of Killed and Seriously Injured in accidents at the camera ‘site’, over a 3-year period, expressed as an average per year | H |
| Year | The year the camera was installed. A formula based on the date in column G | I |
Note
Note 1: Only the first five cameras, and the last five cameras are presented in the screenshot above. The other rows of the datablock are currently hidden
Note 2: All data within the datablock is pure data except column I which is a formula
Note 3: Column B of the datablock does not contain any blank cells
Note 4: However, other columns may contain blank cells, or other text such as “unknown” or “missing”
Note 5: All cells that appear as a number (e.g. Speeds, KSI or Year) are all formatted as numbers
For each question in Section A present a single formula.
If you present more than one formula, then only the first one will be marked.
Question 1 管理电子表格建模代写
What formula is in cell B8, to calculate the number of cameras in the datablock? (2 marks)
Question 2
What formula is in cell D5, to calculate the total number of Fixed cameras (1604)? (2 marks)
Question 3
What formula is in cell F5, to calculate the total number of 30mph cameras (1669)? (2 marks)
Question 4
What formula is in cell H4, to calculate the total number of cameras with a KSI of zero (681)? (2 marks)
Question 5
What formula is in cell D8, to calculate the number of cameras that are neither Fixed, Mobile or Red Light? (2 marks)
Question 6
What formula is in cell F2, to calculate the number of Fixed cameras on a 30mph road? (2 marks)
Question 7
What formula is in cell H5, to calculate the number of cameras with a KSI greater than zero to 1? (2 marks)
Question 8 管理电子表格建模代写
What formula in cell D2 calculates the total KSI for Fixed cameras? (2 marks)
Question 9
What formula in cell D3 calculates the total KSI for Fixed and Mobile cameras? (2 marks)
Question 10
What single formula would calculate the average KSI for Fixed and Mobile cameras? (2 marks)
Question 11
What formula would calculate the average KSI for Fixed and Mobile cameras on a 30mph road (do not include any ‘dual speed’ cameras)? (2 marks)
Question 12
What formula has been entered in cell I11 and copied down to I3060 to determine the year the camera was installed, based on the date value in column G? If no valid date is entered in column G then the formula returns the text “unknown”. (3 marks)
SECTION B (50% of marks) – Excel Model Development 管理电子表格建模代写
The following section is multiple choice, except for questions 23 and 24
Submit one answer to each question.
The following summary table has been created to summarise the data for the 23 Partnerships.
Question 13
Column N presents the total number of cameras for each Partnership.
Which of the following formulae could be entered into cell N11, and then copied down to cell N33 to produce all 23 values? (4 marks)
A: =COUNT($B$11:$B$3060,$M$11)
B: =COUNTA($B$11:$B$3060,$M11)
C: =COUNTIF(B$11:B$3060,M$11)
D: =COUNTIF(B11:B3060,M11)
E: =COUNTIF($B$11:$B$3060,M11)
F: none of the above
Question 14
Column O presents the total KSI for each Partnership.
Which of the following formulae could be entered into cell O11, and then copied down to cell O33 to produce all 23 values? (4 marks)
A: =SUMIF(H11:H3060,B11:B3060,M11)
B: =SUMIFS($H$11:H$3060,B$1:B$3060,$M11)
C: =SUMIFS(H$11:H$3060,B$11:B$3060,M11)
D: =COUNTIF($B$11:$B$3060,M11,$H$11:$H$3060)
E: =SUMIF($B$11:$B$3060,$M$11,$H$11:$H$3060)
F: none of the above
Question 15 管理电子表格建模代写
Column P presents the total number of 30mph cameras for each Partnership, not including any ‘dual speed’ cameras. Which of the following formulae could be entered into cell P11, and then copied down to cell P33 to produce all 23 values? (4 marks)
A: =COUNTIFS($F$11:$F$3060,30,$B$30:$B$306,M11)
B: =COUNTIFS($F$11:$F$3060,30,$B$11:$B$3060, “Avon”)
C: =COUNTIFS(F$11:F$3060,30mph,B$11:B$3060,M$11)
D: =COUNTIFS($F$11:$F$306,30,$B$11:$B$3060,$M11)
E: =COUNTIFS($F$11:$F$3060,”30”,$B$11:$B$3060,$M$11)
F: none of the above
Question 16
Columns Q and R presents the total number of 40mph and 50mph cameras respectively, for each Partnership, not including any ‘dual speed’ cameras. Which of the following formulae could be entered into cell Q11, and then copied across to R11 and down to cell R33, to produce all 46 values? (4 marks)
A: =COUNTIFS($F$11:$F$3060,$M11,$B$11:$B$3060,Q$10)
B: =COUNTIFS($F$11:$F$3060,$Q$10,$B$11:$B$3060,$M11)
C: =COUNTIFS($F$11:$F$3060,Q10,$B$11:$B$3060,M11)
D: =COUNTIFS($F$11:$F$3060,Q$10,$B$11:$B$3060,M11)
E: =COUNTIFS($F$11:$F$3060,Q$10,$B$11:$B$3060,$M11)
F: none of the above
Question 17 管理电子表格建模代写
Column S presents the total number of cameras for each Partnership, which are not 30mph or 40mph or 50mph. Which of the following formulae could be entered into cell S11, and then copied down to cell S33 to produce all 23 values? (4 marks)
A: =P11+$Q11+$R11-$N11
B: =COUNTIFS(F$11:F$3060,NOT(30,40,50),B$11:B$3060,M11)
C: =COUNTIFS(F$11:F$3060,AND(“<30”,”>50”),B$11:B$3060,M11)
D: =COUNTIFS($F$11:$F$3060,”<30”,$F$11:$F$3060,”>50”,$B$11:$B$3060,$M11)
E: =COUNTIFS(F$11:F$3060,OR(“<30”,”>50”),B$11:B$3060,M11)
F: =$N11-SUM($P11:R11)
G: none of the above
Question 18
Column T presents the average KSI per camera for 30mph cameras for each Partnership, not including any ‘dual speed’ cameras. Which of the following formulae could be entered into cell T11, and then copied down to cell T33 to produce all 23 values? (4 marks)
A: =AVERAGEIF($F$11:$F$3060,30,$H$11:$H$3060)
B: =AVERAGEIKSI($H$11:$H$3060,$F$11:$F$3060,30,$B$11:$B$3060,M11)
C: =AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,T$10,$B$11:$B$3060,$M11)
D: =AVERAGEIFS(H$11:H$3060,F$11:F$3060,30,B$11:B$3060,$M$11)
E: =O11/P11
F: =(O11*(P11/N11))/P11
G: none of the above
Question 19 管理电子表格建模代写
Column V presents the average KSI per camera for 50mph cameras for each Partnership, not including any ‘dual speed’ cameras. Which of the following formulae could be entered into cell T11, and then copied down to cell T33 to produce all 23 values?
Any Partnerships with no 50mph cameras should return a blank cell, rather than a #DIV/0! error. (4 marks)
A:=IFERROR(AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,M$11),””)
B:=IF(R11=0,””,AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,$M11))
C:=IF(R11>0,AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,$M11))
D:=IF($R11>=1,AVERAGEIFS($H$11:$H$3060,$F$11:$F$3060,50,$B$11:$B$3060,M11)),””)
E:=IF(ISBLANK(R11),””,AVERAGEIFS(H$11:H$3060,F$11:F$3060,50,B$11:B$3060,M11))
F: =AVERAGEIFS(H$11:H$3060,F$11:F$3060,50,B$11:B$3060,M11)*R11
G: none of the above
Question 20
Cell R3 contains the formula: =MIN(R11:R33)
to find the minimum number of 50mph cameras in any Partnership (the answer being zero)
Which formula in cell R4 determines which Partnership this is for? (the answer being Cambridge) (4 marks)
A: =Cambridge
B: =M3
C: =INDEX(M11:M33,R3)
D: =INDEX(M11:M33,MATCH(0,R11:R33))
E: =(MATCH(R3,R11:R33,0),INDEX(M11:M33))
F: =(INDEX(M11:M33,MATCH(R3,R11:R33,0)))
G: none of the above
Question 21
What will the following formula tell us? (4 marks)
A: how many 50mph cameras there are in Norfolk
B: the row number in the table for another Partnership with zero 50mph cameras
C: how many 50mph cameras there are in Kent
D: how many 50mph cameras there are in Dorset
E: how many Partnerships have six 50mph cameras
F: it will produce an error
G: none of the above
Question 22
What will the following formula tell us? (4 marks)
A: how many 50mph cameras there are in Cambridge
B: how many 50mph cameras there are in Dev and Corn
C: the average KSI for 50mph cameras in Cambridge
D: the value of cell V3
E: the average KSI for 50mph cameras Dev and Corn
F: it will produce an error
G: none of the above
Question 23
The highest observed KSI for any individual camera site is 15.0 (see cell Y7).
What single formula will determine the Partnership, camera type and speed for this camera site, expressed as a text string in the following format:
Notts – Fixed – 40mph
Ensure the formula is dynamic such that if the KSI data changes the result would update. (4 marks)
Question 24
In the datablock, there are two cameras at the location: “A1013 London Road, Grays”
a) what formula will produce the KSI for the first camera listed in the datablock? (2 marks)
b) what formula will produce the KSI for the second camera listed? (4 marks)
SECTION C (25% of marks) – VBA 管理电子表格建模代写
Question 25
A macro has been created to copy the data from the main datablock onto 23 individual datasheets, one for each Partnership, so that each datasheet stores the camera data for just that Partnership.
All 23 datasheets have already been created but are currently empty, apart from the headings in row 10 – see the screenshot below.
Every individual datasheet is identical, as below, apart from the name of the sheet, which is the name of the Partnership; Avon, Bedford, Cambridge and so on, as they appear in column M of the summary table.
The main datablock is held on a sheet called data.
However, due to errors in the main datablock the macro also has to make the following 2 alterations to the data stored on the individual datasheets – but does not alter the data in the main datablock.
- If the value in the Year column (column I) is prior to 2002 then the Installation Type (column C) must be set to the text “Pre”.
- If the value in the Speed Limit column (column F) is either blank or a zero value then replace this with the text “unknown” and count how many times this has occurred for each Partnership. Present the answer in a message box formatted like this one:
The macro presented below will not produce the desired results as it has some mistakes in the code, which will cause the macro to either crash, or not produce the correct outcome.
Identify 5 separate mistakes and for each ‘mistake’:
- Identify the line of code by number (1 – 33)
- Identify the specific code which causes the error
- Provide the correct version of the code, or describe the solution to the mistake
Note 1: a line may contain more than one mistake.
Note 2: only the first 5 mistakes presented will be marked. Any other mistakes presented will be ignored. (25 marks)
更多代写：github代码查重 托福代考多少钱 英国Chemistry网课代修 contrast essay代写 Book Summary代写 简历求职信代写
