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...

1 answer below »
CRICOS Provider No. 00103D Page 1 of 5ITECH1100 Understanding the Digital RevolutionAssignment 1 - Fiction BoomerangOverviewFor this individual assignment, you will use skills acquired through practical laboratory exercises toautomate a business process, and to visualize the impact of the automation.You should use Microsoft Excel (or equivalent open-source software) for this assessment task.Important: This assignment specification is generated just for you. Do not distribute this specification.Timelines and ExpectationsPercentage value of task: 20%Due: Sunday, Week 6, 16:00 (See Course Description for date)Learning Outcomes AssessedThe following course learning outcomes are assessed by completing this assessment:A1. Prepare a basic solution to a business problem;A2. Select appropriate IT solutions for business functions;A3. Apply business information software for data visualization and analysis purposes.S1. Write basic programming logic;S3. Interpret and construct representations of business data flow and processes;K8. Outline the basic principles of programming.Assessment DetailsFiction Boomerang is a small second-hand book shop, run by a team of three staff members: Angela,Bertha, and Charlotte.For this assignment, you will complete the following set of tasks using Excel, and build an ePortfolio page todescribe your work.Task 0 - Setting upCreate an ePortfolio page for your assignment. You will submit this page to Moodle as per the lab tasks.You may call it whatever you like, for example ITECH1100 Assignment XXXXXXXXXXHours of operationMost 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; andCharlotte 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 closedentirely on Sundays.CRICOS Provider No. 00103D Page 2 of 5CostsAngela 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 alsoentitled to four weeks of paid annual leave, during which a casual replacement is required at a cost of $33per hour.Fixed costs such as rent and insurance are $2900 per year, and utilities costs are $140 per month.Task 1 - Costs of operating the businessUsing Excel, create a spreadsheet called operating_costs.xlsx that calculates the projected annual outgoingcosts of running Fiction Boomerang.Your spreadsheet should be configured such that the working hours, hourly rates, and fixed and utility costscan be varied easily.Document your findings in your ePortfolio page (approximately 100 words).Obtaining BooksThroughout the day, customers come to Fiction Boomerang to buy books, and occasionally to sell them.When people bring in books to sell to Fiction Boomerang, the staff member on duty will review the booksand make an offer for each one individually.Currently, 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 FictionBoomerang 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 andamount paid for each book in a spreadsheet. This spreadsheet is available for download on Moodle.Task 2 - Sales team offersUsing Excel, process the history of purchases spreadsheet and use appropriate charts to visualize:How the prices paid differed for each staff member; andHow the prices paid have changed over timeDescribe your findings in your ePortfolio (approximately 250 words), and attach the Excel file to your page.Process automationAngela 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 todetermine how to allocate them, including the option of rejecting the book altogether.CRICOS Provider No. 00103D Page 3 of 5The staff have already agreed on how to determine whether a book is terrible, poor, or good.Book purchaseCustomerBook offered DoneEvaluate offerBook shopAssessquality RejectTerribleAssesspublication dateOfferlow pricePoorGoodOlder than15 yearsOffer mediumpriceCheck ishardcover15 yearsor newerPaperbackOffer high priceHardcoverTask 3 - AutomationUsing Excel, create a spreadsheet page that automates the above process, allowing a member of the teamto 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 theFiction 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.Task 4 - Price settingUsing Excel and the historical data you have available, determine sets of prices which meet the objectivesof the following scenarios:Scenario one: Total offer prices are approximately the same as for the historical dataScenario two: Assuming all books purchased are sold, allow all staff to draw the same wage asCharlotte.CRICOS Provider No. 00103D Page 4 of 5Describe the strategy you used to find appropriate prices for each scenario (approximately 150 words).Ensure that the Low price is less than the Medium price, and both are less than the High price.Bonus challenge task (optional!)Disclaimer: This task is 100% optional, and you can receive full marks without attempting or completing it.It is intended to be a challenge if you are interested in such things, and the marks available do not reflectthe significant research and effort required to implement it correctly. Tutors will not provide significantassistance for this challenge task.Optional task 5Reimplement Task 1 and Task 3 using either Python or HTML+JavaScript.Write a brief overview of how to run your solution, and attach a zip file containing your code to yourePortfolio page.There are no partial marks awarded for this bonus task - you must complete all features to attain thebonus marks.It is possible to attain full marks for this assignment without completing this challenge task.Further detailsAssignment supportThis assignment is supported by the first 6 lectures and labs. Work on the assignment should be spreadover several weeks after the relevant lab has been mastered.SubmissionAll files should be submitted to Moodle by the due date and time. Check with your tutor as to whether ahard copy is required in addition to the electronic submission.Marking Criteria/RubricRefer to the attached marking guide.FeedbackFeedback will be supplied through Moodle. Authoritative marks will be published through fdlMarksPlagiarismPlagiarism is the presentation of the expressed thought or work of another person as though it is one's ownwithout properly acknowledging that person. You must not allow other students to copy your work and musttake care to safeguard against this happening. More information about the plagiarism policy and procedurefor the university can be found athttp://federation.edu.au/students/learning-and-study/online-help-with/plagiarism.CRICOS Provider No. 00103D Page 5 of 5Marking Guide: Assignment 1Feature Criteria Maximum ObtainedOperating costsAppropriate projection calculation 2Configurable hours, rates, fixed annual and monthlycosts 2Description of findings 1Uses hand-calculated values (-1)Sales team offersVisualization of prices offered per staff member 2Visualization of change in prices over time 2Description of findings 1AutomationSpreadsheet accurately models the BPMN process 4Description of automation 1Unclear instructions (-1)Price settingAppropriate Low, Medium and High prices for scenario1 2Appropriate Low, Medium and High prices for scenario2 2Description of strategy 1Bonus optionalchallenge taskFaithful reimplementation of tasks 1 and 3 usingPython or HTML+JavaScript (+2)Total: 20
Answered Same Day May 03, 2020 ITECH1100

Solution

Abr Writing answered on May 06 2020
138 Votes
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
Task 1 - Costs of operating the business
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.
Document your findings in your ePortfolio page (approximately 100 words).
        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.
Task 3 - Automation
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.
Task 4 - Price setting
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
Bonus challenge task (optional!)
Optional task 5
Reimplement Task 1 and Task 3 using either Python or HTML+JavaScript.
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

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here