电子表格建模代写 MSCI 242代写管理电子表格建模代写

2022/11/02Excel代写电子表格建模代写 Excel建模代写 MSCI 242代写电子表格建模代写管理电子表格建模代写243

MSCI 242 Spreadsheet Modelling for Management

电子表格建模代写 OPEN BOOK EXAMINATION (1.5 HOURS) This exam contains three sections. You must answer all of the questions in each section. Section A contains short

OPEN BOOK EXAMINATION (1.5 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 single question worth 25% of the total marks

SECTION A (25% of marks) – Excel Functions and Formulas 电子表格建模代写

Use the dataset below for Sections A, B and C

The dataset you will analyse is sales data for 7 products, over a period of one calendar year.

The 7 product codes are as follows: A-18/C4, A-18/C5, A-18/C6, A-18/C7, A-18/C8, B-18/C9, B-18/C10

Each month’s sales data is held on their own, separate sheet.

The structure of the data is identical on each sheet except that some months have more days than others.

The 12 sheet names are as follows: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec

Below is a screenshot of the sheet for February’s sales.

Note: day 11 through to day 24 have been hidden in the screenshot above, for presentation purposes.

Column A contains the day of the month number: 1 to 28 for this month, as there are 28 days in February.

Column B contains the day of the year number: January has 31 days, so February is 32 through to 59.

Column C denotes whether the day is a weekend day: 1 = Saturday or Sunday. All other weekdays are 0.

Columns D through to J are the number of units sold for each of the 7 products.

For example, on day 1 of February, 65 units of product A-18/C4 were sold. On day 2, 74, and so on.

Additional calculations include:

• total monthly sales for each product, in cells D2:J2

• average monthly sales for each product, in cells D3:J3

• maximum number of units sold that month, for each product, in cells D4:J4

• total sales for each day, across all 7 products, in column L [e.g. day 1 is 653 units]

• total sales for the whole month, in cell L2 [20,002 units]

• average daily sales, across all 7 products, in cell L3 [714.36 units per day]

• maximum daily sales, across all 7 products, in cell L4 [829 on day 26]

All Part A questions only refer to the data for February 电子表格建模代写

Question 1

What formula is in cell L7, to calculate the total number of units sold for day 1? (2 marks)

Question 2

What formula is in cell D2, to calculate the total number of units sold for product A-18/C4? (2 marks)

Question 3

What formula is in cell D3, to calculate the average number of units sold for product A-18/C4? (2 marks)

Question 4

What formula is in cell D4, to calculate the maximum number of units sold for product A-18/C4? (2 marks)

Question 5

Suppose we want to know how many units have been sold this month on just weekend days (Saturday and Sunday). Write the formula that would achieve this. (2 marks)

Question 6 电子表格建模代写

What will this formula return?

=INDEX(D6:J6,MATCH(MAX(D2:J2),D2:J2,0)) (2 marks)

Question 7

Suppose we want to know how many units have been sold in total on the first Monday of February. Write the formula that would achieve this. (2 marks)

Question 8

What will this formula return?

=INDIRECT(RIGHT(G6,2)) (2 marks)

Question 9

Suppose we want to know whether the day with the highest sales in February was a weekend day or a weekday. Write the formula that would tell you this. (4 marks)

Question 10

Suppose we want to know the total units sold on Saturdays. Write the formula that would achieve this. (5 marks)

SECTION B (50% of marks) – Excel Model Development 电子表格建模代写

The following section is multiple choice. Submit one answer to each question.

The following summary table has been constructed on a sheet called Summary to present the number of units sold for each product, for every month of the year.

In addition to this summary table, a Data Address Matrix has been created on the same Summary sheet to store the addresses for the each month’s sales data, for each of the 7 products:

Question 11

Cell F7 calculates the total units sold for the A-18/C4 product in January. Which formula could be placed in cell F7 and be copied across and down to cell L18, to complete the summary table for all 7 products, for all 12 months?

A: = Jan!$D$2

B: = MONTH($D$2)

C: = SUM(INDIRECT(Q7))

D: = INDIRECT(D7&$D$2)

E: = INDIRECT(Q7)

F: = INDIRECT(SUM(Q7))

G: none of the above

(5 marks)

Question 12 电子表格建模代写

Suppose we want to know the total sales for all products, for the months with 31 days. Which single formula could achieve this?

A: = SUMIF(E7:E18,31,N17:N18)

B: = COUNTIF(E7:E18,31)

C: = SUMIF(N7,N9,N11,N13,N14,N16,N18)

D: = N7+N9+N11+N13+N14+N16+N18

E: = SUMIF(D7:D18,OR(“Jan”,”Mar”,”May”,”Jul”,”Aug”,”Oct”,”Dec”),N7:N18)

F: = SUM(MONTHDAYS(31))

G: none of the above

(5 marks)

Question 13

Suppose we want to know the total sales for all products for the six-month period from March through to August. Which single formula could achieve this?

A: = SUMIF(C7:C18,AND(“>=3″,”<=8”),N7:N18)

B: = SUM(F9:N14)

C: = SUMIF(D7:D18,OR(“Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”),N7:N18)

D: = SUMIF(C7:C18,”>2″,N7:N18)-SUMIF(C7:C18,”>8″,N7:N18)

E: = SUM(N7:N18)

F: = SUMMONTHS(C7:C18,3-8,N7:N18)

G: none of the above

(5 marks)

Question 14 电子表格建模代写

What will this formula return?

= INDIRECT(LEFT(D8,1)&INDIRECT(RIGHT(K6,2))-1)

A: #REF!

B: 233015

C: a blank cell

D: 34661

E: 35708

F: 35709

G: none of the above

(5 marks)

Question 15

Cell Q7 produces the data address text string for the A-18/C4 product in January. Which formula could be placed in cell Q7 and be copied across and down to cell W18, to complete the data address matrix for all 7 products, for all 12 months?

A: = $D7&”!”&Q$7&”7:”&Q$6&$E7+6

B: = $D7&”!”&Q$6&”7:”&Q$6&$E7+6

C: = D$7&”!”&Q$6&”7:”&Q$6&E7+6

D: = $D7&”!”&Q$6&”7:”&Q6&$E7+6

E: = $D7&”!”&Q$6&”7:”&$Q$6&$E7+6

F: = $D7&”!”&$Q$6&”7:”&Q$6&$E7+6

G: none of the above

(5 marks)

Question 16 电子表格建模代写

What will this formula return?

= MAX(INDIRECT(Q8))-AVERAGE(INDIRECT(R8))

A: #REF!

B: 1242.75

C: 0

D: 90

E: 28.11

F: -77.36

G: none of the above

(5 marks)

An additional column O has been added to the summary table, to extract the data for a dynamic chart, based on a numerical choice inputted by the user in cell O4.

Cell O4 has been named chart_choice and currently contains a 1, and thus will extract the data for the 1^stof the seven products (A-18/C4).

Cell J4 contains the text that will be used for the chart’s dynamic title.

Question 17

What is the formula in cell O7 that has been copied down to cell O18, to extract the chosen data based on the value in the chart_choice cell (O4)?

A: = INDEX(F7:L18,chart_choice)

B: = INDEX(F6:L6,chart_choice)

C: = INDEX(F7:L7,”chart_choice”)

D: = IF(chart_choice=1,F7,IF(chart_choice=2,G7,IF(chart_choice=3,H7,IF(chart_choice=4,I7,

IF(chart_choice=5,J7,IF(chart_choice=6,K7,L7)))))

E: = INDEX(F7:L7,chart_choose)

F: = INDEX(F7:L7,O4)

G: none of the above

(5 marks)

Question 18

What is the formula in cell J4, to create a dynamic title for the chart?

A: = “Monthly sales for “&F6

B: = “Monthly sales for “&INDEX(F6:L6,chart_choice)

C: = “Monthly sales for “& chart_choice

D: Monthly sales for A-18/C4

E: = “Monthly sales for “AND(chart_choice)

F: = “Monthly sales for A-18/C4”

G: none of the above

(5 marks)

A macro has been created which compiles all 12 months data onto a single data sheet, called All Data!

A screenshot of this compiled datastack is given below.

Note it only shows the first 5 days and the last 5 days of the year, just for presentation purposes.

Question 19

Suppose we wish to know, for each product, which day of the year saw the most units sold. What is the formula in cell E4 that has been copied across to cell K4?

A: =INDEX(B7:B371,MATCH(MAX(E7:E371),E7:E371,0))&”-“

&INDEX(A7:A371,MATCH(MAX(E7:$E371),E7:$371,0))

B: =INDEX($B$7:$B$371,MAX(E7:E371))&”-“&INDEX($A$7:$A$371,MAX(E7:E371))

C: =INDEX($B7:$B371,MAX(E7:E371))&”-“&INDEX($A7:$A371,MAX(E7:E371))

D: =INDEX($B7:$B371&”-“&$A7:$A371,MATCH(MAX(E7:E371),E7:E371,0))

E: =FIND(MAX(E7:E371),$B7:$B371&”-“&$A7:$A371)

F: =INDEX($B7:$B371,MATCH(MAX(E7:E371),E7:E371,0))&”-”

&INDEX($A7:$A371,MATCH(MAX(E7:E371),E7:E371,0))

G: none of the above

(5 marks)

Question 20

Suppose we wish to know, for each product, how many weekend days saw sales greater than 50% higher than the annual average. What is the formula in cell E5 that has been copied across to cell K5?

A: =COUNTIF(E7:E371,”>”&E3+E3/2)

B: =COUNTIF(E7:E371,E3+E3/2)

C: =COUNTIFS($D7:$D371,1,E7:E371,”>”&E3*1.5)

D: =COUNTIFS($D7:$D371,1,E7:E371,”>”&E3+50%)

E: =COUNTIFS($D7:$D371,1,E7:E371,1.5*AVERAGE(E7:E371))

F: =COUNTIFS($D7:$D371,1,$E7:$E371,”>”&1.5*E3)

G: none of the above

(5 marks)

SECTION C (25% of marks) – VBA 电子表格建模代写

Question 21

As mentioned previously, a macro has been created to compile all 12 months data onto a single data sheet called All Data!

The macro is presented below. The macro copies and pastes each month’s sales data onto the All Data sheet, and the month name is added to column A to create the datastack shown on the previous page.

A message box is shown at the end to inform the user of a successful compilation.

However, the macro has some mistakes in the code, which will cause the macro to either crash, or not compile the data correctly.

Identify 5 separate mistakes and for each ‘mistake’:

• Identify the line of code by number (1 – 24)

• Describe the mistake

• Provide the correct version of the code, or describe the solution to the mistake

Note: a line may contain more than one mistake.

Note: only the first 5 mistakes presented will be marked. Any other mistakes presented will be ignored. (25 marks)