Information System Project Case Study
Geo and Sally’s Flower Shoppe
The following is a case study to describe the data needs of a small company. The
descriptive material includes:
Background
Business Processes
Personnel
Forms and Documents
Interviews
Background
Geo and Sally’s Flower Shoppe began 10 years ago as a small local flower shop by two
friends who loved creating beautiful flower arrangements for a reasonable price. Over
time, Geo and Sally’s customer base began to expand as local customers told friends
and family members around the country about the wonderful arrangements Geo and
Sally could create for a very reasonable price.
Besides flower arrangements, customers were interested in purchasing the unique pots
they used for the arrangements, floral and garden accessories, garden flowers,
vegetable plants, bushes, and even smaller trees. In addition to family customers, the
business has expanded to include several small and even a few larger businesses in
the local area. Through all of the expansion, flower arrangements remain the mainstay
and primary profit center for the business. Geo’s brother Sammy suggested Geo and
Sally set up an Internet storefront to sell more flower arrangements, which they did last
year. They now find themselves owners of an ever-expanding Internet business with a
loyal local customer base at their shop in Denver, Colorado.
The shop itself has grown from the initial 500 square foot modified living room of an old
home Geo used to live in to a new 6,000 square foot facility the company moved into
two years ago. It includes a 1000 square foot showroom where they can display a
variety of unique flower arrangements and containers. Arrangements in the showroom
that do not sell within three days must be replaced so only the best items are seen and
purchased by the large walk-in customer base.
Geo and Sally’s information and data storage needs have exceeded the capacity of the
current system. Sammy told Geo that he and Sally need to hire a consulting group to
help them to implement a modern database system for accounting, payroll, inventory
control, and sales.
© 2015 Laureate Education, Inc.
Page 1 of 11
Business Processes
#
1 Selling
Process
2 Operations (planting, growing,
arranging flowers, and other items)
3 Materials ordering, receiving, and
stocking
4 Inventory control
5
6
7
8
Daily balancing
Payroll and Human Resources
Accounts receivable
Accounts payable
Main Tasks
Tracking prospects, recording sales,
order confirmations, invoicing, etc.
Ordering, recording, and tracking work
progress
Controlling operating materials
Accepting, recording, tracking
consumption, and costs
Reconciling credits and debits
Tracking and paying employees
Tracking moneys owed the company
Tracking what the company owes
creditors and vendors
Personnel (position: name – process responsibility)
1.
2.
3.
4.
5.
6.
7.
Owners: Geo and Sally – all
Sales Manager: Sammy – 1, 5
Salespersons: Saria, Rasta, Danny, Tiffany, and John (Internet sales only)
Bookkeeper: Sasha – 5, 6, 7, 8
Operations Manager: Dylan – 2, 3, 4, 6
Workers: Jeremiah, Sharon, Mariah, Shelly, and Linda
Purchasing Manager: Donn – 2, 3, 4
Forms and Documents
(Figures 1 through 13 represent skeletal data descriptions—input and output—of the
manual system.)
1. Customer Invoice
2. Commission Statement
3. Vendor Data Card
4. Vendor Invoice
5. Inventory Report
6. Purchase Order
7. Daily Sales Summary
8. Time Card
9. Payroll Check and Summary Form
10. Federal 941 Payroll Tax Withholding Summary
11. General Ledger
12. Bank Deposit Record
13. Cash Register Ticket
© 2015 Laureate Education, Inc.
Page 2 of 11
Figure 1. Customer Invoice
Geo and Sally’s Flower Shoppe
Date: _____________
Invoice number: 99999
Item
Description
Customer
Address
City
□ Cash
Stock number
□ Visa
Quantity
Salesperson: ________________________________________
□ MC
Price
Ext.
Price
Subtotal
Sales
tax
Total
Figure 2. Commission Statement
COMMISSION STATEMENT
Salesperson: __________________ Reporting Date: _____________
Sales date
Invoice
Retail Total
Commission
Totals
© 2015 Laureate Education, Inc.
Page 3 of 11
Figure 3. Vendor Data Card
Larry’s Garden Products
234 Main Street
Boulder, CO 82322
Product Line: Garden Products
Vendor ID: 88888
Customer Service Contact: Rick James
Telephone: 333-455-4544
Figure 4. Vendor Invoice
Vendor Name
Customer ID: ___________
Shipping Date: _________
Sold to:
Item Description
Quantity
Units
Order Date:
PO Number:
Price
Ext Price
Sub-total
Shipping
Total
Figure 5. Inventory Report
Inventory Report for Period Ending: ____________
Starting
Ending
Item
Description
Sales
Thresholds
Inventory
Inventory
© 2015 Laureate Education, Inc.
Order
Page 4 of 11
Figure 6. Purchase Order
Geo and Sally’s Flower
Shoppe
123 Main Street
Denver, CO 80221
Supplier ID: ____________
Federal Tax ID: 36-39393939
Item Description
Quantity
Ship by:
Comment:
Authorized by: ________________________________
Figure 7. Daily Sales Summary
Manager name:
Cash register amount at start of day
Number cash sales actions
Number credit card sales
actions
Number check sales actions
Total sales actions
Overage/shortage
Comments:
© 2015 Laureate Education, Inc.
PO Number 22222
Order Date:
Shipping
Date:
Terms
Net 30
CO Tax ID: 404-393939
Units Price
Amount
Subtotal
Discount
%
Tax
Shipping
Total
Date:
Cash sales
Credit card
sales
Check sales
Total sales
Total amount refunded
Balance
Page 5 of 11
Figure 8. Time Card
Name:
ID:
Date Time in Time Out Hours
Total Hours:
Figure 9. Payroll Check and Summary Form
Check no: 9999
Date: ____________
Geo and Sally’s Flower Shoppe
Name:
SSN
Pay to the order of:
______________________________
$ __________._____
Gross
Pay
FICA
Amount: _________________________ Dollars
FITA
Payer name: _____________________________
© 2015 Laureate Education, Inc.
Current Year to
Date
Med
State
Net Pay
Page 6 of 11
Figure 10. Federal 941 Payroll Tax Withholding Summary
Federal 941
1st Qtr.
Monthly
Month
FITA
FICA
Med
Deposits
1
781.06
426.03
99.64
1,832.40
Gross Pay
6,871.42
2
753.83
395.77
92.56
1,730.50
6,383.37
3
740.18
386.33
90.35
1,693.54
6,231.13
2,275.07
1,208.13
282.55
5,256.44
19,485.92
Grand
Total
2
Total wages and
19,485.92
3
Total income tax
2,275.07
5
Adjusted total income tax
2,275.07
6a
Taxable SS
wages
19,485.92
x 12.4 %
6 b 2,416.25
7a
Taxable Med
wages
19,485.92
x 2.9 %
7 b 565.09
8
Total social security and Medicare taxes (add
lines 6b and 7b)
10
Adjusted total of Social Security and Medicare 2,981.35
11
Total taxes (add lines 5 and 10)
5,256.42
14
Total deposits for the quarter ending:
_____________________
See
above
15
Balance
17
a
17
b
2,981.35
0
17
c
17
d
Note: for block 17, take data from first, second and third month data at top of this
report
Colorado State Withholding Tax
Gross Pay
$19,485.92 State taxes withheld:
© 2015 Laureate Education, Inc.
$1,056.47
Page 7 of 11
Figure 11. General Ledger
Geo and Sally’s Shoppe
General Ledger Date: ______________________
Code Description
Balance brought forward (date) ______________
Credit
Debit
Net
Figure 12. Bank Deposit Record
Bank deposit record
Serial no. 1234
Account: 04393884
Date of deposit: __________________
Total of checks
deposited
Note: It is company policy to deposit all receipts
Total cash
daily.
deposited
Total deposited
Comment:
© 2015 Laureate Education, Inc.
Page 8 of 11
Figure 13. Cash Register Ticket
Geo and Sally’s Flower
Shoppe
Register no: 43233
Date: ___________
Item Type
Amount
1
2
3
4
5
Sub-tot
Tax
Total
© 2015 Laureate Education, Inc.
Page 9 of 11
Interviews
Sally (Co-owner and CEO) – “Geo and I started the business 10 years ago. Most of the
time, I was able to keep track of accounts but, in the last few years, our business has
grown and I cannot manage it anymore the way I used to. I think all of our employees
are honest and hard workers but lately I have been unable to track inventory and
customers. Sasha is having greater difficulty with billing and paying bills, because of the
volume we now have and I cannot afford to hire more accounting staff. We tried that
and found the paperwork just got more complex. I think everybody is honest, but
keeping track of inventory and costs creates a lot of work. Besides, it is not very
effective. I really need a system that can tell what we have on hand, what we need to
order, and which are our best customers. I really need to provide better service to our
customers. I need a system that would allow me to target sales to the highest-volume
customers and be able to select the types of merchandise they want. Now that I have so
many salesmen, I cannot keep track of what the customers are buying like I could when
it was just Geo and I running the company. I also need accurate reports of inventory,
what types of items we have in stock, what flowers, plants, garden products, and
containers the wholesale and retail values, and so on. There are excessive paper logs
to maintain, and it takes a lot of time to retrieve records. It is also very challenging to
communicate and collaborate with various company personnel about matters such as
sales, balancing, etc. The trade is now so competitive that I really have to watch my
costs, and that means controlling inventory and being very smart about what I order for
inventory. What can you do for me?”
Sammy (Sales Manager) – “I really like working for Geo and Sally, but I just cannot
provide the type of service to our customers like they say they used to when they were
selling flowers in the original shop. The volume is just too much and we cannot expect
all the salesmen to know the personal tastes of every customer, but that is what we
need to provide the service they want. We really need to know which customers like
what, when new inventory will arrive, and what has already arrived but is not in the
showroom. And, the paperwork is getting worse. We really need better data. A key
issue we have to address is when to sell on credit. A few customers are, at times,
overextended with their accounts, and we really should not sell to them on credit. We
need to know what their outstanding balances are at the time they order or come into
the shop. We need an efficient way in which each salesman can store customer
information that can be easily, yet securely, accessed by other sales personnel.
“I need detailed information about what our salesmen are doing so that I can help them
focus on the right merchandise with the correct customers, and frankly, I need to know
which salesmen are just lazy.
“The way we do it now, we have to fill out invoices, take manual inventory and total cash
receipts, balance the cash register, and count the cash, and so on. There is a lot of
work that has to be done at the end of every day after we close the shop. I would like to
© 2015 Laureate Education, Inc.
Page 10 of 11
be able to just count the cash and know what the total deposits should be; every time
there is a mistake, we have to start over.”
Sasha (Bookkeeper) – “Well, as you know, this is a growing company and I just cannot
handle the paperwork like I used to. It is just too time consuming to post daily ledgers,
reconcile daily sales reports and cash receipts and deposits. I need a way to automate
the data and be able to hold the people responsible who actually handle the money and
inventory. I also need to merge the store and Internet sales information, which can then
be easily accessed by any other authorized personnel. Some salespeople are on
commission, but the senior employees are salaried. I would like to just print the checks
and do the audits on a regular schedule, rather than having to manually process
invoices and time cards to figure out who should get paid what and how much tax to
deduct. Just the tax numbers for sales and payroll are a lot of work.”
Dylan (Operations Manager) – “My problem is just keeping track of what work orders
have been received, how much has been completed on each flower arrangement, which
arrangements in the showroom need to be rotated out, who is doing the work, and when
it will be completed. It is too much work for one person and it slows down work when
each worker has to keep all kinds of records. The records are never accurate or worth
trying to use. I need a way to know exactly what is going on at all times so I can more
efficiently assign work to my employees. It would be very beneficial if I can somehow
share information with other personnel, view their logs, and exchange information more
efficiently.”
Donn (Purchasing) – “I have a lot of vendors, and we stock a lot of different flower
types, plants, garden products, and containers. I need to know exactly how much I have
in stock, the usage rates, and what materials will be needed for pending orders, so that I
can order the goods in time for the work to start. It causes real problems when we have
to stop work on a project just because we have run out of the correct materials. I need
to be able to coordinate with Dylan and update information about work orders and items
needed for completing them. You can just guess what it does to our relationship with
our customers when we deliver products late. We have a lot of waste in dead stock:
materials that just sit on the shelf and wilts because we ordered too much or the wrong
materials for the customer’s order. There must be a better way.”
© 2015 Laureate Education, Inc.
Page 11 of 11
Purchase answer to see full
attachment