微信客服：xiaoxionga100
微信客服：ITCS521
BISM1201-程序代写案例
时间：2022-04-17
BISM1201
Transforming Business with Information Systems
Assignment
Semester 1, 2022
Course Coordinator: Associate Professor Stan Karanasios
bism1201@business.uq.edu.au
Assignment Specification Semester 1, 2022
There are two parts to this assessment.
Part A consists of three information systems questions (35 marks)
Part B consists of Excel questions to be answered in an Excel worksheet “BISM1201 Assignment Part
B Excel S1 2022.xlsx” (this file is available on course Blackboard site) (15 marks)
Worth: 50% of total assessment
Individual Completion: This assignment is to be completed individually by each student. Students
are encouraged to discuss issues that arise in this course together, however, the written work you
submit must be entirely your own. Similarly, you must not help another student to cheat by sharing
assignments (present or past).
Due Dates: The assignment is due by Tuesday 17/05/2022 at 3PM (Brisbane time). Submissions after
this date and time will be considered "Late" and relevant penalties will apply.
Submission
Part A and Part B must be submitted directly to the assessment section on Blackboard using the link
Assignment submission link (PART A and PART B) (see the assessment folder).
• Submit Part A as a PDF file via the submission link.
• Submit a copy of Part A on Turnitin – use the link Turnitin submission for Assignment PART
A. You must ensure that the Part A that you submit to Turnitin matches Part A submitted via
the submission link. We will not mark Part A submissions if there is no matching file on
Turnitin.
• Submit Part B as an Excel workbook file “BISM1201 Assignment Part B Excel S1 2022.xlsx”
(this file is available on course Blackboard site). Your answers should be entered into the
relevant cells of the Excel file, as per the assignment specification.
Your two submission files MUST be named (ALL CHARACTERS IN UPPERCASE) in the following
format: Tutorial number (comprising T followed by the full digit tutorial number i.e., T10, T02). This
tutorial number must then be followed by an underscore character, your full name followed by an
underscore character and Student_ID e.g., "T10_TIMSMITH_45894125”. (Student tutorial number
can be found on Blackboard under Class Timetable & links)
NOTE: You are allowed multiple attempts to submit (we only mark the last submission). However,
each time you submit using the submission link you should submit both files (PDF and Excel file),
because Blackboard treats each submission as new.
No hardcopy or email submission is required or accepted.
For information on how to submit Part A and Part B on Blackboard via the assignment submission
link, please visit:
https://web.library.uq.edu.au/library-services/it/learnuq-blackboard-help/learnuq-
assessment/blackboard-assignments/assignment-submission-instructions
For information on how to submit Part A to Turnitin, please visit:
https://web.library.uq.edu.au/library-services/it/learnuq-blackboard-help/learnuq-
assessment/turnitin-assignments
Presentation
• For Part A, you must follow the word length requirements (+/-10%).
• Your assignment must be presented using the sections as sub-headings (note: each section does
not need to start on a new page).
• Be sure to use double-spacing, 12-point font, page numbering and 2.5cm margins on all sides of
the page.
• Please proofread your assignment for spelling and grammatical errors.
Referencing and Citing
Part A questions require you to do basic analysis of the cases. You must base your answers and
terminology on the material covered in our weeks of information systems theory.
If you wish to report another author's point of view you should do so in your own words, and
properly reference in accordance with an approved referencing style. Direct quotations should be
used sparingly, form a small part of your work, and must be placed in quotation marks and
referenced.
Part A Question 1 and Question 2 require you to give a real-world example. Examples can come from
journals, newspapers, organisation websites, magazines etc. The real-world examples must be
referenced using in-text refencing and included in the reference list at the end of the assignment.
For example, “Nike uses a product differentiation information system strategy because it allows
customers to use its online platform to customise its sneakers. This means that Nike can use
information systems to offer a different value proposition than its competitors (Smith & Chang,
2010)”.
For more information on referencing styles, visit the library or see:
https://web.library.uq.edu.au/research-tools-techniques/referencing/referencing-style-guides. Use
the UQ Harvard style.
UQ value academic integrity, referencing others work is a key part of this. Please see this link for
more information on academic integrity: https://my.uq.edu.au/information-and-services/manage-
my-program/student-integrity-and-conduct/academic-integrity-and-student-conduct.
PART A: Information
Systems Theory
35 marks
Question 1. IS strategy (10 marks)
BISM-Desks is a family-owned firm that creates bespoke desks for homes and offices. Unlike some of
the major retailers, the only way for customers to purchase it’s desks is by visiting a show room in
Brisbane and buying floor stock or by designing a desk by choosing intricate details like material,
color, patterns, size, shelving, layout etc.
Because BISM-Desks relies on face-to-face sales the firm struggled throughout COVID-19 (2020-
2021). This led to a lost opportunity as desks, especially customisable desks for different spaces,
were in high demand.
BISM-Desks has used Porter’s Five Forces model to realise that it faces pressure from competitors
and new entrants and is concerned that their customers might move to online alternatives. In
response, the firm is considering ways to modernise and expand. While in the past it focused on
‘high-end’ customers, it believes that the normalisation of working from home means that there will
be even greater demand for customisable desks and that customers may be willing to pay a little
more for a desk. This is not so dissimilar to retailers like IKEA, however BISM-Desks’ products are
based on the highest quality materials, are Australian made and are not flat-packed/self-assembled.
While this means the cost of its products are higher, it allows BISM-Desks to differentiate itself from
large retailers.
The firm also recognises that it is not ‘digitally ready’. It has not relied on information systems to
help operate its business beyond basic word processing, email and keeping track of finances. The
owners are more interested with ‘building beautiful desks’ than in IT. The firm has no centralised
database nor is data captured to learn about customers or to support decision-making. There is no
way to track customer demographics or behaviour beyond what is learned about customers when
they visit the showroom. Likewise, inventory ordering is undertaken by email and phone which leads
to very slow links with suppliers. These issues are all coming to the forefront as the owners want to
expand the business whilst also getting ready for stepping back from the day to day running of it and
instead relying more on professional staff to run the business. However, they cannot do so without
more efficient and standardised processes.
BISM-Desks has recruited you as a business analyst. The owners would like advice on different
information systems strategies that can help the firm modernise itself and make the most of the
demand for its products.
Q1: Select two of the below information systems strategies and explain how each can help BISM-
Desks grow and be more competitive.
(i) Basic strategy/align IT with business objectives;
(ii) Low-cost leadership;
(iii) Product differentiation;
(iv) Focus on market niche;
(v) Strengthen customer or supplier intimacy.
Provide a real-world example1 for each information systems strategy you identified to show how it is
applicable to the case of BISM-Desks (i.e., one real-world example/reference per strategy)
(600 words total) (10 marks).
Question 2. Enterprise systems (5 marks)
BISM-Hotel, a medium sized luxury hotel operator in Brisbane, recently bought four other hotels
across Queensland as part of its plan to become the largest luxury hotel operator in Queensland.
BISM-Hotel has managed its customer details through a very basic Microsoft Access database and
used a booking application to manage transactions and bookings. This has worked relatively well in
the past but was limited in terms of understanding customer behaviour, trends and offering loyalty
programs and so on.
The hotel marketing manager who has worked with BISM-Hotel for ten years has traditionally used
her experience to decide customer-related decisions based on her own interactions with customers
rather than on data. For example, the marketing manager knows the most loyal customers, what
their preferences are, and their purchase patterns from years of interaction. She captures this data
in a spreadsheet, emails and notes. All in all, BISM-Hotels’ customer insights worked well enough for
a single city centre hotel, however with its expansion plans, BISM-Hotel wants to be able to build
more customer insights and encourage customers to stay at its other hotels.
BISM-Hotel would like to keep track of high value customers to ensure that they don’t leave for
other competitors. At the same time, the BISM-Hotel wants to keep track of the issues faced by
customers and resolve them. Recently BISM-Hotel noticed that its ratings and reviews on social
media have been dropping. This is a concern for a luxury hotel and it would like to improve
customers satisfaction. In particular, it has noticed that customer complaints and issues are received
via email, phone and face-to-face but there is no way to systematically record and monitor issues
and as consequence many do not get resolved.
A business analysis of the four other hotels BISM-Hotel acquired shows they all have their own way
of working, processes and systems. BISM-Hotel will therefore need to align customer relationship
management processes across all of its hotels.
You have started working at BISM-Hotel as a business analyst and your manager has asked you to
explain how a Customer Relationship Management (CRM) system could help BISM-Hotel with its
expansion plans and address the limitations of its current approaches to customer relationship
management.
Q2. Explain to your manager two reasons why BISM-Hotel would want to implement a Customer
Relationship Management (CRM) system. For each of your reasons give a real-world example2 and
apply it to the case (400 words total) (5 marks).
1 You should reference your examples. Examples should not be from the course textbook (or seminar/tutorial
material). The examples can come from any context, that is they do not have to be about furniture or small
firms.
2 You should reference your examples. Examples should not be from the course textbook (or seminar/tutorial
material). The examples can come from any context, that is they do not have to be about hotels.
Question 3. Information systems development (20 marks)
You have recently joined a new IT consulting firm called BISM-Digital which will specialise in
providing tailored and cutting-edge information systems to governments, banks, manufacturers, and
other large organisations.
BISM-Digital has been approached by several firms that have indicated they need: (i) new cutting-
edge information systems tailored to their organisation’s needs; (ii) information systems delivered as
quick as possible so as to better compete in the market; (iii) want to actively be involved and engage
with BISM-Digital throughout the information systems development process; and, (iv) that because
of the novelty of their information systems projects the development process must be able to
accommodate change. These firms have also expressed concerns that information systems
development projects are frequently late, have high failure rates and the end product rarely meets
their requirements.
Based on the needs of customers, your manager has tasked you with making a recommendation on
whether BISM-Digital should follow either a Scrum or Waterfall approach for information systems
development.
Q3. Make a recommendation to your manager that BISM-Digital follows either Scrum or Waterfall in
its information systems development projects. Using the Scrum/Waterfall process and concepts give
four reasons why Waterfall/Scrum could lead to more successful outcomes. In doing so, contrast
your reasons with the alternate approach (i.e., if you suggest Scrum then contrast your answer with
Waterfall). (800 words) (20 marks)
PART B: Excel
15 marks
The Excel workbook file for the tasks described below (BISM1201 Assignment Part B Excel S1
2022.xlsx) is available on the assessment tab on the course Blackboard site.
There are four worksheets in the workbook, labelled Task 1, Task 2, Task 3 and Report. Only edit the
indicated cells (the blue or green shaded cells). Do not alter/change/move any other cells, nor make
any new columns or rows.
Please add your (i) student name, (ii) student ID, (iii) tutorial number, and (iv) tutor name on the first
worksheet (in the blue box in the upper left corner). (Student tutorial number can be found on
Blackboard under Class Timetable & links)
Please complete the questions as outlined below.
You must use functions/features we cover in our Excel tutorials, unless otherwise indicated (see
column 5). The questions have been designed to test your knowledge of the functions we cover in
tutorials.
Task 1 (Worksheet 1). 3 questions (6 marks)
Sales data in columns A:B has been imported from your company's sales database.
The Source column (A) contains labels indicating which employee made the sale (Employee_ID), then
a hyphen, then the region in which the sale was made (i.e. N,E,S,W - North, East, South, West
regions). For example, 103-N means the employee with ID 103 made the sale in the North region.
The Sales Analysis table in columns D:J requires a number of formulas to be written as per the task
list below, some of which depend upon the data table in columns L:M.
Cells Task/problem Example Functions/features you
should
Q1
2
marks
E12:H21 Write a formula which will
produce the sales amount for
each employee per region. i.e. for
101 in North, East, South and
West sales regions.
In some cases, an employee has
not made any sales for a given
region, such as employee 104 in
the South sales region. This may
cause an error (e.g., “#N/A”),
therefore in your formula use
IFERROR to produce $0 instead.
Do this with one formula that is
created for E12 and copied to
E12:H21.
Employee 102-N made
$7,815 sales in the North
region, $1,712 in the East,
$9,363 in the South, and
$5,827 in the West.
Define A:B (i.e. the entire
A and B columns) as a
Named Range called
“Sales” and use this in
your VLOOKUP.
Ensure you use the correct
reference types so you can
copy the formula to the
entire range (E12:H21), i.e.
for all employees and all
sales regions.
Q2
2
marks
I12:I21 Write a formula to evaluate
employee performance for the
month, according to the
following rules.
If there are any sales regions for
any individual employee which
have no sales ($0), the evaluation
is “Interview”, as the employee is
required to explain reasons for
this to management.
Otherwise, if the North region’s
sales are greater than or equal to
$4,000 and the East region’s sales
is greater than or equal to $5,000
and South’s sales is greater than
or equal to $3,000 and West’s
sales are greater than or equal to
$2,000, then the evaluation is
“Good”, otherwise, the
evaluation is to be left blank (do
not use a space for this).
Enter the formula into I12 and
copy the formula down for all
employees.
Employee 101 sold more
than: $4,000 in the North
region, $5,000 in the East
region, $3,000 in the
South region and $2,000 in
the West region.
Therefore, I12 should
display Good.
Employee 104 had no
sales in the South region
and therefore I15 should
be “Interview”.
Use COUNTIF to
determine if an employee
has made no sales ($0) for
a region.
Q3
2
marks
J12:J21 Employees are entitled to a
bonus payment, which is the
percentage given in M11,
multiplied by the total of all sales
for that employee in all four
regions minus the minimum total
sales in M10 [bonus = bonus rate
x (total sales for the four regions
– minimum sales)].
If the employee does not meet
the minimum total sales, the
bonus is $0.
Enter the formula into J12 and
copy the formula down for all
employees.
Employee 103 made
$23,387 in sales minus the
minimum total sales
($20,000) equals $3,387.
30% of $3,387 is $1,016
Therefore, J14 should
display $1,016.
Use arithmetic operations,
appropriate reference
types, brackets and pay
attention to the order of
operations.
Task 2 (Worksheet 2). 1 question (4 marks)
Your employees are assigned different amounts of money to cover their work-related expenses,
based on their job roles. Jobs in city locations have a larger expense budget than those in regional
locations (since regional locations are generally cheaper).
Cells Task/problem Example Functions/features
you should use
Q1
4
marks
E4:G11 Write a formula to extract the expense
amount for 2022 to 2024 for the Job Role
in column C (e.g., Naomi is an Executive
Officer) from the Expense Budget table in
columns I:L.
Note that each job role has two rows in
the Expense Budget table, depending
upon whether it is in a city or regional
location. For example, as Naomi is in
Milton, which is listed as a city location in
the locations table (columns N:O) her
2022 budget is $25,000.
If there is an error, e.g. #N/A, the result
of the formula should be a question mark
(“?”), which will indicate to staff that
something went wrong and should be
checked.
Enter the formula into E4 and copy the
formula across and down for all three
years for all employees.
As Naomi’s job role in C3 is
“Executive officer (ExOf)”,
we need to match this with
the “EO” in the Expense
Budget table and use the
Locations table to find the
right expense budget “City”.
For Naomi the Budget role is
EO (City) and using the
Expense Budget Table we
can find that the amount for
2022 is $25,000, for 2023 it
is $26,250 and for 2024 it is
$27,562.
Therefore, these values
should be displayed in E4,
F4, G4.
Define I2:L18 as a
Named Range called
“Budget” and use
this in your
VLOOKUP.
Define N2:O6 as a
Named Range called
“Locations” and use
this in your
VLOOKUP.
You must include
functionality in your
formula to
automatically select
the appropriate
year column from
the table. Do not
manually edit these
for each column.
Task 3 (Worksheet 3 and Worksheet 4). 2 questions (5 marks)
Important: This second part of this question pertains to TWO worksheets named Task 3 and Report.
Your staff members are evaluated yearly on six dimensions of their performance: quality, quantity,
knowledge, initiative, reliability, and adaptability. Each of these dimensions is rated by the
employee’s supervisor and recorded in the table on the Task 3 worksheet.
The weighted score column (K) will contain your formula to calculate an overall score for each
employee based on their performance and appropriate weightings given in the weighting table, as
per instructions below.
Cells Task/problem Example Functions/features
you should
Q1
3
marks
K4:K23 in
the Task 3
worksheet
Write a formula to calculate a
weighted score for employee 1097,
Dorian.
The score is the sum of the six
dimensions (columns E:J) which are
each multiplied by the corresponding
weighting in the dimension weightings
table (columns M:O) e.g. quality score
Dorian joined in 2017,
therefore we take his
weightings from the Column
O in the Dimension
Weightings table. After using
the correct weightings in the
weightings formula, Dorian’s
weighted score of 70.5%
should be displayed in K4.
You should use
YEARFRAC and
TODAY. We have
not used these in
our tutorials, but
you should be able
to learn new
functions. You
should also use
(E4) x quality weighting + quantity
score (F4) x quantity weighting, etc.
Convert the number to a percentage
by dividing the entire result by 100,
then use the percentage format from
the ribbon. Format it to one decimal
place.
Be aware that the weighting for new
staff (hired less than one year ago) is
different to all other staff, so your
formula must choose the correct
weighting column from the weightings
table based on the length of the staff
member’s employment.
Copy the formula down the K column
for all employees.
Harold joined the firm in
2022, and therefore we take
his weightings from the
Column N in the Dimension
Weightings table. Therefore,
Harold’s weighted score of
51.0% should be displayed in
K15.
functions covered
in tutorials.
This is a long,
repetitive formula!
Q2
2
marks
F13:I13 in
the
Report
worksheet
F13 in the Report worksheet: Write the
appropriate formula to determine the
lowest score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
G13 in the Report worksheet: Write
the appropriate formula to determine
the average score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
H13 in the Report worksheet: Write
the appropriate formula to determine
the median score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
I13 in the Report worksheet: Write the
appropriate formula to determine the
highest score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
The lowest quality score in
the Task 3 worksheet
(E4:E23) is 14.
The average quality score in
Task 3 worksheet E4:E23 is
61.05.
Use the relevant
statistical
functions.
Ensure that you
appropriately link
to the Task 3
worksheet.
END OF ASSIGNMENT SPECIFICATION
Transforming Business with Information Systems
Assignment
Semester 1, 2022
Course Coordinator: Associate Professor Stan Karanasios
bism1201@business.uq.edu.au
Assignment Specification Semester 1, 2022
There are two parts to this assessment.
Part A consists of three information systems questions (35 marks)
Part B consists of Excel questions to be answered in an Excel worksheet “BISM1201 Assignment Part
B Excel S1 2022.xlsx” (this file is available on course Blackboard site) (15 marks)
Worth: 50% of total assessment
Individual Completion: This assignment is to be completed individually by each student. Students
are encouraged to discuss issues that arise in this course together, however, the written work you
submit must be entirely your own. Similarly, you must not help another student to cheat by sharing
assignments (present or past).
Due Dates: The assignment is due by Tuesday 17/05/2022 at 3PM (Brisbane time). Submissions after
this date and time will be considered "Late" and relevant penalties will apply.
Submission
Part A and Part B must be submitted directly to the assessment section on Blackboard using the link
Assignment submission link (PART A and PART B) (see the assessment folder).
• Submit Part A as a PDF file via the submission link.
• Submit a copy of Part A on Turnitin – use the link Turnitin submission for Assignment PART
A. You must ensure that the Part A that you submit to Turnitin matches Part A submitted via
the submission link. We will not mark Part A submissions if there is no matching file on
Turnitin.
• Submit Part B as an Excel workbook file “BISM1201 Assignment Part B Excel S1 2022.xlsx”
(this file is available on course Blackboard site). Your answers should be entered into the
relevant cells of the Excel file, as per the assignment specification.
Your two submission files MUST be named (ALL CHARACTERS IN UPPERCASE) in the following
format: Tutorial number (comprising T followed by the full digit tutorial number i.e., T10, T02). This
tutorial number must then be followed by an underscore character, your full name followed by an
underscore character and Student_ID e.g., "T10_TIMSMITH_45894125”. (Student tutorial number
can be found on Blackboard under Class Timetable & links)
NOTE: You are allowed multiple attempts to submit (we only mark the last submission). However,
each time you submit using the submission link you should submit both files (PDF and Excel file),
because Blackboard treats each submission as new.
No hardcopy or email submission is required or accepted.
For information on how to submit Part A and Part B on Blackboard via the assignment submission
link, please visit:
https://web.library.uq.edu.au/library-services/it/learnuq-blackboard-help/learnuq-
assessment/blackboard-assignments/assignment-submission-instructions
For information on how to submit Part A to Turnitin, please visit:
https://web.library.uq.edu.au/library-services/it/learnuq-blackboard-help/learnuq-
assessment/turnitin-assignments
Presentation
• For Part A, you must follow the word length requirements (+/-10%).
• Your assignment must be presented using the sections as sub-headings (note: each section does
not need to start on a new page).
• Be sure to use double-spacing, 12-point font, page numbering and 2.5cm margins on all sides of
the page.
• Please proofread your assignment for spelling and grammatical errors.
Referencing and Citing
Part A questions require you to do basic analysis of the cases. You must base your answers and
terminology on the material covered in our weeks of information systems theory.
If you wish to report another author's point of view you should do so in your own words, and
properly reference in accordance with an approved referencing style. Direct quotations should be
used sparingly, form a small part of your work, and must be placed in quotation marks and
referenced.
Part A Question 1 and Question 2 require you to give a real-world example. Examples can come from
journals, newspapers, organisation websites, magazines etc. The real-world examples must be
referenced using in-text refencing and included in the reference list at the end of the assignment.
For example, “Nike uses a product differentiation information system strategy because it allows
customers to use its online platform to customise its sneakers. This means that Nike can use
information systems to offer a different value proposition than its competitors (Smith & Chang,
2010)”.
For more information on referencing styles, visit the library or see:
https://web.library.uq.edu.au/research-tools-techniques/referencing/referencing-style-guides. Use
the UQ Harvard style.
UQ value academic integrity, referencing others work is a key part of this. Please see this link for
more information on academic integrity: https://my.uq.edu.au/information-and-services/manage-
my-program/student-integrity-and-conduct/academic-integrity-and-student-conduct.
PART A: Information
Systems Theory
35 marks
Question 1. IS strategy (10 marks)
BISM-Desks is a family-owned firm that creates bespoke desks for homes and offices. Unlike some of
the major retailers, the only way for customers to purchase it’s desks is by visiting a show room in
Brisbane and buying floor stock or by designing a desk by choosing intricate details like material,
color, patterns, size, shelving, layout etc.
Because BISM-Desks relies on face-to-face sales the firm struggled throughout COVID-19 (2020-
2021). This led to a lost opportunity as desks, especially customisable desks for different spaces,
were in high demand.
BISM-Desks has used Porter’s Five Forces model to realise that it faces pressure from competitors
and new entrants and is concerned that their customers might move to online alternatives. In
response, the firm is considering ways to modernise and expand. While in the past it focused on
‘high-end’ customers, it believes that the normalisation of working from home means that there will
be even greater demand for customisable desks and that customers may be willing to pay a little
more for a desk. This is not so dissimilar to retailers like IKEA, however BISM-Desks’ products are
based on the highest quality materials, are Australian made and are not flat-packed/self-assembled.
While this means the cost of its products are higher, it allows BISM-Desks to differentiate itself from
large retailers.
The firm also recognises that it is not ‘digitally ready’. It has not relied on information systems to
help operate its business beyond basic word processing, email and keeping track of finances. The
owners are more interested with ‘building beautiful desks’ than in IT. The firm has no centralised
database nor is data captured to learn about customers or to support decision-making. There is no
way to track customer demographics or behaviour beyond what is learned about customers when
they visit the showroom. Likewise, inventory ordering is undertaken by email and phone which leads
to very slow links with suppliers. These issues are all coming to the forefront as the owners want to
expand the business whilst also getting ready for stepping back from the day to day running of it and
instead relying more on professional staff to run the business. However, they cannot do so without
more efficient and standardised processes.
BISM-Desks has recruited you as a business analyst. The owners would like advice on different
information systems strategies that can help the firm modernise itself and make the most of the
demand for its products.
Q1: Select two of the below information systems strategies and explain how each can help BISM-
Desks grow and be more competitive.
(i) Basic strategy/align IT with business objectives;
(ii) Low-cost leadership;
(iii) Product differentiation;
(iv) Focus on market niche;
(v) Strengthen customer or supplier intimacy.
Provide a real-world example1 for each information systems strategy you identified to show how it is
applicable to the case of BISM-Desks (i.e., one real-world example/reference per strategy)
(600 words total) (10 marks).
Question 2. Enterprise systems (5 marks)
BISM-Hotel, a medium sized luxury hotel operator in Brisbane, recently bought four other hotels
across Queensland as part of its plan to become the largest luxury hotel operator in Queensland.
BISM-Hotel has managed its customer details through a very basic Microsoft Access database and
used a booking application to manage transactions and bookings. This has worked relatively well in
the past but was limited in terms of understanding customer behaviour, trends and offering loyalty
programs and so on.
The hotel marketing manager who has worked with BISM-Hotel for ten years has traditionally used
her experience to decide customer-related decisions based on her own interactions with customers
rather than on data. For example, the marketing manager knows the most loyal customers, what
their preferences are, and their purchase patterns from years of interaction. She captures this data
in a spreadsheet, emails and notes. All in all, BISM-Hotels’ customer insights worked well enough for
a single city centre hotel, however with its expansion plans, BISM-Hotel wants to be able to build
more customer insights and encourage customers to stay at its other hotels.
BISM-Hotel would like to keep track of high value customers to ensure that they don’t leave for
other competitors. At the same time, the BISM-Hotel wants to keep track of the issues faced by
customers and resolve them. Recently BISM-Hotel noticed that its ratings and reviews on social
media have been dropping. This is a concern for a luxury hotel and it would like to improve
customers satisfaction. In particular, it has noticed that customer complaints and issues are received
via email, phone and face-to-face but there is no way to systematically record and monitor issues
and as consequence many do not get resolved.
A business analysis of the four other hotels BISM-Hotel acquired shows they all have their own way
of working, processes and systems. BISM-Hotel will therefore need to align customer relationship
management processes across all of its hotels.
You have started working at BISM-Hotel as a business analyst and your manager has asked you to
explain how a Customer Relationship Management (CRM) system could help BISM-Hotel with its
expansion plans and address the limitations of its current approaches to customer relationship
management.
Q2. Explain to your manager two reasons why BISM-Hotel would want to implement a Customer
Relationship Management (CRM) system. For each of your reasons give a real-world example2 and
apply it to the case (400 words total) (5 marks).
1 You should reference your examples. Examples should not be from the course textbook (or seminar/tutorial
material). The examples can come from any context, that is they do not have to be about furniture or small
firms.
2 You should reference your examples. Examples should not be from the course textbook (or seminar/tutorial
material). The examples can come from any context, that is they do not have to be about hotels.
Question 3. Information systems development (20 marks)
You have recently joined a new IT consulting firm called BISM-Digital which will specialise in
providing tailored and cutting-edge information systems to governments, banks, manufacturers, and
other large organisations.
BISM-Digital has been approached by several firms that have indicated they need: (i) new cutting-
edge information systems tailored to their organisation’s needs; (ii) information systems delivered as
quick as possible so as to better compete in the market; (iii) want to actively be involved and engage
with BISM-Digital throughout the information systems development process; and, (iv) that because
of the novelty of their information systems projects the development process must be able to
accommodate change. These firms have also expressed concerns that information systems
development projects are frequently late, have high failure rates and the end product rarely meets
their requirements.
Based on the needs of customers, your manager has tasked you with making a recommendation on
whether BISM-Digital should follow either a Scrum or Waterfall approach for information systems
development.
Q3. Make a recommendation to your manager that BISM-Digital follows either Scrum or Waterfall in
its information systems development projects. Using the Scrum/Waterfall process and concepts give
four reasons why Waterfall/Scrum could lead to more successful outcomes. In doing so, contrast
your reasons with the alternate approach (i.e., if you suggest Scrum then contrast your answer with
Waterfall). (800 words) (20 marks)
PART B: Excel
15 marks
The Excel workbook file for the tasks described below (BISM1201 Assignment Part B Excel S1
2022.xlsx) is available on the assessment tab on the course Blackboard site.
There are four worksheets in the workbook, labelled Task 1, Task 2, Task 3 and Report. Only edit the
indicated cells (the blue or green shaded cells). Do not alter/change/move any other cells, nor make
any new columns or rows.
Please add your (i) student name, (ii) student ID, (iii) tutorial number, and (iv) tutor name on the first
worksheet (in the blue box in the upper left corner). (Student tutorial number can be found on
Blackboard under Class Timetable & links)
Please complete the questions as outlined below.
You must use functions/features we cover in our Excel tutorials, unless otherwise indicated (see
column 5). The questions have been designed to test your knowledge of the functions we cover in
tutorials.
Task 1 (Worksheet 1). 3 questions (6 marks)
Sales data in columns A:B has been imported from your company's sales database.
The Source column (A) contains labels indicating which employee made the sale (Employee_ID), then
a hyphen, then the region in which the sale was made (i.e. N,E,S,W - North, East, South, West
regions). For example, 103-N means the employee with ID 103 made the sale in the North region.
The Sales Analysis table in columns D:J requires a number of formulas to be written as per the task
list below, some of which depend upon the data table in columns L:M.
Cells Task/problem Example Functions/features you
should
Q1
2
marks
E12:H21 Write a formula which will
produce the sales amount for
each employee per region. i.e. for
101 in North, East, South and
West sales regions.
In some cases, an employee has
not made any sales for a given
region, such as employee 104 in
the South sales region. This may
cause an error (e.g., “#N/A”),
therefore in your formula use
IFERROR to produce $0 instead.
Do this with one formula that is
created for E12 and copied to
E12:H21.
Employee 102-N made
$7,815 sales in the North
region, $1,712 in the East,
$9,363 in the South, and
$5,827 in the West.
Define A:B (i.e. the entire
A and B columns) as a
Named Range called
“Sales” and use this in
your VLOOKUP.
Ensure you use the correct
reference types so you can
copy the formula to the
entire range (E12:H21), i.e.
for all employees and all
sales regions.
Q2
2
marks
I12:I21 Write a formula to evaluate
employee performance for the
month, according to the
following rules.
If there are any sales regions for
any individual employee which
have no sales ($0), the evaluation
is “Interview”, as the employee is
required to explain reasons for
this to management.
Otherwise, if the North region’s
sales are greater than or equal to
$4,000 and the East region’s sales
is greater than or equal to $5,000
and South’s sales is greater than
or equal to $3,000 and West’s
sales are greater than or equal to
$2,000, then the evaluation is
“Good”, otherwise, the
evaluation is to be left blank (do
not use a space for this).
Enter the formula into I12 and
copy the formula down for all
employees.
Employee 101 sold more
than: $4,000 in the North
region, $5,000 in the East
region, $3,000 in the
South region and $2,000 in
the West region.
Therefore, I12 should
display Good.
Employee 104 had no
sales in the South region
and therefore I15 should
be “Interview”.
Use COUNTIF to
determine if an employee
has made no sales ($0) for
a region.
Q3
2
marks
J12:J21 Employees are entitled to a
bonus payment, which is the
percentage given in M11,
multiplied by the total of all sales
for that employee in all four
regions minus the minimum total
sales in M10 [bonus = bonus rate
x (total sales for the four regions
– minimum sales)].
If the employee does not meet
the minimum total sales, the
bonus is $0.
Enter the formula into J12 and
copy the formula down for all
employees.
Employee 103 made
$23,387 in sales minus the
minimum total sales
($20,000) equals $3,387.
30% of $3,387 is $1,016
Therefore, J14 should
display $1,016.
Use arithmetic operations,
appropriate reference
types, brackets and pay
attention to the order of
operations.
Task 2 (Worksheet 2). 1 question (4 marks)
Your employees are assigned different amounts of money to cover their work-related expenses,
based on their job roles. Jobs in city locations have a larger expense budget than those in regional
locations (since regional locations are generally cheaper).
Cells Task/problem Example Functions/features
you should use
Q1
4
marks
E4:G11 Write a formula to extract the expense
amount for 2022 to 2024 for the Job Role
in column C (e.g., Naomi is an Executive
Officer) from the Expense Budget table in
columns I:L.
Note that each job role has two rows in
the Expense Budget table, depending
upon whether it is in a city or regional
location. For example, as Naomi is in
Milton, which is listed as a city location in
the locations table (columns N:O) her
2022 budget is $25,000.
If there is an error, e.g. #N/A, the result
of the formula should be a question mark
(“?”), which will indicate to staff that
something went wrong and should be
checked.
Enter the formula into E4 and copy the
formula across and down for all three
years for all employees.
As Naomi’s job role in C3 is
“Executive officer (ExOf)”,
we need to match this with
the “EO” in the Expense
Budget table and use the
Locations table to find the
right expense budget “City”.
For Naomi the Budget role is
EO (City) and using the
Expense Budget Table we
can find that the amount for
2022 is $25,000, for 2023 it
is $26,250 and for 2024 it is
$27,562.
Therefore, these values
should be displayed in E4,
F4, G4.
Define I2:L18 as a
Named Range called
“Budget” and use
this in your
VLOOKUP.
Define N2:O6 as a
Named Range called
“Locations” and use
this in your
VLOOKUP.
You must include
functionality in your
formula to
automatically select
the appropriate
year column from
the table. Do not
manually edit these
for each column.
Task 3 (Worksheet 3 and Worksheet 4). 2 questions (5 marks)
Important: This second part of this question pertains to TWO worksheets named Task 3 and Report.
Your staff members are evaluated yearly on six dimensions of their performance: quality, quantity,
knowledge, initiative, reliability, and adaptability. Each of these dimensions is rated by the
employee’s supervisor and recorded in the table on the Task 3 worksheet.
The weighted score column (K) will contain your formula to calculate an overall score for each
employee based on their performance and appropriate weightings given in the weighting table, as
per instructions below.
Cells Task/problem Example Functions/features
you should
Q1
3
marks
K4:K23 in
the Task 3
worksheet
Write a formula to calculate a
weighted score for employee 1097,
Dorian.
The score is the sum of the six
dimensions (columns E:J) which are
each multiplied by the corresponding
weighting in the dimension weightings
table (columns M:O) e.g. quality score
Dorian joined in 2017,
therefore we take his
weightings from the Column
O in the Dimension
Weightings table. After using
the correct weightings in the
weightings formula, Dorian’s
weighted score of 70.5%
should be displayed in K4.
You should use
YEARFRAC and
TODAY. We have
not used these in
our tutorials, but
you should be able
to learn new
functions. You
should also use
(E4) x quality weighting + quantity
score (F4) x quantity weighting, etc.
Convert the number to a percentage
by dividing the entire result by 100,
then use the percentage format from
the ribbon. Format it to one decimal
place.
Be aware that the weighting for new
staff (hired less than one year ago) is
different to all other staff, so your
formula must choose the correct
weighting column from the weightings
table based on the length of the staff
member’s employment.
Copy the formula down the K column
for all employees.
Harold joined the firm in
2022, and therefore we take
his weightings from the
Column N in the Dimension
Weightings table. Therefore,
Harold’s weighted score of
51.0% should be displayed in
K15.
functions covered
in tutorials.
This is a long,
repetitive formula!
Q2
2
marks
F13:I13 in
the
Report
worksheet
F13 in the Report worksheet: Write the
appropriate formula to determine the
lowest score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
G13 in the Report worksheet: Write
the appropriate formula to determine
the average score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
H13 in the Report worksheet: Write
the appropriate formula to determine
the median score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
I13 in the Report worksheet: Write the
appropriate formula to determine the
highest score for the quality
performance dimension amongst all
employee records on the Task 3
worksheet.
The lowest quality score in
the Task 3 worksheet
(E4:E23) is 14.
The average quality score in
Task 3 worksheet E4:E23 is
61.05.
Use the relevant
statistical
functions.
Ensure that you
appropriately link
to the Task 3
worksheet.
END OF ASSIGNMENT SPECIFICATION
