ITECH1100 Literature Life: Business Automation & Data Analysis

This project focuses on automating business processes at Literature Life, a second-hand book shop, using Microsoft Excel. It involves analyzing current operations, including staff scheduling and book pricing. The project calculates outgoing costs, analyzes book price data using histograms, and proposes an automation process for book valuation based on publication date, hardcover status, and condition. An Excel simulation is developed to standardize pricing, and different scenarios are explored to optimize sales and manage costs, aiming to improve efficiency and consistency in the bookshop's operations. Desklib provides access to similar projects and study resources for students.

Running head: LITERATURE LIFE
Assignment 1 – Literature Life
Name of the Student
Name of the University
Author Note

1LITERATURE LIFE
Task 0:
In the book shop Literature Life three staff members namely Audrey, Bhavneet and
Claudia works in different days and in different times. Audrey works from 9.00 AM to 12.30
PM in Monday, Tuesday and Wednesday. Bhavneet works from 9:00 AM to 12:30 PM in
Thursday, Friday and Saturday. While, Claudia works from 12.30 Pm to 5.30 PM every day
from Monday to Friday. The shop is open each day from 9:00 AM to 5:30 PM except on
Saturday it is open for halftime i.e. from 12.30 PM to 5.30 PM. Literature Life is entirely
closed on Sunday.
The shift timing and days are arranged in the following table.
Name of
the staff
Working days Number of
working
days
Working
hours in a
day
Total Working
time in week(in
hrs)
Total
working
time in a
year
Audrey Monday, Tuesday and
Wednesday
3 3.5 10.5 546
Bhavnee
t
Thursday, Friday and
Saturday
3 3.5 10.5 546
Claudia Monday to Friday 5 5 25 1300
Task 1:
Now, the projected annual outgoing cost is calculated in excel based on the hourly rates of
the employees and their total working hours in a year. Audrey and Bhavneet do not take any
salary from the earnings of the shop as they are semi-retired but Claudia charges $22 for each
hour of working in the shop. Moreover, she enjoys a paid leave for 4 months. In the 4 month

2LITERATURE LIFE
the individual for her replacement who plays her role in the same shift and in the same days
charges $33 per hour. It is assumed that a year has approximately 52 weeks. Additionally, the
rents and insurance cost which are fixed is $2900 per year and the cost of utilities is $140 per
month.
Name hourly
rate(USD)
total working hours
in a week(in hrs)
total cost in a
week(USD)
total
annual
cost(USD)
Audrey 0 10.5 0 0
Bhavneet 0 10.5 0 0
Claudia 22 25 550 28600
Casual replacement
individual
33 25 825 3300
Total 31900
Fixed Costs Annual
costs(USD)
Rents and Insurance 2900
Utilities costs 1680
Projected annual
Outgoing cost
36480
So, the projected annual outgoing cost of running Literature Life is $36480.
Task 2: Sales team offers

3LITERATURE LIFE
The price paid is different based on the staff members for same quality of books. Hence, 3
frequency distribution tables are given for good, poor and terrible quality of books.
Offered price range for good quality(in
$)
Frequenc
y
0 0
0 to 2 14
2 to 4 20
4 to 6 50
6 to 8 42
8 to 10 17
10 to 12 1
12 to 14 1
Histogram of price of good quality books accepted by different staff members:

4LITERATURE LIFE
0 0 to 2 2 to 4 4 to 6 6 to 8 8 to 10 10 to 12 12 to 140
14
20
50
42
17
1 1
Histogram
Frequency
Offered price range for good quality(in $)
Frequency
Offered price range for poor quality(in
$)
Frequenc
y
0 0
0 to 0.5 2
0.5 to 1 45
1 to 1.5 74
1.5 to 2 24
2 to 2.5 11
2.5 to 3 2
Histogram of price of poor quality books accepted by different staff members:

5LITERATURE LIFE
0 0 to 0.5 0.5 to 1 1 to 1.5 1.5 to 2 2 to 2.5 2.5 to 30 2
45
74
24
11
2
Histogram
Frequency
Offered price range for poor quality(in $)
Frequency
Offered price
range for
terrible
quality(in $)
Frequency
0 4
0 to 0.2 130
0.2 to 0.4 10
0.4 to 0.6 29
0.6 to 0.8 0
0.8 to 1 4

6LITERATURE LIFE
Histogram of price of terrible quality books accepted by different staff members:
0 0 to 0.2 0.2 to 0.4 0.4 to 0.6 0.6 to 0.8 0.8 to 1
4
130
10
29
0 4
Histogram
Frequency
Offered price range for terrible quality(in $)
Frequency
From the three histograms it is quite evident that maximum staffs offered between $4-$6 for
good quality of books, $1-$1.5 for poor quality books and in between $0-$0.2 for books with
terrible quality.
Now, the price offered to books as changed over time or date of issue is shown in the
clustered column below.

7LITERATURE LIFE
2017-05-08 09:27 2017-05-23 13:49 2017-06-08 11:23 2017-06-20 12:13 2017-07-03 13:10 2017-07-17 15:27
0
2
4
6
8
10
12
14
Offered book price($) vs date of issue
Task 3:
Now, an automation process is proposed by Audrey and Bhavneet to improve the consistency
of the payment process of the books and making the process unbiased with respect to the staff
members. The books will be evaluated on three basis namely book publication date, book
hardcover status and condition of the book. The automation process algorithm is shown in the
process flow diagram below.
The process flow diagram of the Automation:

8LITERATURE LIFE
Now, an excel simulation of the total automation process is developed where any staff
member will be able to put their chosen price and choose the publication status, book
condition and the book hardcover status and the price of the book will be automatically
evaluated. A sample of simulation with $40, $80 and $120 as the low, medium and high price
of the book is given below with some random book statuses.

9LITERATURE LIFE
Excel screenshot:
Book Quality Hardcover of the book Book Publication date Price offered to the book Enter offered Price in($)
Terrible Yes older than 15 years Low Price $40.00
Poor Paperback 15 years or newer Medium Price $80.00
Good High Price $120.00
Rejected $0.00
Quality Good
Hardcover Yes
Publication date 15 years or newer
Evaluated offer of the book $120.00
Task 4 - Price setting
Case of Scenario 1: The total offered prices for 480 books in the historical data is $1116.9.
Hence, to meet the total sales of $1116.9 with exact number of books sold in high, medium
and low price, the prices in the automation model should be $0.28, $1.44 and $5.79 for low,
medium and high respectively.
Case of Scenario 2: The total cost of Claudia in year is $28600. Hence, if all of the three staff
members charges the same wage as Claudia then the total wage will be 3*28600 = $85800.
The casual replacement costs and the fixed cost with utilities cost will be the same as before.
Hence, the total cost in a year will be $85800 + $3300 + $2900 + $1680 = $93680.
Now, a book sold in twice the rate at which it is purchased. Hence, the selling prices of low,
medium and high prices is $0.28*2 = $0.56, $1.44*2 = $2.88 and $5.79*2 = $11.58
respectively.

10LITERATURE LIFE
Now, definitely to meet this annual cost the number of books sold in low, medium and high
price is needed to be higher than the historical year. The exact number of books that are
needed to be sold can be found by using the mathematical optimization process with known
constraints.