Reference no: EM132566922
ACCT6001 Accounting Information Systems, Laureate International Universities
Case Study - Excel based
Learning Outcome 1: Apply technical knowledge and skills in creating information for the workplace using spreadsheets and relational databases
Learning Outcome 2: Communicate with IT professionals, stakeholders and user groups of information systems
The Drone Experts
Richard Morris has recently opened an online store named The Drone Experts. He sells a very popular model of drone called the ‘Rocket Drone'. In an effort to widen his market, Richard has built a small website hosted by a web service provider. At the end of every week, the web service provider sends a data file to Richard that contains data about visitor traffic at his website. Richard would like to utilise this data to get insights on visitor usage of his website and he believes that the data would be valuable for business decision making. However, Richard lacks the technical knowledge to analyse and interpret this data and he is unsure how the results from the analyses can be used for making business decisions.
At the end of first week, Richard received a data file on visitor traffic. He decides to hire you so that you could assist him to analyse and interpret the data. The table 1 shows the first data file that Richard received on visitor usage of his website.
|
Operating System
|
Browser
|
Site Connection
|
Add to Favourites
|
Date
|
Entry Time
|
Exit Time
|
Number of
pages viewed
|
City
|
State
|
|
Windows
|
MS
Internet Explorer
|
DuckDuckGo
|
Yes
|
01/05/2020
|
21:30:45
|
21:50:49
|
5
|
Melbourne
|
VIC
|
|
Linux
|
Chrome
|
Yahoo
|
No
|
03/05/2020
|
16:20:23
|
16:23:26
|
1
|
Rockhampton
|
QLD
|
|
Linux
|
Firefox
|
WOW
|
Yes
|
03/05/2020
|
18:21:46
|
19:24:32
|
16
|
Campsie
|
NSW
|
|
Windows
|
MS
Internet Explorer
|
Ask
|
No
|
05/05/2020
|
09:36:21
|
09:45:20
|
4
|
Barton
|
ACT
|
|
Windows
|
MS
Internet Explorer
|
Dogpile
|
No
|
06/05/2020
|
12:24:20
|
12:24:40
|
1
|
Adelaide
|
SA
|
Figure 1
While Richard's website was visited only five times in the first week, Richard is expecting the number of visits to go up over time. Before the he gets more real data form the web service provider in the coming weeks, Richard wants to explore the possible analyses that can be done with this data. Richard handed you a document listing specific instructions on what he expects you to do in Excel. The instructions are listed below.
Requirements
(1) Open an Excel Workbook and name it as ‘Student ID_ Student Name_ ACCT6001 Assessment 3' (i.e. 0009989t_Adam_Smith_ACCT6001 Assessment 3). Create a worksheet labelled as ‘Visitor Data'. Make a table similar to the one above using hypothetical details for 45 visitors.
In your hypothetical data, assume: (a) all visitors accessed Richard's website from Australia, (b) at least 3 visitors accessed Richard's website from each of the Australian states and territories (i.e. at least: 3 visitors from New South Wales; 3 from Queensland; 3 from South Australia; 3 from Victoria; 3 from Western Australia; 3 from Australian Capital Territory; and 3 from Northern Territory), (c) All visits are made between 01/05/2020 and 28/05/2020. (d) Each visitor visits the website only once. So, each row of data contains information about a specific visitor.
The table should include the following columns: Operating system, Browser, Site connection, Add to favourites, Date, Entry Time, Exist Time, Number of pages viewed, City, States.
(2) In the ‘Visitor Data' worksheet, add a column labelled as ‘Time Spent On Website'. This column should show the time that each visitor spends on Richard's website. The values for this column should be calculated by using the values of Entry Time and Exit Time columns. The values should be reported in the h:mm:ss format.
(3) Create a new worksheet in your workbook and label it as ‘Visit Duration'. Report the descriptive statistics (i.e. mean, median, maximum, minimum) for Time Spent On Website and the Number of Pages Viewed using appropriate excel functions. The descriptive statistics should be linked to the ‘Visitor Data' worksheet.
Include a table in this worksheet showing the total number of visits and the total number of viewed pages, by week. The values for each week should be directly calculated from the ‘Visitor Data' worksheet. The table may look like Figure 2.
|
Visit Count/Viewed Pages
|
|
|
Week 1
|
Week 2
|
Week 3
|
Week 4
|
Total
|
|
Visit Count
|
30
|
46
|
32
|
49
|
157
|
|
Visited Pages
|
92
|
105
|
87
|
127
|
411
|
Figure 2
Richard mentioned, "I believe visitors are likely to visit more pages if they spend more time on my website". Do you find support for this belief? Calculate and report the results for correlation to support your answer.
(4) Create a worksheet labelled as ‘Favourites'. Include a table in this worksheet showing how many visitors added Richard's website to their "Favourites" each week. The values should be directly linked to ‘Visitor Data' worksheet. Your table may look like Figure 3.
|
Added as Favourite Link?
|
|
|
Week 1
|
Week 2
|
Week 3
|
Week 4
|
Total
|
|
Added
|
5
|
24
|
45
|
32
|
106
|
|
Not Added
|
0
|
5
|
20
|
14
|
39
|
|
Total
|
5
|
29
|
65
|
46
|
145
|
Figure 3
(5) Create a worksheet and label it as ‘Pivot table and Charts'. Build tables and charts that show the average Visit Duration by Operating system, by Site Connection, by Browser and by State. Provide these by creating 4 pivot tables/charts (one for each Operating System, Site Connection, Browser and State). You will place all 4 of these on the same worksheet. A sample for output for Visit Duration by Operating system is shown in Figure 4.
Figure 4
(6) Richard noted, "I want to use the data on website visitors to develop my monthly budget. I have gathered the following information that I believe can be useful for budget development.
• 60% of the customers who spend more than 3 minutes on my website end up buying 1 drone. 30% of the customers who spend more than 5 minutes buy 2 drones.
• I buy each drone from a drone manufacturer for $420. The selling price for each drone is $600.
• Each drone is packed and delivered separately. Even for the customers who order multiple drones, I deliver each drone separately. Customers are not charged any additional delivery fees as I pay for the delivery. I deliver drones through StartTrack Delivery Services. The delivery costs vary across states. Their chart of delivery fees is listed below."
|
State
|
Delivery Cost
|
|
New South Wales
|
$40
|
|
Queensland
|
$37
|
|
South Australia
|
$55
|
|
Tasmania
|
$60
|
|
Victoria
|
$20
|
|
Western Australia
|
$35
|
|
Australian Capital
|
$42
|
|
Northern Territory
|
$30
|
Create a ‘Budget Summary Report' worksheet. Include a table with a column for the state, estimated quantity, total incomes, total expenses and expected profit. The values for the estimated quantity column should be linked to the ‘Visitor Data' worksheet. Use appropriate functions to fill out the remaining cells of the table. A sample table is shown in figure 5.
|
State
|
Estimated Quantity
|
Total Income
|
Total Expenses
|
Expected Profit
|
|
New South Wales
|
21
|
12,600.00
|
10,395.00
|
2,205.00
|
|
Queensland
|
12
|
7,200.00
|
5,820.00
|
1,380.00
|
|
South Australia
|
10
|
6,000.00
|
5,050.00
|
950.00
|
|
Tasmania
|
13
|
7,800.00
|
6,695.00
|
1,105.00
|
|
Victoria
|
23
|
13,800.00
|
10,810.00
|
2,990.00
|
|
Western Australia
|
12
|
7,200.00
|
5,820.00
|
1,380.00
|
|
Australian Capital Territory
|
7
|
4,200.00
|
3,465.00
|
735.00
|
|
Northern Territory
|
3
|
1,800.00
|
1,455.00
|
345.00
|
|
Total Profit $ 11,090.00
|
Figure 5
Generate a pie chart to show the distribution of expected profits across different states.
(7) Due to the recent economic crisis around the world, you believe that the demand for drones will change in the next month. As several drone sellers in China have shut down their businesses, you believe that the local customers will buy more drones form Australian sellers. However, to secure the attention of these new customers, Richard will need to run an advertisement campaign to promote his brand. The advertisement campaign will cost him $8,000 but you expect that the number of units estimated to sell (in requirement 6) will rise. If he spends on the advertisement in the next month, the number of units will increase for all states as shown below.
|
State
|
Increase in Quantity
|
|
New South Wales
|
5%
|
|
Queensland
|
7%
|
|
South Australia
|
15%
|
|
Tasmania
|
11%
|
|
Victoria
|
18%
|
|
Western Australia
|
12%
|
|
Australian Capital
|
3%
|
|
Northern Territory
|
6%
|
If Richard does not run the advertisement campaign, the number of units will remain unchanged. Do you recommend Richard spends on the advertisement?
(8) Create a worksheet labelled as ‘Analysis and Recommendation'. This worksheet should contain the following three sections.
a. Findings - Briefly summarise your findings based on the analysis performed in requirement 2 to requirement 7.
b. Recommendation for additional analysis - In this section, you should comment on whether additional analyses can be performed using the types of data that Richard currently gets from the web service provider. You should provide two examples of additional analyses that can be performed using the given data and explain how to perform those analyses. Comment on how these additional analyses would help Richard to make business decisions.
c. Recommendation for additional data - Currently there is an option for Richard to get other types of data on his website visitors by paying an additional fee to the web service provider. Provide practical examples of two additional types of data that Richard should collect on his website visitors. Suggest the types of analyses that can be done using these additional data. Comment on how these analyses on additional data would help Richard to make business decisions.
Attachment:- Accounting Information Systems.rar