This assignment is designed to reinforce the following skills and areas of knowledge:

· Preparing data for import into a database

· Create an Access database

· Build queries to answer typical business questions

· Construct pivot tables to analyse datasets

· Use Excel tools to allow for the targeted filtering and scrutiny of data

· Perform an AIS investment analysis

· Communicate the process followed in building an AIS

· Offer advice on AIS design options

· Communicate AIS concepts in an appropriate written format

Mapping Assessment Task to Course Objectives:

· Understand the use of information systems for business and analyse and make recommendations on the utilisation and design of such systems

· Interpret business process documentation

YES

· Requires analysis and design of AIS elements, including a database, data visualisation, and data analysis

· Requires analysis of documentation and

including flowcharts and data flow diagrams YES

· Communicate with database professionals about

the design of databases YES

·Critique computerised and manual business

identification and modelling of relationships in Access (UML Class Diagram)

· Requires preparation of a database (data checking, importing, table design, relationships, queries),

· Requires critical involvement in the design of

processes in terms of their exposure to risk, and suggest internal controls that address the risks

· Apply critical thinking and problem solving to activities dealing with accounting information systems.

YES

YES

an AIS and the information it provides to support decision making

· Requires critical assessment and problem solving skills during the process of designing an AIS

SUPPORT REFERENCES:

· Lecture 6 – spreadsheet design

· Fairhurst (2019). Using Excel for Business and Financial Modelling: A Practical Guide. 3^rd edition. John Wiley& Sons Ltd: West Sussex, UK

TASK DESCRIPTION

This assignment task draws upon the practical skills in Microsoft Access and Microsoft Excel that have been covered in workshops and lectures. In this task, you and your group members are required to apply the concepts learned in class to a business case and develop a series of business solutions that support the provision of information for decision making and analysis. This task involves building queries in Access and designing worksheets in Microsoft Excel.

This assignment must be completed in groups. Group work, allocation of tasks to team members, managing group project progress, and working together to solve problems and achieve goals are key transferrable skills that are in increased demand in the workforce.

CASE DESCRIPTION

Melbourne Bikes has reached a critical point in its business. Their first year of operations was a success, but they have come to realise that they cannot run their business in an Excel spreadsheet alone. The use of the Excel spreadsheets being sent back and forth is no longer sustainable. As a result the business is considering switching to an accounting and booking system that all of the staff can use. This course of action has been proposed by DataFast, an AIS provider.

However, Bill and Trent, the two owners, are concerned about the financial implications of investing in a technology-based asset and would like to see a model that allows them to determine whether or not the investment is potentially viable.

For analysis purposes, Bill and Trent break the first year of operation up into quarters, which are classified as follows:

· October quarter (1/9/2021-31/12/2021)

· January quarter (1/1/2022-31/3/2022)

· April quarter (1/4/2022-30/6/2022)

· July quarter (1/7/2022-30/9/2022)

Bill and Trent, who recently hired you as their junior accountant, have given you some Excel worksheets that have some financial details from their first year of operations at Melbourne Bikes. The spreadsheet has details of daily hire activity and related data classes. However, you notice that it is not suitable for analysis. In order to do the required analysis, you will need to develop some Access queries and perform some analysis in Excel.

DATABASE

Using the spreadsheet data in the start file, create an Access database. Your database should:

a. Contain 4 tables

b. Have appropriate relationships between tables with referential integrity enforced

Based on the database tables that you have constructed; each group member must prepare one (1) query. The query name should include the student ID of the person who prepared it (e.g. Query 1 – Total Revenue SID12345678). If a group member does not prepare a query then they will not receive marks for the query component.

Query 1 – TOTAL REVENUE

Query 2 – Revenue by Month (with months listed in chronological order)

Query 3 - Total Revenue by Quarter

Query 4 – Revenue Analysis (with months listed in chronological order)

EXCEL

To work through the Excel tasks, please use the source data that contained in the start file. Your group is required to build the following:

a. NAVIGATION PAGE

Construct a title / navigation page that provides links to all of the content in your file. The file should contain the following:

- The name and student number of each group member

- Links to the different worksheets in your Excel file

- Colour coding in the worksheet tabs and navigation page contents to classify the different page types (e.g. budget worksheets one colour, pivot tables another colour etc).

b. EXCEL DASHBOARD

The owners would like a data dashboard that allows them to see a summary of their revenue performance. The dashboard should have the following:

1. Revenue analysis by Month and by Day of the Week

· When the owner selects a Month (i.e. January, February … December) from the dropdown list (which lists the NAME of the month), they should be presented with the following information:

i. Number of trading days (month)

ii. Total Revenue earned (month)

iii. Total bicycle hire hours (month)

iv. Average revenue per day (month)

v. Average bicycle hire hours per day (month)

· When the owner selects a Day (i.e. Monday, Tuesday … Sunday) from the dropdown list (they are interested in day of the week impacts on revenue) they should be presented with the following information:

i. Number of days that are the selected day

ii. Total Revenue earned on that day for the year

iii. Total hours hired on that day for the year

iv. Average revenue on that day for the year

v. Average hours hired per day for the year

2. A chart that shows total revenue per month that can be filtered by time (use a time filter

/ slicer)

3. A chart that shows total hours hired by month and can be filtered by time (use a time filter / slicer)

4. A section that summarises attainment of key KPIs:

The KPI summary should identify the Goals (in relation to the KPIs), the target and current performance, and indicate if the goal is being achieved or not. The key KPIs identified by management are as follows:

- % of days with >25 hours of bike hires – 25% goal

- Average daily revenue - $400 goal

- Days less than $150 revenue – 31 goal

- Average peak period daily income - $400 goal

c. EXCEL BUDGET

The business owners are considering investing in a new AIS. In order to get finance the owners need to show a complete cash flow report for October 2021- September 2022 and a forecast for the next three years. Based on the details that follow, prepare a cash report and the budget for the next three years.

As a starting point, the cash flow report for 2021-2022 should include the items shown in the following table:

Actual Performance Oct 21 - Sept 22

CASH RECEIPTS

Bike hours hired

TOTAL CASH RECEIPTS

CASH PAYMENTS

Trading Days

Labour hours per day

Hourly rate

Labour cost

Rent Electricity Internet

BeSpoke commission

TOTAL PAYMENTS

NET CASH FLOW

The amounts should be calculated using formulas and the data provided in the start file. You may need to perform calculations and apply formulas in the starting data in order to complete the task.

Using the historical results for 2021-22 as your starting point, prepare a budget for the next three years (1/10/2022-30/9/2025).

The budget should assume that the loan for the AIS is taken and the loan repayments occur at the end of the year. This means that you will need to add in any new expense/payment items that may arise and factor in the assumptions provided for the future years.

Your completed cash flow budget output should:

- Show the actual cash flows for the 2021-2022 year

- Show a budget for the next three years (1/10/2022-30/9/2025), showing how the investment in the new system will impact the cash flows for the business (e.g. any new cash flows as a result of the investment)

- Include the NPV and IRR for the investment

- Include a formula that advises on whether the investment should occur or not (Project investment decision)

Scenario Analysis

In addition, Trent, who is one of the owners, is concerned about the impact that the assumptions have on the project NPV. For this reason, he has asked you to consider three possible scenarios:

Trent would like to know how these would impact:

· The NPV of the project

· The project investment decision

ASSUMPTIONS FOR CASH FLOW REPORT AND BUDGET:

· REVENUE

· The actual cash receipts for the 2021-2022 operating year are contained in the Excel file.

· The hired hours per year are expected to increase by 2.5% per year across the three year estimate period

· The average hourly hiring rate for a bicycle is forecast to be $18.75, starting 1/10/2022

· The business aims to operate every day of the year, in order to capitalise on the tourist market.

· Melbourne Bikes has to pay BeSpoke Cycles a commission, calculated as a percentage of revenue, in return for BeSpoke Cycles providing the bikes for Melbourne Bikes to hire out.

· EXPENSES

The expected amounts for 1/10/2022 to 30/9/2025 are based on the assumption that a new AIS will be acquired.

· INFORMATION SYSTEM INVESTMENT

Bill and Trent both agree that the business is operating better than they expected and that their simple Excel Spreadsheet is proving inefficient at managing the business data. In fact, they have reached a critical point where if they do not invest in the system on 1/10/2022 they will simply not be able to keep the business going.

They have recently spoken to a representative from DataFast, an accounting software company, about investing in a customised accounting information system.

DataFast provided the following details:

· The accounting information system for Melbourne Bicycles would require an upfront payment of $20,000, to be paid in October 2022

· Melbourne Bikes will use borrowings and their own funds for this

· There would be a $2500 per year licence fee that would need to be paid

· A system upgrade in 2025 will cost $15,000

· FINANCE

Bill and Trent are looking at finance options to cover the cost of the system and have been offered a business loan by EQT Finance, who have agreed to lend $15000 to Melbourne Bikes, at an annual interest rate of 10%. The loan will commence on 1/10/2022 and be paid back over 3 years, with the payments made at the end of each year.

d. EXCEL LOAN ANALYSIS

In order to understand the loan options better, Bill would like to see the calculations behind the loan. He has requested a worksheet that shows:

- The calculation of the loan repayment amount

- A loan repayment schedule like the one shown below:

Loan Payment Analysis

Bill would also like to know more information about the loan. In particular, he would like to know how the amount that has to be repaid each month would change with different interest rates. He is particularly concerned with the rate range of 4% to 15% with 0.25% increments (e.g. 4%, 4.25%, 4.5%...10%) and with payment frequencies of weekly, fortnightly, monthly, quarterly, and yearly.

Required:

Prepare a worksheet that shows the:

· calculation of the loan repayment amount;

· loan repayment schedule; and

· periodic loan repayment analysis for the specified different interest rate levels and payment frequencies.

ASSIGNMENT REQUIREMENTS AND DELIVERABLES

Each group MUST submit the following files:

- an Excel worksheet

- an Access database

EXCEL

The Excel worksheet should demonstrate the application of the spreadsheet concepts we have covered this semester, these include:

· Clear organisation and layout

· Clear documentation of assumptions

· Application of relevant formulas to perform calculations

· Calculation of the ACTUAL cash receipts / payments for the 2021-22 year

· Calculation of the budget to the 2022-23 – 2024-25 year

· Calculation of the NPV and IRR for the AIS project

· Calculation of the loan repayment amount

· Data input controls where necessary

· Application of analysis techniques

o Data tables

o Scenario analysis

· Pivot table preparation

· Conditional formatting

· Clear formatting that allows for easy navigation and interpretation of the workbook and its contents

· Along with the data worksheets that came with the file, separate worksheet tabs should be created for the following:

o Navigation

o Dashboard

o Budget

o Loan

o Scenario summary

o Revenue pivot

o Hours pivot

ACCESS

The Access database should contain:

· the tables that you created;

· the relationships between the tables; and

· the queries that you built (with query names including the student ID of the person who prepared the query).