Great Deal! Get Instant \$10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

# CRICOS Provider No. 00103D Page 1 of 5ITECH1100 Understanding the Digital RevolutionAssignment 1 - Fiction BoomerangOverviewFor this individual assignment, you will use skills acquired through...

Answered Same Day May 03, 2020 ITECH1100

## Solution

Abr Writing answered on May 06 2020
Fiction Boomerang.docx
Hours of operation
Most of the time, each team member works separate shifts:
Â· Angela works 9:00 AM to 12:30 PM, Monday, Tuesday and Wednesday;
Â· Bertha works 9:00 AM to 12:30 PM, Thursday, Friday and Saturday; and
Â· Charlotte works 12:30 PM to 5:30 PM, Monday to Friday.
The shop runs from 9:00 AM to 5:30 PM each day, except Saturday when it closes at 12:30. It is closed entirely on Sundays.
Day
Angela
Bertha
Charlotte

To
From
Duration
To
From
Duration
To
From
Duration
Monday
9:00 AM
12:30 PM
3.5
0
0
0
12:30 PM
5:30 PM
5
Tuesday
9:00 AM
12:30 PM
3.5
0
0
0
12:30 PM
5:30 PM
5
Wednesday
9:00 AM
12:30 PM
3.5
0
0
0
12:30 PM
5:30 PM
5
Thursday
0
0
0
9:00 AM
12:30 PM
3.5
12:30 PM
5:30 PM
5
Friday
0
0
0
9:00 AM
12:30 PM
3.5
12:30 PM
5:30 PM
5
Saturday
0
0
0
9:00 AM
12:30 PM
3.5
0
0
0
Costs
Angela and Bertha are the semi-retired co-owners of the bookshop, and do not take a salary.
Charlotte, however, is a part-time employee, with total employment costs of \$22 per hour. Charlotte is also entitled to four weeks of paid annual leave, during which a casual replacement is required at a cost of \$33 per hour.
Fixed costs such as rent and insurance are \$2900 per year, and utilities costs are \$140 per month.
Â
Working hours in Week
Working Weeks in Yea
Salary
Angela
10.5
52
0
Bertha
10.5
52
0
Charlotte
25
48
22
Replacement
25
4
33
Using Excel, create a spreadsheet called operating_costs.xlsx that calculates the projected annual outgoing costs of running Fiction Boomerang.
Your spreadsheet should be configured such that the working hours, hourly rates, and fixed and utility costs can be varied easily.
Yearly Utilities Cost
1680
Yearly Salary
29700
Rent and Insurance
2900
Total Cost
34280
The spreadsheet, operating_costs.xlsx, is configured in such a way that the working hours, hourly rates, and fixed and utility costs can be varied easily.
The total operational cost for running the small second-hand book shop, Fiction Boomerang for an year is \$34,280. The maximum spending is giving the salary to the staff. The co-owners, Angela and Bertha, donâ€™t charge a salary but Charlotte and here replacement do. The shop is spending \$29,700 per year on her and her replacement salary.
Obtaining Books
Throughout the day, customers come to Fiction Boomerang to buy books, and occasionally to sell them.
When people
ing in books to sell to Fiction Boomerang, the staff member on duty will review the books and make an offer for each one individually.
Cu
ently, the process for determining how much the book shop will pay for a book is entirely subjective. Staff members offer an amount per book based on the quality of the book, and how popular they think it is, and how they are feeling at the time.
The sale amount is always simply double the amount paid and is set at the same time - if Fiction
Boomerang pays \$3.00 for a book, they will put it on sale with a marked price of \$6.00.
(Fiction Boomerang does not deal in rare or antique books)
For several months, staff have been keeping track of the date and time, quality, publication year and amount paid for each book in a spreadsheet. This spreadsheet is available for download on Moodle.
Task 2 - Sales team offers
Using Excel, process the history of purchases spreadsheet and use appropriate charts to visualize:
Â· How the prices paid differed for each staff member; and
The prices paid by staff member for different books can be seen above. All the six possibilities of all the book condition and type can be seen in the chart above. Good condition book represented by blue color were paid the highest price. The downward slope of the three curves shows that pape
ack books were paid less price than books with hardcover.
Â· How the prices paid have changed over time
The above line chart shows the variation of prices offered to books over time.
Process automation
Angela and Bertha want to improve the consistency of how they pay for and price books.
They'd like to standardize on three standard price offers, and have designed the following process to determine how to allocate them, including the option of rejecting the book altogether.
The staff have already agreed on how to determine whether a book is te
ible, poor, or good.
Using Excel, create a spreadsheet page that automates the above process, allowing a member of the team to enter whether the book is hardcover, its publication year, and its condition, and receive a price to offer.
Ensure that you include enough text and formatting to make your spreadsheet usable by a member of the Fiction Boomerang team, or by a University lecturer.
Your spreadsheet should be configured such that the Low, Medium and High prices can be varied easily.
Describe how you automated the process (approx 150 words), and attach your Excel file to your ePortfolio.
Price
Low
Medium
High

2
5
13
Condition
Good
Poo
Te
ible
Publication Date
Old
New
Type
Pape
ack
Hardcove
The automation was done using If, AND commands in EXCEL. Please check the Automation sheet for more detals.
Using Excel and the historical data you have available, determine sets of prices which meet the objectives of the following scenarios:
Scenario one: Total offer prices are approximately the same as for the historical data
When low price was set to \$2, medium price to \$5 and high price to \$12, the results are as follows:

Offer (\$)
Price Setting (\$)
Mean
2.617083333
2.63125
Standard E
o
0.128056574
0.15947967
Median
1.5
2
Mode
0.2
2
Standard Deviation
2.805578962
3.494024499
Sample Variance
7.871273312
12.2082072
Kurtosis
0.996642951
2.777434999
Skewness
1.304724632
1.982707788
Range
13
12
Minimum
0
0
Maximum
13
12
Sum
1256.2
1263
Count
480
480
Scenario two: Assuming all books purchased are sold, allow all staff to draw the same wage as
Charlotte.
Â
Working hours in Week
Working Weeks in Yea
Salary
Angela
10.5
52
22
Bertha
10.5
52
22
Charlotte
25
48
22
Replacement
25
4
33
Yearly Utilities Cost
1680
Yearly Salary
53724
Rent and Insurance
2900
Total Cost
58304
Write a
ief overview of how to run your solution, and attach a zip file containing your code to your ePortfolio page.
operating_costs.xlsx
Sheet1
Day        Angela                        Bertha                        Charlotte
To        From        Duration        To        From        Duration        To        From        Duration
Monday        9:00 AM        12:30 PM        3.5        0        0        0        12:30 PM        5:30 PM        5
Tuesday        9:00 AM        12:30 PM        3.5        0        0        0        12:30 PM        5:30 PM        5
Wednesday        9:00 AM        12:30 PM        3.5        0        0        0        12:30 PM        5:30 PM        5
Thursday        0        0        0        9:00 AM        12:30 PM        3.5        12:30 PM        5:30 PM        5
Friday        0        0        0        9:00 AM        12:30 PM        3.5        12:30 PM        5:30 PM        5
Saturday        0        0        0        9:00 AM        12:30 PM        3.5        0        0        0
Working hours in Week        Working Weeks in Year        Salary
Angela        10.5        52        0
Bertha        10.5        52        0
Charlotte        25        48        22
Replacement        25        4        33
Yearly Utilities Cost        1680
Yearly Salary        29700
Rent and Insurance        2900
Total Cost        34280
30255.xlsx
Data
Price        Low        Medium        High                Condition                                Publication Date                        Type
2        5        12                Good        Poor        Te
ible                Old        New                PaperBack        Hardcove
Date        Condition        Published        Type        New/Old        Offer (\$)        Price Setting (\$)                        Average of Offer (\$)        Column Labels
2017-03-31 09:25        Poor        1961        Pape
ack        Old        1.2        2                        Row Labels        Good        Poor        Te
ible        Grand Total
2017-03-31 09:25        Good        2009        Pape
ack        New        5.5        5                        Hardcover        7.4681318681        2.0977777778        1        5.5571428571
2017-03-31 09:50        Te
ible        2014        Pape
ack        New        0.2        0                        Pape
ack        4.0913043478        1.230952381        0.2813793103        1.4064705882
2017-03-31 10:14        Good        1987        Pape
ack        Old        3.5        2                        Grand Total        6.011875        1.4590643275        0.3006711409        2.6170833333
2017-03-31 10:37        Te
ible        1989        Pape
ack        Old        0.2        0
2017-03-31 10:52        Good        1985        Pape
ack        Old        4.5        2
2017-03-31 13:34        Poor        1982        Hardcover        Old        2        2
2017-03-31 14:50        Good        2003        Hardcover        New        8.5        12
2017-04-01 09:18        Te
ible        2001        Pape
ack        Old        0.2        0
2017-04-01 12:09        Good        2001        Pape
ack        Old        3.5        2
2017-04-03 09:12        Te
ible        1989        Pape
ack        Old        0.4        0
2017-04-03 10:15        Poor        2009        Hardcover        New        2        2
2017-04-03 11:06        Good        1981        Pape
ack        Old        2.5        2
2017-04-03 14:48        Good        1993        Pape
ack        Old        4.5        2
2017-04-03 15:01        Good        2007        Hardcover        New        6.5        12
2017-04-03 15:57        Good        2002        Hardcover        Old        6        2
2017-04-03 16:46        Good        2011        Pape
ack        New        5.5        5
2017-04-03 16:49        Poor        2013        Pape
ack        New        1        2
2017-04-03 17:28        Poor        2009        Pape
ack        New        1.5        2
2017-04-04 09:58        Good        1996        Hardcover        Old        6        2
2017-04-04 12:22        Te
ible        1996        Pape
ack        Old        0        0
2017-04-04 13:51        Poor        2014        Pape
ack        New        1.5        2
2017-04-04 14:20        Good        2013        Hardcover        New        6        12
2017-04-04 14:52        Good        2001        Pape
ack        Old        5.5        2
2017-04-04 15:03        Good        1969        Pape
ack        Old        5.5        2
2017-04-04 16:47        Good        2009        Hardcover        New        6        12
2017-04-04 17:23        Poor        2001        Pape
ack        Old        1.5        2
2017-04-05 09:57        Te
ible        1997        Pape
ack        Old        0.5        0
2017-04-05 10:44        Poor        1993        Pape
ack        Old        1.5        2
2017-04-05 10:50        Te
ible        2001        Pape
ack        Old        0        0
2017-04-05 13:46        Good        1996        Pape
ack        Old        5.5        2
2017-04-05 14:14        Poor        2011        Pape
ack        New        1.5        2
2017-04-05 14:18        Poor        2001        Pape
ack        Old        1.5        2
2017-04-05 15:06        Good        1997        Pape
ack        Old        5        2
2017-04-06 11:56        Good        2003        Pape
ack        New        1.8        5
2017-04-06 13:23        Te
ible        2001        Pape
ack        Old        0.2        0
2017-04-06 13:37        Poor        1993        Pape
ack        Old        1.5        2                                Offer (\$)        Price Setting (\$)
2017-04-06 15:50        Te
ible        2002        Pape
ack        Old        0.2        0                        Mean        2.6170833333        2.63125
2017-04-06 16:19        Te
ible        1961        Pape
ack        Old        0.2        0                        Standard E
or        0.1280565737        0.1594796696
2017-04-07 09:59        Te
ible        1996        Pape
ack        Old        0.2        0                        Median        1.5        2
2017-04-07 11:53        Te
ible        2011        Pape
ack        New        0.2        0                        Mode        0.2        2
2017-04-07 12:15        Good        2001        Hardcover        Old        9        2                        Standard Deviation        2.8055789621        3.4940244994
2017-04-07 12:52        Te
ible        1968        Pape
ack        Old        0.2        0                        Sample Variance        7.8712733125        12.2082072025
2017-04-08 09:12        Good        2014        Pape
ack        New        4.5        5                        Kurtosis        0.9966429511        2.7774349986
2017-04-08 09:35        Good        2009        Hardcover        New        8.5        12                        Skewness        1.3047246322        1.9827077885
2017-04-08 10:31        Good        2008        Hardcover        New        8.5        12                        Range        13        12
2017-04-10 11:58        Good        2005        Hardcover        New        5.8        12                        Minimum        0        0
2017-04-10 12:14        Good        2002        Hardcover        Old        6.6        2                        Maximum        13        12
2017-04-10 12:18        Poor        2008        Pape
ack        New        1        2                        Sum        1256.2        1263
2017-04-10 13:11        Good        1981        Pape
ack        Old        5.5        2                        Count        480        480
2017-04-10 13:22        Good        1985        Pape
ack        Old        4        2
2017-04-10 15:36        Poor        2005        Pape
ack        New        1.5        2
2017-04-10 16:17        Te
ible        1969        Pape
ack        Old        0.2        0
2017-04-11 11:24        Te
ible        2009        Pape
ack        New        0.4        0
2017-04-11 14:16        Poor        2014        Pape
ack        New        1.5        2
2017-04-11 14:44        Te
ible        1999        Pape
ack        Old        0.2        0
2017-04-12 09:15        Poor        1997        Pape
ack        Old        1.5        2
2017-04-12 09:21        Poor        2007        Hardcover        New        2.6        2
2017-04-12 11:00        Te
ible        1993        Pape
ack        Old        0.8        0
2017-04-12 11:29        Poor        1992        Hardcover        Old        1.8        2
2017-04-12 15:40        Poor        1975        Pape
ack        Old        1.5        2
2017-04-13 11:00        Good        1969        Hardcover        Old        6        2
2017-04-13 16:53        Poor        1975        Pape
ack        Old        1.5        2
2017-04-14 09:41        Te
ible        1996        Pape
ack        Old        0.2        0
2017-04-14 09:54        Good        1987        Pape
ack        Old        3.5        2
2017-04-14 11:10        Te
ible        2013        Pape
ack        New        0.2        0
2017-04-14 11:13        Te
ible        2011        Pape
ack        New        0.2        0
2017-04-14 12:02        Te
ible        1993        Pape
ack        Old        0.2        0
2017-04-14 16:38        Te
ible        2014        Pape
ack        New        0.2        0
2017-04-17 10:24        Good        2009        Pape
ack        New        4.5        5
2017-04-17 10:30        Good        1987        Pape
ack        Old        4.6        2
2017-04-17 10:53        Good        1953        Pape
ack        Old        2        2
2017-04-17 11:10        Poor        2012        Hardcover        New        2.2        2
2017-04-17 11:51        Poor        1969        Hardcover        Old        1.6        2
2017-04-17 12:15        Poor        1968        Pape
ack        Old        1.4        2
2017-04-17 12:21        Te
ible        2011        Pape
ack        New        0.8        0
2017-04-17 12:45        Good        2015        Pape
ack        New        5.5        5
2017-04-17 14:44        Poor        2005        Pape
ack        New        1.5        2
2017-04-18 09:19        Good        1985        Pape
ack        Old        3.4        2
2017-04-18 09:24        Good        2010        Pape
ack        New        4.5        5
2017-04-18 10:59        Poor        2014        Pape
ack        New        1        2
2017-04-18 15:20        Poor        2016        Pape
ack        New        1.5        2
2017-04-18 16:54        Te
ible        2009        Pape
ack        New        0.2        0
2017-04-19 10:39        Te
ible        1985        Pape
ack        Old        0.4        0
2017-04-19 11:59        Good        2007        Pape
ack        New        4        5
2017-04-19 12:48        Poor        1987        Pape
ack        Old        1        2
2017-04-19 14:41        Poor        1977        Pape
ack        Old        1.5        2
2017-04-19 14:55        Te
ible        2007        Pape
ack        New        0.2        0
2017-04-19 15:13        Good        2003        Pape
ack        New        5        5
2017-04-20 09:59        Good        2010        Hardcover        New        7        12
2017-04-20 13:20        Good        2003        Pape
ack        New        5.5        5
2017-04-20 15:01        Poor        2008        Pape
ack        New        1.5        2
2017-04-20 16:37        Poor        1975        Pape
ack        Old        1.5        2
2017-04-20 16:44        Poor        2005        Pape
ack        New        1.5        2
2017-04-20 17:18        Poor        1993        Pape
ack        Old        1.5        2
2017-04-21 09:25        Good        2001        Pape
ack        Old        2        2
2017-04-21 09:26        Good        1987        Pape
ack        Old        2.5        2
2017-04-21 10:11        Poor        1981        Pape
ack        Old        0.6        2
2017-04-21 12:23        Good        2009        Hardcover        New        12        12
2017-04-21...
SOLUTION.PDF