BISM7202 Information Systems for Management
Case Specification: MS Office
Assignment – Office 365 Excel
2019 SEMESTER 2 2020
UQ Business School
27th August 2020
Specification
Template
This specification document is a companion document to the Assessment Guideline.
The Excel template of the expected worksheets and associated data files are available on the BISM7202 Blackboard site. The Excel template provided must be used as the basis for the assignment.
You should 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).
As part of professional presentation, you must enter your student name in the header of the spreadsheet, and enter your student number, sheet name, and file name in the footer of the spreadsheet.
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.
Note: Entering your student name and number in the Document Control Sheet should be the first step that you do in undertaking this assignment.
IF YOU DO NOT ENTER YOUR STUDENT NAME AND NUMBER AS THE FIRST TASK YOUR SPREADSHEET SOLUTION WILL NOT WORK!
2
UQ Business School
27th August 2020
Background and Scenario
An innovative company called Gary’s Green Grocers to Go (GGG2G) has been set up that provides a door-to-door vegetable delivery service to the inner-city suburbs of Brisbane. GGG2G has existed for many years, but the new owner, Winona Trescott, has reinvigorated the business whilst employing the same employees.
Although it is an ‘old’ company, GGG2G is now set up as a franchise, which means that there is a ‘head’ franchisor with several franchisees who operate ‘under license’ from the franchisor. GGG2G has four franchisees in Logan City, but Winona Trescott is now looking to expand into targeted suburbs in the Brisbane City area.
GGG2G is all about the ‘Green’ – the company is environmentally conscious. Recently, GGG2G decided to start making its deliveries by pedal-powered cargo bike rather than by petrol-powered station wagon as was the practice in the recent past. Winona is very environmentally conscious and does not want to damage the planet to deliver vegetables.
Over its four years of operation in its new form, GGG2G has built up a trustworthy reputation among its customer base. GGG2G customers receive a ‘set vegetable’ box of in-season vegetables each week via their membership subscription program – delivered by a happy, smiling delivery person on a cargo bike.
GGG2G franchisees are licenced to deliver fresh vegetables by the box to homes and businesses in nearby suburbs within the ‘target suburbs’ nominated by Winona.
All the franchisees are based on an existing local fruit and vegetable shop, and Winona is looking for advice on where to locate the new shops within the target suburbs to maximise the number of potential customers. Due to the pedal-powered cargo bikes, however, the range of suburbs that each franchisee can service is limited, so the potential customers are only within a five-kilometre radius of the shop.
Also, due to the recent COVID-19 pandemic, Winona has enthusiastically joined a community initiative called ‘CareGiver’ (Winona is very ‘woke’). CareGiver provides free food to vulnerable members of the community, and pays a set fee when a box of food is delivered. Winona has decided that GGG2G will provide staff members with a box of fruit and vegetables to deliver to CareGiver recipients in the suburb where the staff member lives each day that they come in to work. Staff with fewer than two years of service are ineligible, as are franchisee managers and owners. If a staff member lives in a suburb with only one or two employees in it, then the staff member can deliver two boxes. Staff receive 50% of the CareGiver payment to GGG2G, unless they live in a target suburb in which case the staff member receives the whole payment amount. This is because Winona is really interested in promoting the business in the targeted suburbs.
Winona Trescott, the owner of GGG2G, has asked you to develop a spreadsheet that will help to determine where to locate the new franchise shops and maximise the number of potential customers within the targeted suburbs. She wants you to:
(1) Develop a schedule of employee CareGiver Commissions (along with current salary and superannuation payments).
(2) Present information from this schedule in a pivot table and a pivot chart.
(3) Identify CareGiver recipients that live in the target suburbs as a schedule.
(4) Complete the sheet analysing the distance between targeted suburbs (note – the formula is
provided) and (using the Solversheet) identify a potential solution as to where to locate the
new franchise stores.
(5) Undertake a scenario analysis for obtaining loans for the purchase of new Electric Cargo
Bikes; and
(6) Answer two questions about the Excel model in reflective comments and recommendations to
Winona.
3
UQ Business School
27th August 2020
Document Control Sheet
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 for values from the Constant sheet, make sure they are accessed using appropriate named ranges.
You should also format (but not edit) this sheet professionally.
There are 8 lookup tables or values contained in this Constant Sheet. You are to complete these as directed below.
Employee Salary Table
Employees are paid at different rates based on their job title. Each job comes with a different employer superannuation percentage rate that exceeds the legal requirement. The details of the different job descriptions are presented below. Further, this table identifies whether a particular job title is eligible for JobKeeper, and the threshold for service loading of an individual’s salary.
That is, once an individual has worked at GGG2G for a number of years equal to or exceeding the identified threshold, their salary is increased for each year of service (as at the start of the financial year) by the annual service loading annual rate.
This table is completed for you in the template.
Hint: Throughout the spreadsheet, cells with a light shaded green background require you to enter a value or a formula in them, or take some action with them.
Cells with a yellow 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.
Note: Entering your student name and number in the Document Control Sheet should be the first step that you do in undertaking this assignment.
IF YOU DO NOT ENTER YOUR STUDENT NAME AND NUMBER AS THE FIRST TASK YOUR SPREADSHEET SOLUTION WILL NOT WORK!
Note: Throughout this assignment, you must use a Named Range whenever referring to a range or a cell from a worksheet different to the current worksheet to ensure that someone reviewing your spreadsheet can understand it. 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
Note: In Australia, the financial year is for the period 1 July to 30 June, which is different to the calendar year that is for the period 1 January to 31 December.
4
UQ Business School
27th August 2020
Employee Superannuation Contribution Table
Employees at GGG2G have collectively agreed to contribute a percentage of their annual salary to their superannuation fund based on their age at the beginning of the calendar year as a post-tax contribution (‘non-concessional contributions’). This is not extra money received by the employee.
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.5%.
o Employees aged 60 and over have elected to contribute 6%.
You are required to complete the data entry of this table in the template.
Beginning of Calendar Year
Enter the first day of the 2020 calendar year (i.e., 01/01/2020).
You are required to complete the data entry of the table in the template.
Beginning of Financial Year
Enter the first day of the 2020-2021 financial year.
You are required to complete the data entry of the table in the template.
CareGiver Commission Rules
This table sets out quantitative rules (maximum packages per employee per working day and minimum years of service to be eligible to be part of the CareGiver program.
In designing your solution, note that employees may only deliver packages for the suburb where they live, franchisee managers and owners are not eligible (see Employee Salary Table), and employees receive half the amount paid by CareGiver as a commission for each box they deliver UNLESS the CareGiver package recipient is in a GGG2G Target Suburb (in which case the employee receives the whole CareGiver Payment).
Employees only receive one CareGiver box per day unless there are two or fewer employees that live in that suburb and who are eligible for the CareGiver program.
This table is completed for you in the template.
CareGiver Package Suburbs
This table sets out the suburbs with CareGiver recipients in them, and specifies the payment for each CareGiver box delivered in that suburb. The payment is calculated as a base of $15 per box plus a $1 payment for every kilometre away from Brisbane City that the suburb is, on average, located.
Location details are specified in the Latitude and Longitude columns. This represents an average ‘mid-point’ for the suburb.
This is a really complicated calculation that is interesting to use. Maybe you’ll use it again? Who knows, right? However, in this instance the calculation of the CareGiver Payment is calculated for in the first cell. You are to copy the formula to the remainder of the column to calculate the CareGiver Payment.
Hint: You may need to check that the formula is absolutely correct after copying it – you wouldn’t want anyone who uses the table as a reference to make an incorrect payment based on the wrong location in Brisbane (or even worse, as if it were on Null Island!).
5
UQ Business School
27th August 2020
The CareGiver Commission paid to employees is half of the total CareGiver Payment; the GGG2G Payment is the remainder of the CareGiver Payment. You are required to complete the table for the CareGiver Commission and the GGG2G Payment using a simple formula in the template.
GGG2G Distances and Percentages of Potential Customers Table
This table sets out the information for the algorithm that determines the percentage of potential customers from each suburb that a store location will receive according to the distance from the store.
From 0kms up to but not including 2kms, the store will service 25% of potential customers.
From 2kms up to but not including 3kms, the store will service 15% of potential customers.
From 3kms up to but not including 5kms the store will service 5% of potential customers.
The store will service no potential customers for suburbs that are 5 kms or more from the store.
The first row of the table is completed for you. You are required to complete the table for the remaining rows explained above in the template.
Target Suburb Profile Table
Each row in this table is a GGG2G target suburb in the Brisbane area. The latitude and longitude of an ‘average’ (centroid) point for each suburb is provided. There are 37 suburbs in this table. You are to use this information to determine the distance between each suburb and each store in the Matrix of Suburb Distances sheet.
The first column is the name of the suburb. The second and third columns identify the centroid point for each suburb according to latitude and longitude.
The fourth column identifies the number of households in the suburb and the final column identifies the potential customers in that suburb according to market research undertaken by GGG2G.
This table is completed for you in the template.
Employees Sheet
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 GGG2G employees and provides an overview of the working arrangements for employees, their salary and superannuation, and their eligibility for the CareGiver program. Your first tasks are to:
(1) Column L: Insert a formula that calculates the Years of Service for each employee according to their first working day in comparison to the first day of the financial year identified in the Constant Sheet. Years of Service is a whole number, and any fraction of the year counts as a full year. For example, someone whose first working day was 20th October 2013 would have 7 years of service
(2) Column M: Using the information set out in the Employee Salary Table in the Constant sheet and by reference to the employee’s Job Title and Years of Service, determine the employee’s annual salary including the service loading. For example, someone who has 6 years of service and is a Delivery Service Manager would receive an annual salary (including Service Loading) of $66,046.05.
Note: The fourth and fifth columns are not the same for all students and so your solution will likely vary from the solution found by other students for the New store Locations problem.
DO NOT EDIT THE CONTENTS OR FORMULA IN THIS TABLE
Note: Please note that employer superannuation is calculated on the basis of the employee’s annual salary. Superannuation is not included in annual salary.
6
UQ Business School
27th August 2020
(3) Column N: Using the information set out in the Employee Salary Table and by reference to the employee’s Job Title and Employer Super Rate, calculate the employer’s contribution to the employee’s superannuation. Note that this contribution is in addition to the employees annual salary.
Note: This employer super contribution is in addition to the employees annual salary.
(4) Column O: Using the information set out in the Employee Superannuation Contribution Table, and by reference to the employee’s age as at the beginning of the calendar year, calculate the employee’s superannuation contribution to their superannuation. Note that this contribution is paid by the employee out of their salary, and so does not affect their total salary package (whereas annual salary with service loading, employer super contribution, and weekly CareGiver Commission do).
(5) Column P: This formula is provided for you. This very useful formula identifies the suburb in the address by reference to the suburbs listed in the Constant sheet for CareGiver Package Suburbs – fortunately, no GGG2G employees live in a suburb not listed as CareGiver Package Suburbs.
Hint: This very useful formula may be very useful elsewhere in your spreadsheet.
(6) Column Q: Using a lookup-based formula with logical functions, determine whether, given their job title (Employee Salary Table) and their years of service to GGG2G, the employee is eligible to deliver CareGiver packages. Use a “Y” to indicate that the employee is eligible to deliver CareGiver Packages, and an “N” to indicate that they are not eligible.
(7) Column R: Insert a formula that calculates the number of packages per working day the employee is entitled to deliver according to the CareGiver Commission Rules set out in the Constant sheet.
(8) Column S: Calculate the Weekly CareGiver Commission received by the person delivering the CareGiver packages according to the number of days they work per week and according to the CareGiver Commission Rules set out in the Constant sheet.
(9) Column T: Calculate Total Salary Package, which includes annual salary (including service loading), employer super contribution, and Weekly CareGiver Commission.
(10)Use Conditional Formatting to format each full row of the GGG2G Salary Overview and CareGiver Commission Schedule so that employees who are delivering no CareGiver Packages per working Day are highlighted as red text on a light red background, and those who deliver two CareGiver Packages per working day are highlighted as green text on a light green background. Finally, use conditional formatting so that employees who deliver one CareGiver Package per working day is highlighted as orange text on a light orange background. There should be no employees delivering more than two packages each day.
Hint: You will need to use the employee’s date of birth in this formula to determine their age on the date in the Constants sheet (Beginning of Calendar Year) and compare this to the Employee Superannuation Contribution Table.
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 5 March 1990 would not be 29 on 1 January 2020 and so would not pay any extra contribution.
Hint: There is a new function in Excel 2019 that is similar to the CountIf function. However, instead of counting the values that meet criteria for a single range, this new function can be used to count the number of times that two values in a row in two ranges together meet the two criteria you provide.
Hint: To convert a fortnightly payment into an annualised payment figure, every paymaster knows to multiply the fortnightly payment by 26.09.
A fortnight is made up of two weeks.
7
UQ Business School
27th August 2020
(11)At the bottom of the table (row 89 to 93) you will see a ‘Totals Table’ section that provides summary figures for the schedule (rows 5-82) for the columns L-O and R-T. You are to insert a formula that calculates the total, minimum, maximum, and average figures for each column shaded green in the schedule for these rows and columns.
(12)Using Database Functions, and based upon the Criteria for Summary Table Database Functions in row 85/86, for each column (again, L-O and R-T) total the minimum, maximum, average, and total so that changing the criteria in row 86 results in changes to the summary table values in rows 98-101. You should also use a database function to count the number of records in the schedule that meet the criteria for cell K96.
(13)When submitting the spreadsheet, set the criteria for summary table database functions so that the Summary Table displays data relating to the Job Title of “Delivery Service Manager” and less than 5 years of service.
Pivot Table and Pivot Chart Sheets
Winona would like a visual representation and summary of the information in the Schedule set out in the Employees sheet. You are to create a professionally formatted and interactive Pivot Chart (considering the chart type, title, colours, etc) on its own worksheet. This pivot chart is to use the Schedule in the Employees sheet as a basis (i.e., the data in the Employees sheet, rows 5-82).
Creating the Pivot Chart creates the Pivot Table Sheet. You should then move the Pivot Chart created on the Pivot Table sheet to its own worksheet (it should not be a ‘floating object’ in a worksheet).
Label the Pivot Table worksheet created in doing so as ‘Pivot Table’ and the chart worksheet as ‘Pivot Chart’.
The pivot table will be edited through either the Pivot Table Fields or PivotChart Fields in Excel.
Set the Pivot Table so that each column of the table (apart from the ‘Row Label) shows the Employee’s Job Title (and a Grand Total). Each row of the table should show the employee’s Residential Suburb.
In the cells of the table should be a sum of all the packages delivered by all the employees that live in the suburb. The final column, and final row, are the grand total of the row and column respectively.
To make this an interactive data visualisation, use a slicer to analyse the Pivot Table based on the employee’s Job Title – make sure that the slicer is easily visible near the Pivot Table. Using the Slicer, filter the table to show only those jobs eligible for the CareGiver program (i.e. all job titles except Franchisee Manager and Owner) before submission.
You should ensure that this filter is applied in the slicer when the assessment is submitted.
Format the pivot table professionally, including a meaningful heading. Aim for clarity in your presentation.
The Pivot Chart should then reflect the selected information in the Pivot Table.
Hint: Database functions begin with a “D” and rely on criteria set out in a range that you identify and populate with your criteria.
Hint: You will need to create the PivotChart and then – after right-clicking on the chart – click ‘Move Chart’ to place it in its own worksheet.
Hint: These worksheets do not exist in the template – you will need to create them by using the PivotChart on the ribbon or menu.
Hint: This is one of those occasions when you are asked to change the workbook’s structure or cell locations – see above.
Hint: You should ensure that this filter is applied through the slicer when the assessment is submitted.
8
UQ Business School
27th August 2020
Format the chart professionally, including a meaningful chart title and axis titles.
You may select the chart type that makes the most sense to you, though simple is often better. It is very important that the user of the charge can see the number of CareGiver packages delivered in each suburb, and can identify which job titles are doing the delivery (for example, two packages are delivered by accountants and two packages delivered by delivery service managers).
Hint: Changing the Pivot Table Slicer must change the Pivot Table Chart.
CareGiver Recipients Sheet
Next, Winona wants to understand the recipients of CareGiver packages and those that are in a target suburb for GGG2G. The CareGiver Recipients sheet is currently empty. However, it was intended to keep a record on recipients and their contact details. You have been provided with a file of sample customer contacts by the CareGiver charity as a separate data file.
That is, you have been provided with a CSV file that contains data. You are required to import the contacts in the Customer Contacts Sheet and place it starting in cell A3 (this row will be the headings in the CSV file). The headings are Title, GivenName, MiddleInitial, Surname, TelephoneNumber, and Address. You are to convert the imported dataset into a table.
A bit apologetically, Winona tells you that some entries in the customer listing may have been accidentally recorded twice or several times. She tells you to remove any duplicated addresses that may have been accidently recorded. So, you may need to ‘wrangle’ the data a little bit.
Using the newly created table, you must alter this spreadsheet to add and populate the following columns using appropriate lookup functions at the end of the table (i.e. in columns G and H):
(1) Column G: Add a column entitled ‘CareGiver Recipient Suburb’. Derive this column from the address column using an appropriate formula that searches for the suburb in the address (e.g. “Toowong”).
(2) Column H: Add a column entitled ‘Is Target Suburb?’. Derive this column from the ‘Target Suburb Profile – Location and Household/Potential Customer Count’ table on the Constant sheet. If the suburb where the CareGiver Recipient is a Target Suburb, then show this value as ‘True’ or ‘Y’. If not, then show this value as a ‘False’ or an ‘N’.
Format the table appropriately to make each row of the customer contact table visually distinct (alternating the colour of each row would address this requirement).
Matrix of Suburb Distances
Winona wishes to select the location of her new franchisees according to the largest number of customers they are able to service in a sustainable manner.
This table is central to those calculations, and is used in the next sheet. You are to complete the ‘Matrix of Suburb to Suburb Average Distances table in this sheet according to the following directions.
Each row in this table (row 7 to row 43) is a suburb in the Brisbane area identified in the ‘Target Suburb Profile’ table in the Constant sheet. Columns D to AM are matched pairs with each suburb in row 7 to row 43. That is, the order of the rows of suburbs is the same as the order of the columns of suburbs.
Hint: If Winona tells you to do something, it is really important that you do it! She is the client after all!
Hint: You will need to search for the suburb name in the customer’s address to determine the CareGiver Recipient Suburb. You will find an example of how to do this in a sheet referenced earlier in this specification. And no, you can’t just look for the number of commas in the address.
Hint: This is one of those occasions when you are asked to change the workbook’s structure or cell locations – see above.
9
UQ Business School
27th August 2020
You must use a formula (use the suburb name as a key) to look up the latitude and longitude for each suburb and populate column B and Column C, and row 4 and row 5, with latitude and longitude as indicated in the template. This information is obtained from the ‘Target Suburb Profile’ table in the Constant sheet. This latitude and longitude represents an ‘average’ (centroid) point for each suburb.
You are to use this information to determine the distance between each suburb using the latitude and longitude. To calculate this, use the latitude and longitude of each location according to the following formula:
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.
Format this table appropriately.
Solution to New Store Locations
Winona wishes to select the location of her new stores according to the largest number of customers they are able to service in a sustainable manner.
You are to use the Solver feature in this sheet to work out an optimal combination of stores in suburbs to maximise the number of serviceable potential customers. There are three steps to undertake that will allow you to create your own solution to this problem of identifying the location of these stores.
The first step to do is to calculate potential customers in the range E4:AO40. In doing this, you are creating a matrix of every suburb where the intersecting cell value is the number of potential customers that store will service.
Potential customers for each suburb are a function of the suburb’s distance from the new store’s location, and this diminishes the further away from the store the suburb is. The formula for calculating this figure is derived from the ‘GGG2G Distances and Percentage of Potential Customers’ table in the Constant sheet, and you use this information in combination with the distance you calculated in the ‘Matrix off Suburb Distances’ sheet to calculate the potential customers for each two-suburb combination.
For example, a suburb that is less than two kilometres away from a store would receive 25% of the potential customers identified for that suburb in the ‘Target Suburb Profile’ table of the Constant sheet. Suburbs that are more than five kilometres apart will have a result of zero.
Note: Please note that your formulas in this solution should be efficient.
Recall that stores only have a limited range as delivery of fruit and vegetables is by pedal-powered cargo bike.
Distance = ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371
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 the same suburb in this matrix will be 0. For example, the distance between the suburb of Woolloongabba in the row of this matrix and the suburb of Woolloongabba in the column of this matrix should be 0. If it is not zero – then your formula is definitely wrong!
Note: The template has been deliberately constructed so that different students will have different solutions to this problem.
Hint: You can use Lookup and Reference functions to achieve this outcome. For example, you can lookup each suburb name in the row that matches to the same suburb’s column in the ‘Matrix of Suburb Distances’ sheet to identify the distance between suburgbs. You can then use that distance to lookup the weighting from the ‘GGG2G Distances and Percentage of
10
UQ Business School
27th August 2020
The second step is to calculate serviceable potential customers for each suburb if it has a store. The sum of each row of the range E4:AO40 is the total potential customers if a store is located in that suburb. The ‘Serviceable Potential Customers’ column is that value if a store is present.
Add a total in Row 42 for the ‘Has a Store’ column (this counts the number of allocated stores) and the ‘Serviceable Potential Customers’ (this is the total number of potential serviceable customers given the location of stores in suburbs.
The third step is to use Solver to identify where the new GGG2G stores should be located.
The objective is to maximise the total number of ‘serviceable potential customers’ you identified in Row 42.
For the Solver feature’s ‘variable cells’ area, use the area highlighted in yellow on this sheet (and set to all 0s) in the template. These yellow cells (variables) are binary (0 or 1) and are used to assign stores to suburbs. That is, if there is a ‘1’ in this cell, then a store is to be located in that suburb.
You will need to use the Simplex LP Solving Method.
The key constraints are that the yellow cells are binary (i.e. 0 or 1), and there must be exactly 4 stores assigned in total (see Row 42) – there cannot be 5 or more stores and there cannot be 3 or fewer stores. With this information you can run the Solver and obtain your own solution of where to locate stores for the maximum serviceable potential customers.
Save the results of Solver to a new ‘answer’ sheet. This answer sheet records that you executed the Solver and that it worked for you. Give the answer sheet a meaningful name, locate it after the Solution to New Store Location sheet, and be sure to delete any answer sheets that are not your final solution.
You must restore the original values in the yellow cells before submitting your assessment (i.e. set cells B4:B40 to 0).
Note: It is important that you restore the original values after running the Solver reports.
Mortgage Simulator
GGG2G is a growing business. Winona does not believe interest rates will increase, and thinks that – when things return to ‘normal’ post-COVID – it will be time to grow the business by leveraging the current low interest rates available and taking out a business mortgage to expand the business.
Winona would like you to investigate several scenarios for a business loan to undertake this expansion. The loan would be used to pay for brand-new electric cargo bikes to deliver fruit and vegetables with. These bikes would replace the pedal-powered cargo bikes GGG2G currently use. This money would also be used to ensure proper training for GGG2G deliver riders.
You are to use the Excel Scenario Manager (a form of What If analysis) to create a Scenario Summary for each of the following scenarios:
• Best Case: Cost of Works of $120,000, training costs of $7,500, Interest rate of 2% and 12 monthly repayments for 7 years.
• Worst Case: Cost of Works of $180,000, training costs of $15,000, Interest Rate of 5%, and 26 fortnightly repayments over 3 years.
Potential Customers’ table in the Constant sheet. Finally, you can then use that information to calculate the percentage of the potential customers according to what you find by looking up the ‘Target Suburb Profile’ table of the Constant sheet
Hint: The Solver instructions are to use a 1 in the ‘Has a Store’ column if there is a store to be located in that suburb. You can use that information to set serviceable potential customers to zero if there is no store in that suburb.
Hint: You will need to install the Solver Add-In if it is not already installed on your computer. Also – if Solver’s parameters are incorrect it can occasionally take a very long time to complete; you will need to stop the solution and check your parameters in this instance.
11
UQ Business School
27th August 2020
• Likely Case: Cost of Works of $160,000, Compliance costs of $10,000, Interest Rate of 3.25% and 12 monthly repayments for 5 years.
Use the Scenario Manager to include a Scenario Summary sheet on your spreadsheet that contains the Scenario Summary. This sheet is to summarise the three different scenarios.
You should add meaningful named ranges so that the row labels in the scenario summary are meaningful rather than cell references.
You should also format the Scenario Summary to be professional and informative.
To assist with the calculation, you must complete the Schedule of Repayments for all payments identified in the scenario. The first column is a simple counter that indicates the payment number starting at 1 and ending at the number of payments over the life of the loan.
The Principal is the opening balance for each payment period, and the closing balance is the amount left after the interest has been applied to the loan and the repayment has been made. The closing balance of one payment period is the opening balance of the next payment period.
You should use the IPMT and PMT functions to calculate the interest amount and the repayment amount respectively.
As the scenarios vary in the number of payments, the length of the schedule will need to be long enough to accommodate the highest number of repayments in the scenarios. The Best Case scenario has 12 fortnightly repayments over 7 years = 84 rows, the Worst Case scenario has 26 payments over 3 years = 78 rows, and the Likely Case scenario has 12 payments over 5 years = 60 rows).
You should professionally but simply format the Schedule of Repayments. This means that all rows of the schedule that have values in them should have borders all around each cell.
This also means that you should set your formulas so that the Schedule of Repayment does not display rows when payments are finalised (i.e. no more rows after the closing balance equals zero).
You will need to calculate the information in the Output Area using data calculated in the Schedule of Repayments or entered in the Input Area.
From the Input Area, you can calculate the Amount of Loan and Number of Payments (i.e. the number of payments per year multiplied by the number of years indicates the overall Number of Payments).
Note: The value cells (Column B) of the Output Area are the Result Cells for the Scenario Summary.
Hint: You will need to remove blank rows and columns, uninformative columns (e.g. Current Value), and associated notes.
Note: This schedule extends from cell D5:H5 to as far as you need to go to accommodate the full payment schedule for all three scenarios considered.
Hint: The IPMT and PMT functions calculate negative numbers – be sure that your closing balance formula takes this into account.
Your schedule should display interest paid as positive numbers and repayments as negative numbers.
Hint: The last cell of your schedule should be exactly equal to $0.00 – that is, at the last displayed payment the closing balance should be zero.
Note: Do not provide totals for the columns of the Schedule of Repayments 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 payment has been made. You can also use Conditional Formatting using a Classic Formula so as to not show borders of cells that have no values in them.
12
UQ Business School
27th August 2020
From the Schedule of Repayments you can calculate the Repayment Amount for each payment (they are all the same for a single scenario, and change as the scenario is changed), the Total Value of Repayments Made to Repay the Loan, and Total Interest Paid over Life of Loan.
These three items should be calculated as positive, not negative, numbers.
Comments to Winona
In undertaking this extensive analytical exercise, there are two points you are to address for Winona:
1. Identify a weakness (i.e. where it is ‘wrong’) in the developed Excel model that you have developed (2 marks).
Explain why this weakness is a weakness (3 marks).
The weakness you decide to discuss is up to you, but it must relate to the Excel model developed.
The weakness may be an weakness internal to the model – that is, the weakness is inside the actual Excel model developed. For example, the Excel model might have a flaw that means it does not do what is intended, or perhaps the model might be easily broken (‘fragile’) in some way.
Or, the weakness may be an external weakness – that is, that the model does not represent the real world faithfully. For example, the Excel model might not represent real-world practical concerns faithfully.
You must identify the weakness and explain why it is a weakness.
Hint: This discussion will likely require two paragraphs of 3 to 5 lines each.
2. In the course of your post-graduate degree, you discussed different types of decisions that decision-making systems assist you with.
What type of decision is Winona making here (2 marks)?
On reflection, explain whether you consider that the model you have built is both sufficient and
complete for making the type of decision needed, and why (3 marks).
Hint: This discussion will likely require two paragraphs of 3 to 5 lines each.
Hint: The total value of repayments made to repay the loan less the total interest paid over life of loan should equate to the initial Amount of Loan.
HINT: THESE COMMENTS ARE WORTH 10% OF THE ASSIGNMENT, OR 4% OF YOUR COURSE MARK – TO GET A GOOD GRADE YOU WILL NEED TO GIVE A CONSIDERED ANSWER.
13