CS计算机代考程序代写 database Excel BISM7202 Information Systems

BISM7202 Information Systems

for Management

Case Specification: MS Office Assignment

– Office 365 Excel 2019
SEMESTER 2, 2021

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 2

Specification

Template

This specification document is a companion document to the Assessment Guideline.

The Excel template of the expected worksheets are available on the BISM7202 Blackboard

site. The Excel template provided must be used as the basis for the assignment.

You may change the visual formatting (correct typographical errors, change colour, fonts,

data format presentation, column widths, etc) of the worksheets to provide a professional

finished product, and will need to define named ranges, but nothing else unless asked to

do so. For example, do not change the workbook’s structure or cell locations (except

when you are asked to do so).

You would be well advised to make your work of the highest presentation quality (e.g. apply

screen freezing to long pages, use name references where appropriate, use lookup

functions instead of nested ifs where a data table exists, do not hardcode changeable

data, use appropriate fonts and colours, graph axes and titles, etc).

Note: Entering your student name and student number in the Document Control Sheet

should be the first step that you do in undertaking this assignment.

When you develop your solution, you should use (but are not limited to) the functions and

features you were taught in the tutorials. If you need functions or techniques that are not

addressed explicitly in tutorial exercises, you should explore your pre-tutorial reading

materials and preparation exercises or refer to the help component of Excel (or conduct a web

search).

Aspects of the assignment have purposefully been designed to train and test a student’s self-

learning ability with a software application, and thus it is possible that not all the functions

you need to use have been directly addressed in a tutorial exercise.

This assignment requires you to complete an Excel workbook file using Microsoft 365 Excel

/ Excel 2019 based on the specification in this document. The Excel workbook contains

several sheets you should develop. These sheets are described as follows.

It is highly recommended that, prior to assignment submission, you check that your

solution works on the university machines if you have developed it on your own

machine.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 3

Background and Scenario

An innovative, fairly new, company called Henry’s Farm Market (HFM) has been set up

that provides a door-to-door grocery delivery service to the inner-city suburbs of Brisbane.

HFM is set up as a franchise, which means that there is a ‘head’ franchisor with several

franchisees who operate ‘under license’ from the franchisor. HFM has four franchisees in

Brisbane. Each franchisee is a local grocery shop.

As franchisees of HFM, they are licenced to deliver daily groceries by the box to homes and

businesses in nearby suburbs (their ‘franchise area’). Over its two years of operation, HFM

has built up a trustworthy reputation among its customer base. HFM customers receive a ‘set

grocery’ box of groceries each week via their membership subscription program. The length

of membership varies in terms of 13, 26, or 52 weeks, and subscription fees vary by

membership terms.

Henry Thomas, the owner of HFM, has asked you to develop a spreadsheet that will help

refine the franchise area and lower the distance travelled. Henry is very environmentally

conscious and does not want to damage the planet to deliver groceries. He wants you to:

(1) Develop a schedule of employee budgeted salary costs according to his specifications
and build a summary table using database functions;

(2) Undertake a Solver analysis on the business franchise areas to determine a
reallocation of franchise areas by distance from the store;

(3) Undertake a scenario analysis on saving monthly/fortnightly for building a new store
that will cost a ‘What If’ certain amount of money in a few years;

(4) Provide some business-focussed comments to Henry relating to this MS Excel
solution.

List of Sheets in Excel Workbook

When submitted, your final solution will have the following sheets:

• Document Control

• Constant

• Employees

• Payroll Summary

• Pivot Table

• Pivot Chart

• Current Franchise Distribution

• Franchise Redistribution

• Solver Analysis Answer Report

• Pivot Table

• Pivot Chart

• New Store Investment

• Scenario Summary

• Comments to Henry

Sheets in italics need to be created by you as they are not in the template file.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 4

Document Control Sheet

Hint: Throughout the spreadsheet, cells with a light shaded blue background require

you to enter a value or a formula in them or take some actions with them.

Cells with a light-orange background are to be populated by either the Solver or

Scenario Manager tools, not you.

Cells with no colour background should not be edited or changed by you unless

explicitly directed to do so in this specification document.

First enter your details: Student name and student number.

In addition, you should list any assumptions that you have made when you developed your

assignment on this sheet. The assumptions allow examiners to understand your work in

context. You should use these assumptions to resolve any ambiguities you might identify in

this Case Specification.

The assumptions you make must be logical and consistent with the scenario provided in this

Case Specification.

If you do not make any assumptions, please leave this section empty.

Constant Sheet

This sheet contains all the lookup tables that you will need to use in the assignment. When

using lookup tables in your formulas from the Constant sheet, make sure they are

accessed using appropriate Named Ranges.

You should also format (but not edit) this sheet professionally.

Note: Throughout this assignment, you must use a Named Range whenever referring to

a range or a cell in writing formulas/functions to ensure that whoever reviews your

spreadsheet can understand it, especially when the range or cell is from another

worksheet. You can also use Named Ranges to refer to a range or a cell in the same

worksheet, but this is optional.

MARKS ARE ALLOCATED IN THE MARKING RUBRIC FOR USING NAMED

RANGES TO REFER TO RANGES OR CELLS IN A DIFFERENT WORKSHEET

There are 8 lookup tables or values contained in this Constant Sheet. You are to complete

these as directed below.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 5

Employee Salary Table

Employees are paid at different rates based on their job title. Each job comes with a different

employer superannuation percentage rate. The details of the different job descriptions are

presented below.

You are required to complete the data entry of the table in the workbook.

Table 1: Employee Salary Table for 2021-22

Job Title Annual Salary Employer Super

Accountant $65,948 10.5%

Operation Manager $67,723 11%

Owner $126,745 18%

Delivery Service Manager $68,720 10%

IT Manager $79,468 12.5%

Franchisee Manager $99,765 16%

Senior Delivery Service Manager $73,569 12%

Annual Tax Table

Tax is withheld using the following tax rates for 2021-22. This information has been entered

for you in the Constants Sheet.

Table 2: Australian Taxable Income Table for 2021-22

Taxable Income Tax on this Income

$0 – $18,200 Nil

$18,201 – $45,000 19c for each $1 over $18,200

$45,001 – $120,000 $5,092 plus 32.5c for each $1 over $45,000

$120,001 – $180,000 $29,467 plus 37c for each $1 over $120,000

$180,001 and over $51,667 plus 45c for each $1 over $180,000

Note: The above rates do not include the Medicare levy of 2%.

Employee Superannuation Contribution Table

Employees at HFM have collectively agreed to contribute a percentage of their annual salary

to their superannuation fund based on their age at the beginning of the financial year as a

post-tax contribution (‘non-concessional contributions’). This is not extra money received by

the employee.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 6

You are required to complete the data entry of the table in the workbook.

o Employees aged 30 and over have elected to contribute 4%.
o Employees aged 40 and over have elected to contribute 4.5%.
o Employees aged 50 and over have elected to contribute 5%.
o Employees aged 60 and over have elected to contribute 5.5%.

Note: In Australia, the financial year is for the period 1 July to 30 June, which is different to

the calendar year, which is for the period 1 January to 31 December.

Christmas Bonus Rates Table

Employees at HFM who have had extended service with the company are paid an annual

Christmas bonus at the end of each calendar year. You are required to complete the data

entry of the table in the workbook.

o Employees who have been employed for at least 2 years at the beginning of the
calendar year receive a 2% bonus on their annual salary.

o Employees who have been employed for at least 4 years at the beginning of the
calendar year receive a 3% bonus on their annual salary.

o Employees who have been employed for at least 6 years at the beginning of the
calendar year receive a 4% bonus on their annual salary.

o Employees who have been employed for at least 8 years at the beginning of the
calendar year receive a 5.5% bonus on their annual salary.

o Employees who have been employed for at least 10 years at the beginning of the
calendar year receive a 7.5% bonus on their annual salary.

Beginning of Calendar Year

Enter the first day of the 2021 calendar year (i.e., 01/01/2021).

You are required to complete the data entry of the table in the workbook.

Beginning of Financial Year

Enter the first day of the 2021/2022 financial year (i.e., 01/07/2021).

You are required to complete the data entry of the table in the workbook.

HFM Subscriptions Table

The subscription fee paid by customers varies according to the number of weeks they

subscribe. Customers pay $70 per week for a 13-week subscription, $65 per week for a 26-

week subscription, and $55 per week for a 52-week subscription. This information has been

entered for you in the Constants Sheet.

Distance Survey and Suburb Profile Table

Previously, franchise areas were allocated according to a rule of thumb (‘whatever worked’)

at the time the franchise was allocated. As HFM matures, Henry now wants to consider

allocating franchise areas based on the average actual travel distance from the shop to the

suburbs that they service.

This table is central to those calculations.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 7

Each row in this table is an inner-city suburb in Brisbane that is within 5kms or so of the

Brisbane CBD. The latitude and longitude of an ‘average’ (centroid) point for each suburb is

provided. You are to use this information to determine distance for franchise areas.

Each row also indicates the prospective subscribers to the HFM service in these Brisbane

suburbs to each subscription type (13, 26, or 52 weeks). This information is derived from

extensive and, according to Henry, infallible, market research1. A prospective subscriber is

the likely the maximum number of HFM subscribers in the suburb indicated by market

research. The role of prospective subscribers versus actual subscribers is discussed below in

the Current Franchise Distribution section.

Each column in this table represents the four (4) current franchisee stores in Brisbane

(Brisbane City, South Brisbane, Milton, and Fortitude Valley).

In this table, you are to calculate the distance from each franchisee store to each suburb using

the latitude and longitude. To do this, use the latitude and longitude of each location

according to the following formula:

Distance =ACOS(COS(RADIANS(90-Lat1))*COS(RADIANS(90-Lat2)) +

SIN(RADIANS(90-Lat1))*SIN(RADIANS(90-Lat2))*COS(RADIANS(Long1-Long2)))

*6371

In this formula, Lat1 and Long1 should be replaced with the cell reference to the latitude and

longitude of each suburb respectively, and Lat2 and Long2 should be replaced with the cell

reference to the latitude and longitude of each franchisee store respectively.

Note: The Shop Code at the top of this table relates each Shop Code to the suburb in

which it is located.

Hint: You may wish to check your calculations of distance between suburbs using

Google Maps to measure the distance between two points, or a site such as the one

provided by NASA: https://nssdc.gsfc.nasa.gov/special/.

Note: the distance between a franchisee store in a suburb and the suburb in which it is

located will be 0. For example, the distance between the suburb of Milton in the row of

this matrix and the suburb of Milton in the column of this matrix should be 0. If it is not

zero – then your formula is definitely wrong!

Note: Format this table appropriately.

1 You protest to Henry that no market research is ever infallible. Henry tells you ‘Don’t you worry about that!’

https://nssdc.gsfc.nasa.gov/special/

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 8

Employees Sheet

Note: Please note that your formulas should be efficient (not hardcoded). You can use

a Lookup and Reference function to achieve this.

When using lookup tables in your formulas for values in the Employees Sheet, make

sure they are accessed using appropriate named ranges.

You should also format this sheet professionally and meaningfully.

The employee sheet keeps track of HFM’s employees. Your tasks are to:

Suggested order to complete tasks: Employee Budgeted Salary Costs -> Employee

Residential Details -> Database Functions

Employee Budgeted Salary Costs

(1) Column N: Insert a formula to retrieve the annual salary of the employee from the
Constant Sheet based on employee’s Job Title.

(2) Column O & P: Following this, insert formulas to calculate the employer and
employee superannuation contributions.

Note: Please note that employer superannuation is calculated from, but is not included

in, the employee’s annual salary i.e. superannuation is in addition to salary.

Hint: You will need to use the employee’s birthday in relation to the first day of the

financial year to calculate their employee superannuation contributions.

Take a look at the date and time functions to best understand how to determine the

fraction of a year – for example, someone born on 5th March 1991 would not be 30 on 1st

January 2021 and so would not pay any extra contribution.

(3) Column Q: Insert a formula to determine the Christmas bonus employees receive in
addition to their annual salary.

Hint: You will need to use the employee’s first working day in this formula. Take a

look at the date and time functions.

(4) Column R: Using a formula calculate the annual income tax & Medicare levy
withheld from employees based on their salary.

Note: In your solution, assume that all employees pay the Medicare levy of 2% (that is,

assume all employees earn more than the threshold for low-income earners, and no

Medicare Levy Surcharge applies).

Hint: Use the Annual Tax Table to calculate Income Tax from all income figures. For

example, an employee whose salary is $68,720 who has been at the store for two years

would receive ($68,720+ (0.02 x $68,720)) = $70,094.4 in taxable income.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 9

On this taxable income, the accountant would pay income tax of ($5,092 + ($70,094.4 –

45,000) x 0.325) = $13,247.68. The Medicare Levy of 2% also applies and so Income

Tax & Medicare Levy would be $13,247.68 + ($70,094.4 x 0.02) = $14,649.57.

(5) Column S: Finally, insert a formula to determine the annual take home balance for
each employee – this is each employee’s total income less tax paid less any employee

contributions to superannuation.

Employee Residential Details

(1) Residential Suburb (Column L): Insert a formula to determine the employee’s
residential suburb (i.e. Milton).

Hint: You will need to search for the suburb name in the employee’s address to

determine the suburb of their home address. Take a look at the text functions.

(2) Same Suburb as Shop (Y/N) (Column M): Insert a formula to determine whether the
employee lives in the same suburb as their work location.

Hint: Derive this column from the ‘Residential Suburb’ (Column L), ‘Location’

(Column J), and ‘Distance Survey and Suburb Profile’ table on the Constant sheet. If

the suburb where the employee lives is their work location, then show this value as ‘Y’.

If not, then show this value as ‘N’.

Database Functions

You then need to complete the HFM Summary Table at the top of the sheet using Database

Functions. Please use the Criteria for Summary Table Database Functions in row 2/3. Using

two named ranges (Database and Criteria), apply database functions to calculate the

minimum, maximum, average, and total values for the listed headings (N-S), so that changing

the criteria in row 3 results in changes to the summary table values in rows 9-12. You should

also use a database function to count the number of records in the schedule that meet the

criteria for cell N7.

Hint: Database functions begin with a “D” and rely on criteria set out in a range that

you identify and populate with your criteria.

The formulas should be robust and not display errors.

When submitting the spreadsheet, set the criteria for summary table database functions so

that the Summary Table displays data relating to only the Job Title of “Delivery Service

Manager”.

Payroll Summary Sheet

Payroll Summary sheet records the actual payment across 12 months for each employee in

2021 (Please note that the calculations in Employees Sheet are budgeted take-home payment

not the actual payment employees received).

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 10

Using the information from the Employees Sheet and the Payroll Summary Sheet, create a

Data Model and generate a PivotTable to compare the budgeted take-home payment versus

actual payment based on the Job Title. Calculated fields in PivotTable are not required but

please make sure the PivotTable has meaningful label headers. To make it easier for other

users to view the movement of the salaries, visualise the PivotTable by inserting a Clustered

Column PivotChart. The PivotChart must have a meaningful chart title. You should put the

Pivot Table and the Pivot Chart in two separate worksheets and give each of the two

worksheets a meaningful sheet name.

Hint: These worksheets do not exist in the template.

Hint: ‘Job Title’ should be displayed in Rows.

Hint: You do not need to use calculated fields in the PivotTable, which means that you

do no need to add columns/rows manually in PivotTable.

Current Franchise Distribution Sheet

Currently, HFM stores are assigned suburbs as their franchise area (where they have

exclusive rights to provide HFM services) in an ad hoc manner. Henry does not like the

fairly random manner by which this allocation was made.

You are to model the Current Franchise Distribution and calculate the ‘Total Number of

Customers’, ‘Total Distance’, and ‘Total Subscription Revenue’ for each store using the

layout in this sheet. In doing so, calculate the ‘Distance’, Number of actual subscribers by

each subscription type, and ‘Revenue’ in this sheet for all suburbs of HFM.

Hint: You will need to use some of Excel’s Lookup & Reference, Math & Trig, and

Logical functions in some of your formulas.

Hint: You will need to use one of Excel’s Logical functions to ensure a 0 value for any

suburb without an assigned shop code in this table.

Distance is the distance from each assigned franchisee store to each suburb. The current

assignment of suburbs to the franchisee is indicated in the template.

Actual subscribers are different from prospective subscribers. A prospective subscriber is the

likely maximum number of HFM subscribers in the suburb indicated by market research.

The actual number of subscribers is dependent on the number of prospective subscribers and

their distance from the nearest store. Prospective subscribers are identified in the Distance

Survey and Suburb Profile Table of the Constants sheet.

Actual subscribers is the number of prospective subscribers reduced by 10% for every 1

whole kilometres away (rounded down) from the nearest store until there are 0 actual

subscribers. For example, a suburb with 62 prospective subscribers that is 4.8 kilometres

away from the nearest store would have 38 actual subscribers. Mathematically, this can be

represented as (𝑎𝑐𝑡𝑢𝑎𝑙 𝑠𝑢𝑏𝑠𝑐𝑟𝑖𝑏𝑒𝑟𝑠 = 62 − ⌊62 × ⌊
4.8

1
⌋ × 0.1⌋).

Essentially – the further away prospective subscribers are from the store, the fewer actual

subscribers there will be.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 11

Subscription Annual Revenue is the number of actual subscribers in each suburb according

to the assigned store multiplied by the subscription rate for each subscription type (13, 26, or

52 weeks subscriptions) multiplied by the average number of weeks in a year (52.18). 2

Hint: Although subscribers may take up the service, or may drop the service, or change

subscription, you should assume that such changes cancel each other out – that is, you

may assume that the number of subscribers does not change over the twelve-month

period and all customers renew their subscription for the period (or, those that leave

are replaced by new customers).

Hint: To do this last requirement (row 55), you need to add column totals for each

column in Current Franchise Distribution.

You should then complete the Summary Table of Current Customers, Distance, and Revenue

by store as indicated in the template. The Revenue per Kilometre is the Total Revenue for all

stores divided by Total Distance for all stores.

Franchise Redistribution Sheet

As mentioned, Henry is very environmentally conscious and wants to reduce greenhouse gas

emissions and increases HFM’s environmental credentials. Rather than the previous random

arrangements, Henry wants to ensure that all suburbs are serviced by the closest HFM store.

You are to use the Solver analysis in this worksheet to work out a possible redistribution plan

to distribute groceries from the four franchisees to all suburbs, ensuring that the total

distance of stores from the suburbs assigned to them is minimised.

For ‘variable area’ in Solver analysis, use the area highlighted in light orange on this sheet in

the template. These variables are binary (0 or 1) and are used to assign suburbs to stores.

You will need to use the Simplex LP Solving Method.

In this sheet, each row represents an inner-city suburb, and each column headed with a shop

code (Columns B, C, D & E) represents the assigned shop (the highlighted light orange area).

In the intersecting cell of the shop and the suburb, a 0 indicates that the shop is not assigned

to the suburb, whereas a 1 indicates that the shop is assigned to the suburb.

The key constraints are that each inner-city suburb should be assigned to one, and one only,

store. Further, the variable area (the light orange cells) is either 0 or 1 (i.e., binary). The

solver should be used to assign each suburb to its nearest store. The solver solution (i.e.

original values) in the template is the current franchise distribution.

Note: Please note that your formulas in this solution should be efficient. You can use a

Lookup and Reference function to achieve this.

After you fill in the Total Franchisee assigned column, identify the current shop code

assigned to each suburb name and identify the newly assigned shop code for each suburb

name. Using this code, identify the New Shop Location (i.e. the suburb of the assigned

shop) in the next column.

2 This is calculated on the basis that there are on average, and taking into account leap years, 365.25 days each

year. 365.25/7=52.18 (rounded to two decimal places).

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 12

The Distance column shows the distance between the currently-selected shop to the assigned

suburb (i.e. the cell in the light orange matrix with a ‘1’ in it). You should calculate this

using an efficient formula.

Hint: Consider both the ‘Distance to Shop Calculation Matrix’ and the Franchise

Redistribution Solver solution.

Hint: Remember that you used a Math & Trig function in Distance Survey and Suburb

Profile Table to determine the distance from each suburb to each shop. The Distance to

Shop Calculation Matrix is automatically filled once your Distance Survey and Suburb

Profile Table in the Constant Sheet is completed.

Then, identify the subscriber numbers (13, 26 and 52), and total revenue based on this

arrangement. Note that these actual subscriber numbers are calculated according to the

same formula outlined in the Current Franchise Distribution sheet (i.e. actual

subscribers are calculated based on the number of prospective subscribers and the suburb’s

distance from the new assigned store – the nearest store).

Hint: You will need to use some of Excel’s Lookup & Reference, Math & Trig, and

Logical functions in some of your formulas.

Calculate the total figures for these columns at the bottom of the table.

To easily identify the suburbs and franchisees that require changes, use Conditional

Formatting to highlight (background only) the ‘Suburb Names’ in Green (Column A) for

each row if the assigned store stays the same, and highlight (background only) the ‘Suburb

Name’ in Bright Orange (Column A) if the assigned store changes.

Similarly, use Conditional Formatting to highlight (background only) the ‘New Shop Code

Assigned’ (Column H) cells for each row in Bright Orange if the assigned store changes.

Save the results of Solver to a new answer sheet and restore the original values before

submitting.

Note: It is important that you restore the original values after running Solver.

Note: Copy the original matrix values for the highlighted yellow section from the

original sheet if you overwrite these values in error.

Finally, you should complete the summary table of the revenue by store as indicated in the

template. Identify the suburb name of each shop code using a formula, and the remainder of

the summary table can be completed using Mathematical and Trig function.

The Revenue per Kilometre figure is the Total Revenue of all stores divided by Total

Distance of all stores.

Further Analysis using Pivot Table and Pivot Chart

You are to create a Pivot Table and from that you create a professionally formatted Pivot

Chart using the information on the Franchise Redistribution Sheet. You should put the

Pivot Table and the Pivot Chart in two separate worksheets.

Hint: These worksheets do not exist in the template.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 13

When creating the Pivot Table, set the Suburb Name as a filter on the Pivot Table Fields so

that the Pivot Chart can be modified to focus on the selected suburbs according to the

viewer’s wishes. The row labels of the Pivot Table should be the four actual suburbs in

which stores are located, and there should be three columns that calculate the number of

subscribers for each of the three subscription types for each store. You should also include

another column for total revenue.

Hint: You do not need to use calculated fields in the PivotTable, which means that you

do no need to add columns/rows manually in PivotTable. You should make these

happen by customizing the ‘PivotTable Fields’ panel.

Then you create a Pivot Chart (including the chart title, axes titles, colours, data labels, etc)

as a Combo Chart Type on its own worksheet.

Hint: For the Combo Chart type, consider Line Chart & Cluster Column Chart. You

should also set up a secondary axis.

The Chart should show the new shop location, the name of the suburb on Filter, the number

of subscribers of each subscription types, and total revenue to allow them to be compared.

Submit this Pivot Chart with all data shown (i.e. all shops with all suburbs assigned to them

shown on the graph).

Note: You should put the Pivot Table and the Pivot Chart in two separate worksheets

and give each of the two worksheets a meaningful sheet name.

Note: The pivot table should be edited through either the Pivot Table Fields or Pivot

Chart Fields in Excel.

New Store Investment Sheet

HFM is a growing business. Henry is thinking to build a new franchisee store at Rocklea in

responds to his thoughts on expanding their presence at the Rocklea Grocery Markets in a

few years’ time. This would allow him to acquire groceries directly and more cheaply, thus

being able to provide more benefits to the customers.

Henry wants to save the same amount each term (monthly or fortnightly) for building a new

store several years from now that will cost a certain amount of money. He investigates

several scenarios of money deposit to undertake this investment plan. The monthly /

fortnightly deposit would be used to cover the fix cost and variable cost of building a new

store in a few years.

You are to use the Excel Scenario Manager to create a Scenario Summary for each of the

following scenarios:

• Medium Case: Fix cost of $ 85,000, Variable cost of $15,000, Annual interest rate
for saving of 1.5% and 12 monthly deposits each year over 5 years.

• Best Case: Fix cost of $ 90,000, Variable cost of $12,500, Annual interest rate for
saving of 2% and fortnightly (26) deposits each year over 3 years.

• Likely Case: Fix cost of $ 100,000, Variable cost of $11,000, Annual interest rate for
saving of 2.5% and 12 monthly deposits each year over 6 years.

• Worst Case: Fix cost of $ 110,000, Variable cost of $13,000, Annual interest rate for
saving of 3% and 12 monthly deposits each year over 7 years.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 14

Use the Scenario Manager to include a worksheet on your Workbook that contains the

Scenario Summary. This is to summarise the four different scenarios.

Note: The cells in Column B of the Output Area are the Result Cells for the Scenario

Summary.

You should add meaningful row labels to the scenario summary.

Hint: This means that you need to copy the labels in the Input Area and the Output

Area to the appropriate row in the Scenario Summary sheet.

To assist with the calculation, you must complete the Deposit Schedule for all deposits

identified in the scenarios.

Note: This schedule extends from cell D5:H5 to as far as you need to go to

accommodate the full deposit schedule for all scenarios considered.

The Deposit # 1 has been entered for you. You should enter formulas starting from Cell F7

to display 2, 3, 4 … for Deposit # instead of using auto fill series.

As the scenarios vary in the number of deposits, the length of the schedule will need to be

long enough to accommodate the highest number of deposits # in the scenarios. For example,

the Medium Case scenario has 12 monthly deposits each year over 5 years = 60 rows, the

Best Case scenario has 26 payments over 3 years = 78 rows, and the Worst Case scenario has

12 monthly payments each year over 7 years = 84 rows.

The opening balance for the first deposit should be zero. The closing balance is the amount

collected after the interest earned has been added to the deposit for each term. The closing

balance of one deposit period is the opening balance of the next deposit period.

You should use opening balance for each deposit and term interest rate to calculate term

deposit interest. Then you are required to use Excel’s Financial Function, PMT, to calculate

deposit for each term.

Hint: The amount of deposit for each term should be the same.

Hint: Your ‘Deposit Schedule’ should display deposit amount as positive numbers.

Hint: The last cell of your schedule should be exactly equal to the total amount of cost –

that is, after your last deposit the closing balance should be equal to the total amount of

cost of building the new store.

You should professionally but simply format the Deposit Schedule. This means that all rows

of the schedule that have values in them should have borders.

Hint: Refer to Font – ‘All Borders’ format.

This means that you should set your formulas so that the Deposit Schedule does not display

rows when payments are finalised (i.e. no more rows after the closing balance equals to zero).

Note: Do not provide totals for the columns of the Deposit Schedule as this information

will be displayed in the Output Area.

Hint: You can force rows not to display the results of functions by using the ‘If’

function in your formulas to set the cell value to “” if the final deposit has been done.

BISM7202 – Information Systems for Management

Dr Dongming Xu Page | 15

Hint: You are to use Conditional Formatting choosing one of the “Rule Type” so as to

not show borders of cells that have no values in them.

You will need to calculate the information in the Output Area using data entered in the

Input Area and calculated in the Deposit Schedule.

From the Input Area, you can calculate the Total Amount of Cost and Number of Deposits.

From the Deposit Schedule you can calculate the Deposit Amount for Each Deposit, the

Total Value of Deposit Made for building the new store, and Total Interest Collected Over

Life of Deposits. These three items should be calculated as positive, not negative,

numbers.

Comments to Henry Sheet

In undertaking this extensive analytical exercise, there are three comments you wish to raise

with Henry. You must address the following three points:

1. Considering the results of your analysis in the Current Franchise Distribution Sheet and

the Franchise Redistribution Sheet, identify and discuss a single weakness of the Excel

Model that relates to the business impact of the proposed redistribution plan.

You may discuss any weaknesses that relates to the actual Excel model developed or to

possible practical business problems that you identify with the proposed redistribution plan.

You must identify the weakness and answer why you think it is a weakness.

Note: Do not write a paragraph – 3 to 5 lines would be enough to identify the weakness

and explanation on why you think it is a weakness.

Note: The weakness should be related to the Excel business model rather than Excel

tool itself or Excel functions.

2. In the Current Franchise Distribution and the Franchise Redistribution sheets, you

calculated a ‘Revenue Per Kilometre’ figure. This is a business metrics that can reflect

HFM’s performance. Please identify another 3 business metrics that indicates a business’s

growth or decline.

3. Henry wants to know more about the Excel solution you have built for his business. Please

explain to Henry in detail that how Predictive Business Analytics is utilised in this Excel

model?

Hint: Predictive Business Analytics is the use of statistics and modelling techniques to

determine future performance based on current and historical data. It can be used to

improve Business Performance with Forward-Looking Measures.

Note: Do not write a paragraph – 3 to 5 lines would be enough to explain how

Predictive Business Analytics is utilised in this Excel model.