Z cheddar project

Z Chedd’r Project

Summary of Interview with Woody Guthrie, Clerk and Accounting Assistant to Chad Mitchell

The following interview was recently conducted to provide more information on aspects of Z Chedd’r financial reporting processes. Woody usually assembles the end-of-quarter data necessary to prepare financial statements for each store and for Z Chedd’r overall. He understands that you will be performing this task, but he wanted to provide you some background on the process that he has used in the past.

Income statement (IS)

Sales

1. First, Woody takes the sales item transaction data (for retail, wholesale, and internet sales) and ensures that all transactions occur within the quarter. He then checks the Item Amount extension to make sure that sales amounts are calculated correctly. The result is the Sales Detail for Quarter.

2. Using the Sales Detail for Quarter, he then summarizes sales and shipping costs for each store. He calculates total revenue by adding sales and shipping costs. First, however, he needs to convert blank cells (null values) for shipping costs to zeroes[footnoteRef:1], since the only store with shipping costs is the warehouse. He calls this result the IS Stores Revenue. [1: Use the combination of the Val and NZ functions to change null values to zeroes that can be formatted as currency, e.g., Val(NZ([Shipping Cost per Order], 0)). ]

Cost of Goods Sold

1. Calculating cost of goods is the hardest part of the income statement. In all cases, Woody limits the transactions to the quarter.

a. Beginning inventory, valued using the physical inventory count on Dec 31 times the beginning cost in the Inventory table.

b. Purchases, valued at actual cost.

c. Transfers Out, valued by multiplying the quantity transferred times the average cost figure in the Inventory table.

d. Transfers In, valued by multiplying the quantity transferred times the average cost figure in the Inventory table.

e. Promotions, valued by multiplying the promotion quantity times the average cost figure in the Inventory table.

f. Ending inventory, valued using the physical inventory count on Dec 31 times the average cost in the Inventory table.

2. Then, he summarizes the values by store. Join the Stores table with each query identified above. Change the JOIN properties so that the resulting query shows all Stores. He changes null values (blanks) to zeroes as described in footnote 1. He makes sure that all summary-by-store queries show one row for each of the eleven stores.

3. He calculates goods available for sale at each store as beginning inventory plus purchases minus transfers out plus transfers in minus promotions.

4. Then, he subtracts ending inventory to compute cost of goods sold for each store and calls this query IS Stores COGS.

Pay Expense

1. Woody first prepares a query to limit the payroll transactions to the quarter and calculates the pay expense for each transaction. Pay expense includes gross pay plus employer contributions to FICA and MC. He calls this result Pay Expense Detail for Quarter.

2. Then, he summarizes the Pay Expense Detail for each store. He calls this result IS Stores Pay Expense.

General and administrative expenses

1. Woody first limits the miscellaneous expenses (rent, electricity, phones, and credit card bills for example) in the Purchases Admin table to those incurred during the quarter. These purchases are recorded when they are incurred, so there are no prepaid amounts. Woody calls this result Admin Expense Detail.

2. He then summarizes the administrative expenses by store. He calls this result IS Stores Admin Expense.

Promotion Expenses

1. Woody uses the information on promotion expenses subtracted from cost of goods sold as the promotion expense amount.

Credit Card Fees

1. Finally, Woody calculate the expense related to credit card fees. This expense arises from the charges assessed by the credit card processor and appears as the difference between sales plus shipping amounts and the corresponding cash receipts.

2. Woody calculates credit card fees related to each deposit and that amount is shown in the Cash Receipts table. So, for the end of quarter processing, he first limits cash receipts to the first quarter.

3. Next, he summarizes cash receipts by store. He calls this result IS Store Credit Card Expense.

Van and Store Depreciation

1. Woody divides the annual depreciation figures for each van by 4 to calculate the quarterly depreciation expense for the vans. He does the same for the owned store.

2. Then, he joins the Store table and the depreciation calculation. He changes the JOIN properties to show all stores and converts null values to zeroes for all stores. He names these two queries IS Stores Van Depreciation and IS Stores Store Depreciation.

Overall Income Statement

1. Once Woody completes the income statement for the stores, he then summarizes the amounts in that query to show the overall income statement for Z Cheddr. He calls this IS Z Cheddr Overall.

Balance Sheet (BS)

Woody says that the balance sheet is easier to prepare, since he doesn’t need to calculate balance sheets for individual stores. He just prepares one overall balance sheet.

Assets.

· Cash. To compute the ending cash balance, Woody first summarizes the cash balance beginning, the cash receipts, and the cash disbursements (cash disbursements for inventory purchases, cash disbursements for miscellaneous expenses, and payroll) in separate queries. Each query should have one row showing only the total. Then, the ending cash balance is calculated as the beginning cash balance plus cash receipts less cash disbursements.

· Accounts Receivable. Woody says that AR amounts only arise when the wholesale customers’ payments are delayed. He first summarizes wholesale sales. He then summarizes cash receipts for wholesale sales. Finally, he creates a query to calculate AR as the difference between wholesale sales and wholesale cash receipts.

· Inventory. Woody uses the physical count of inventory at the end of quarter as the basis for the ending inventory value. Inventory is valued at average cost as shown in the Inventory table.

· Stores and Vans. Woody uses the book values of the owned store and the vans. He computes accumulated depreciation including the depreciation for the current quarter. Z Cheddr records a full year’s depreciation in the year of acquisition, so accumulated depreciation is the number of years since acquisition plus one quarter times the annual depreciation amount.

Liabilities.

· Woody calculates trade accounts payable by first summarizing purchases of inventory in the first quarter, then summarizing cash disbursements for inventory in the first quarter, and finally subtracting cash disbursements from purchases.

· Woody uses the same process to calculate administrative accounts payable.

· Finally, he calculates payroll taxes payable by summarizing employer and employee FICA and MC, federal withholding, and state withholding. He sums those six items to determine taxes payable.

· Z Chedd’r has no mortgages or notes outstanding, so Woody does not need to calculate long-term liabilities.

Z Equity. Since Mr. Z owns all the stock and he doesn’t need a breakdown of stockholders’ equity, Woody usually computes and single Stockholders’ Equity value as assets minus liabilities.

1

Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact