WHAT'S HAPPENING TO PROFITS AT CAZENOVIA CREATIONS? INTRODUCTION

Annually your firm, Smith & Jones, CPAs, come in to prepare a year end compilation of the financial statements and the Sub S corporation tax return for Cazenovia Creations.This past year the owner,Meghan McGuire, has become concerned because the sales figures year over year have been growing significantly but net income does not seem to be keeping pace.Her best sales quarters are usually around Valentine's Day and the Christmas season..The 2012 Christmas season was particularly good and she had expected a sizeable increase in net income for the year.Meghan has asked for your assistance in determining why the net income for 2012 has not been as good as expected.She has provided you with comparative financial statements and transaction data (purchases, sales, and payroll) for the last seven months of 2012 in an Access database.

BRIEF HISTORY OF CAZENOVIA CREATIONS

Meghan McGuire started selling her jewelry at Craft Shows throughout the Northeast in the 1990's.Her desire was to create wearable art that would be timeless and unique.As her jewelry became more popular, she started Cazenovia Creations selling hand crafted jewelry from other artisans as well as her own designs.Over the next decade, her company grew to become the largest independent seller of hand crafted jewelry in western NY, Ohio, and Pennsylvania.As the business grew Meghan found that she no longer had the time to go to Craft Shows but instead needed to concentrate her efforts on managing the business.She employed several salesrepresentatives who placed the jewelry in boutique clothing/accessory stores located primarily in malls.Meghan also hired two people to interface with the existing jewelers as well as to identify potential new craftspeople..Soon Cazenovia Creations was purchasing and placing pottery, blown glass, woven items and woodcrafts.

As the business grew Meghan realized she did not want to spend her time on administration.She was much happier designing the jewelry that was the backbone of the business.In order to return to her creative pursuits, Meghan hired Sylvia Somerset, a retired bookkeeper/office manager.Sylvia was highly recommended to Meghan by one of Cazenovia Creations' biggest customers.Sylvia had retired from her previous position several years ago when her husband had become chronically ill and had decided to return to the workforce just when Meghan was hiring.

Over time, Meghan grew to rely on Sylvia and gave her more and more of the day to day responsibilities of running the business.Sylvia lived up to her reputation as a reliable employee and often seemed like a wise financial advisor and almost parental figure to Meghan.Sylvia was never late or absent and hadn't even taken a vacation in the time she had been with the company.

When she was hired, Sylvia recommended to Meghan that they use online banking and a database accounting system with remote access (the tables included in the database are given at the end of the case).In addition to the cost benefits to the company, this step would allow Sylvia to work from home if her husband's illness did not allow him to be alone.Meghan agreed that this would be a significant improvement.

REVENUE CYCLE

Meghan, with Sylvia's assistance, has divided the region into 4 sales territories and has hired representatives to travel to boutique stores throughout the region to sell both Meghan's jewelry and the work of other artisans.Because many of these artisans are solo craftspeople they are willing to sell to Cazenovia Creations at a price lower than normal retail in exchange for Cazenovia Creations placing these items in the various boutique stores on their behalf.Cazenovia also handles billing and collection, taking the credit risk for these sales.The gross profit on sales varies from a high of 42% to a low of 25% depending upon the product line.Based upon the normal product mix the overall gross profit averages between 31-33%.

Steps in the revenue cycle:

The territory sales reps bring samples of Meghan's and the artisans' work with them to the boutique locations and take orders for the various products.

Sylvia checks the credit record of the boutique stores, adds new stores if they meet the credit requirements, and approves orders from qualified customers.

Sylvia prepares Cazenovia Creations' purchase orders twice a month to go to the craftspeople.

The craftspeople ship the products directly to the boutique stores with a copy of the shipping notice going to Sylvia.

Sylvia is copied on an electronic receiving report generated by each boutique.

Sylvia maintains the inventory records and price files for all items.

Sylvia matches the shipping and receiving reports, records the corresponding purchases and sales, and creates invoices.

The sales to all boutiques are made with n/30 credit terms.

Sylvia follows up with delinquent accounts and determines any bad debt write-offs that are necessary.

Cazenovia Creations as an entity does not maintain any inventories.Instead the sales representatives take orders for the products of the various vendors and then arrange direct shipment from the vendors to the retail customers.Because of the direct ship nature of all sales/inventory purchases the following entries were made in the previously used manual system (prior to Sylvia converting the records to a database):

Cost of goods soldXXXX

Accounts Payable XXXX

Accounts ReceivableXXXX

Sales- Other Crafts XXXX

This accounting is now done in the database system which is summarized to produce the general ledger.

Miscellaneous customer and vendor information:

Meghan has satisfied her re-dedication to jewelry design in two different ways.She creates individual high end pieces on a custom commissioned basis (these sales are not accounted for on Cazenovia Creations' books). These high end custom orders allow her to satisfy her creative aspirations and are reported by Meghan as a sole proprietor.In order to appeal to a broader market, she also designs more moderately priced earrings and bracelets.Meghan outsources the manufacture of these items to a local workshop (Caporale, Vendor #1453).Caporale mass produces Meghan's creations that the sales representatives market to the boutique stores.Since Caporale direct ships the products to the various boutique stores, the accounting is handled in the same manner as for the outside craftspeople.

One of the major customers for Cazenovia Creations' products is a chain of 25 mall based boutique stores, MaBou..All of the invoicing for the purchases of the entire chain is processed by the parent company.There are currently over 60 total customer stores, including the 25 associated with the MaBou chain.

VENDOR FILES

Cazenovia Creations has grown substantially every year for the past five years, encompassing four sales territories in the tri-state area.Cazenovia rents space at $2,500 per month and incurs operating expenses for Meghan's local workshop and office space for Sylvia and the purchasing agents.

There are currently 19 craftspeople that market their products through Cazenovia Creations including Caporale (the vendor which manufactures Meghan's creations). Cazenovia's purchasing agents determine what products to carry and negotiate pricing and terms of purchase.In addition to working with the current craftspeople, the two purchasing agents seek out new craftspeople.

The vendor transactions are processed as follows:

Sylvia adds new and maintains existing craftspeople vendor records.

Sylvia also establishes and maintains the other vendor records for regular operating expenses.

Sylvia pays all purchase invoices on a timely basis after matching shipping documents to the receiving reports from the boutique stores.

oPayments made on 6/15/2012 pertain to the Accounts Receivable on 5/31/2012, the detail of which is not available.

Sylvia pays the operating expense vendors once monthly on varying due dates.

PAYROLL SYSTEM

Cazenovia's employees include Sylvia, the two purchasing agents, and four sales representatives.The sales representatives are often craftspeople themselves who were having a difficult time making ends meet simply from selling their crafts. The sales position enables them to earn extra cash while still pursuing their own creations.Meghan has thus been able to hire very knowledgeable representatives at fairly low compensation.For 2011 and 2012 each sales representative earned a base pay of $1,600 per month and a 10% commission on sales.The purchasing agents also each earn a monthly salary of $1,600.Sylvia earns $2,800 per month.All employees utilize the direct deposit system that Sylvia has set up for payroll.The sales reps' base pay and the salaries of other employees are paid semimonthly.Sales commissions earned are paid electronically on the last day of the month in a separate direct deposit.

Payroll transactions are processed as follows:

Sylvia maintains all payroll records.She adds new employees, maintains pay rates, records terminations, computes the semimonthly payrolls, and calculates and enters the commissions due to the sales representatives.

Sylvia is responsible for ensuring that all tax withholdings and liabilities are properly computed and forms are timely filed.

TRAVEL EXPENSE REIMBURSEMENTS

Cazenovia Creations also reimburses the sales representatives for travel expenses incurredmileage at $.50 per mile and occasional meals and lodging in the local area when the sales representatives need to come to the home office for updated product training and sampling.Sylvia approves the travel expense requests of the sales reps and reimburses those expenses on a monthly basis.

FINANCIAL REPORTING

The financial statements are prepared by Sylvia for Meghan's review.Meghan originally operated her business as a sole proprietorship and then had elected at the beginning of 2011 to operate as an S corporationMeghan did not take a salary in either 2011 or 2012.For both years, she has taken a monthly distribution of $4,000 from the S corporation.Purchases and sales are recorded on an accrual basis but other operating expenses are recorded on a cash basis. Meghan's workbench, various tools, office furniture and equipment have all been owned for several years and are currently fully depreciated to their remaining salvage values.

Cazenovia Creations

Income Statements for:

Year ending

Year ending

12/31/2012

12/31/2011

Sales - Meghan'sProducts

$347,111

$321,560

Sales - Other Crafts

1,340,944

1,123,547

Total Sales

1,688,055

1,445,107

Cost of Goods Sold:

Meghan's Products

238,695

214,802

Other Crafts

964,647

773,000

Total Cost of Goods Sold

1,203,342

987,802

Gross Profit

484,713

457,304

Operating Expenses:

Salary Expense

158,672

148,800

Commission - Sales Reps

164,044

140,175

Payroll Taxes

27,721

26,008

Utility Expense

12,318

11,214

Rent

30,000

30,000

Insurance Expense

2,760

2,660

Travel Expenses

28,258

25,468

Postage and Supplies

1,524

1,426

Advertising

3,725

2,750

Total Operating Expenses

429,022

388,502

Net Income

$55,691

$68,803

Cazenovia Creations

Statement of Retained Earnings for

the Year ended 12/31/2012

Retained Earnings January 1, 2012

$52,582

Add:Net Income

55,691

108,273

Less: Distributions to Owner

(48,000)

Retained Earnings December 31, 2012

$60,273

Cazenovia Creations

Balance Sheets

As of December 31,

2012

2011

ASSETS

Current Assets:

Cash

$51,410

$72,963

Accounts Receivable

169,959

132,619

Total Current Assets

221,369

205,582

Property & Equipment

Furnishingsand Equipment

28,246

28,246

Less:Accum. Depreciation

(24,246)

(24,246)

Net Property & Equipment

4,000

4,000

Total Assets

$225,369

$209,582

LIABILITIES & STOCKHOLDER'S EQUITY:

Current Liabilities

Accounts Payable

$118,203

$110,555

Payrolltaxes Payable

6,892

6,445

Total Liabilities

125,095

117,000

Stockholder's Equity

Common Stock

40,000

40,000

Retained Earnings

60,273

52,582

100,273

92,582

Total Liabilities

and Stockholder's Equity

$225,369

$209,582

Database Tables

Primary keys are underlined

Foreign keys are indicated under the attribute name

tblChartOfAccounts

ActNum

Title

Norm

tblCommissions

Month

EID

Total

Commission

FICA

FedIncTax

NetComm

FK refers to tblEmployees

tblCustEmp

CustNum

empNum

tblCshDisb

TransNumber

Date

Amount

Glacct

Enum/vendNum

FK refers to tblChartOfAccounts

tblCshReceipts

RANum

Date

Amount

GLacct

CustAcct

FK refers to tblChartOfAccounts

FK refers to tblCustomers

tblCustomers

CNum

Cname

Street

Zip

AreaCode

Phone

FK refers to tblZips

tblEmployees

EID

EFName

ELName

EAreaCode

EPhone

EHireDate

ETerminationDate

EDOB

Address

Zip

Bank Routing

BnkAcctNum

FK refers to tblZips

tblInventory

InventoryID

Type

Description

Cost

Price

Crafter

FK refers to tblVendors

tblInvoice

InvoiceID

InvoiceDate

CustomerID

EID

FK refers to tblCustomers

FK refers to tblEmployees

tblInvoiceLine

InventoryID

Quantity

InvoiceID

FK refers to tblInventory

FK refers to tblInvoice

tblMyBou

StoreNum

ShipAddress

ShipZip

MasterAcct

AreaCode

Phone

FK refers to tblZips

FK refers to tblCustomers

tblMyBouShip

ShipStore

InvoiceID

FK refers to tblInvoice

tblPayroll

Month

PayPeriod

EID

Salary

FICA

FedIncTax

NetPay

FK refers to tblEmployees

tblPO

Vendor

POdate

POnum

FK refers to tblVendors

tblPOline

InventoryID

Quantity

POnum

FK refers to tblInventory

FK refers to tblPO

tblPOshipDate

POnum

InventoryID

Quantity

ShipDate

InvoiceID

FK refers to tblInventory

FK refers to tblInvoice

tblVendors

CrftID

CrftName

CrftAddress

Zip

Contact

AreaCode

Phone

BankRouting

BnkAcctNum

FK refers to tblZips

tblZips

City

State

Zip

Part 1: In order to determine if Meghan's concerns are valid you must perform vertical and horizontal analysis on the financial statements.Changes should be checked to determine if they are consistent with the facts given in the case.Any significant inconsistent changes should be flagged for further investigation.A change may be significant either because of an absolute dollar amount or percentage.

1. Once you determine which items warrant further investigation, you must consider how those items can be investigated.To that end, identify 3 or more possible causes for the changes you have identified.For each possible cause indicate how you could verify if it is an actual source of the observed inconsistency.
2. Use the database posted on Sakai to do the followings:

• create table for each worksheet
• name each table based on the name of the worksheet
• develop queries or pivot tables in excel that will help you investigate those items identified in step 1