Answer Happy

Future
Franks expects F & C Construction to continue to grow. The
local economy is good, and individual homeowners are investing in
home improvements. Frank also wants to expand into the
remodelling and office construction
areas. This expansion will require competitive bidding and
formal reporting, perhaps to meet lending company requirements.
Improvements to the formal information system are needed to support
the expected growth and allow Frank to continue doing the actual
construction work.
Convert the data structure into data tables to a
third normal form.
Implement the data tables in MS Access. (You will have
to make assumptions about field lengths and types.) Print out
a relationship table that identifies the primary and foreign keys
necessary to create the database for the proposed new
system.
Frank, owner of F & C Construction, has the opportunity to bid on a job that includes remodeling two existing auto dealership offices and constructing a new office. This job would give F & C Construction a chance to move into a new market and expand its customer base. Frank is excited about the possibilities of making this move; however, to operate in this market, Frank will need to prepare formal business reports. Additionally, he is concerned about his ability to bid competitively and track the increased number of jobs this opportunity would provide. Background F & C Construction is a small, sole proprietorship. The owner, Frank, started his construction company approximately two years ago. Business is increasing at a steady pace. Currently, jobs range in size from small (kitchen countertop replacements, wooden decks) to large (additions to homes). His customers are individual homeowners. Advertising is predominantly by word of mouth. The company logo is on both company trucks, and a placard is placed in a high-visibility location at job sites. Business cards are posted on bulletin boards in local stores and handed out at every opportunity. Satisfied customers and suppliers have made many referrals. F & C Construction is known for high standards of workmanship, using quality materials, and a no-surprises billing policy. (If Frank forgets to include a charge in the estimate, it is not added on at a later date. If the customer and Frank negotiate a change in the planned work, the bill is modified accordingly.) The company currently employs Frank and two construction workers. Frank and his two workers are licensed to do general contracting work only; therefore, any plumbing or electrical work associated with a job is subcontracted out. Caren, Frank's wife, does the bookkeeping on a part-time basis. A tax accountant is retained as a financial adviser and for filing all necessary tax documents. F & C Construction is run out of an office in Frank's home. A telephone answering machine takes calls whenever Frank and Caren are both away from the office. F & C Construction's physical assets include two pickup trucks, an assortment of large and small tools, and an inventory of supplies (screws, nuts, bolts, spackle, etc.) that is stored in a shed or on the trucks.
Sales When a potential customer calls to request a bid on a job, an appointment is scheduled. Frank does all of the bidding for F & C Construction. A formal proposal (Fig. 1) is completed and submitted to the customer. If the customer accepts the proposal, it is signed and becomes a legal contract. The customer keeps one copy, and F & C Construction retains the other copy. The contract also serves as the accounts receivable record, since it includes a schedule of payments and a work schedule. To date, due to the limited size of the business, keeping track of receivables due from customers has not been a problem. The proposal indicates the customer's name, address, phone number, where the work is to be done, a description of the job, terms of the agreement, and a payment schedule. Payments are required as follows: • Large jobs. Payments are broken down by the following major sections: one-half of the amount owed for the section is due when work begins on that section, and the remainder is due when the work is completed. 1. Subcontractor fees 2. Framing 3. Roofing 4. Siding 5. Sheetrock 6. Trim Small jobs. One-half of the payment is due up front and the remainder is due at completion.
Cash Receipts Payments are recorded on the contract or a separate sheet of paper attached to the contract when received. Frank immediately makes out a deposit slip, in duplicate, and brings the deposit to the bank. One copy of the deposit slip is retained, and the other goes to the bank with the cash/cheques. Caren records all deposits in the cheque register (Fig. 2). The date the deposit was made, the customer name, the cheque number, and the amount of the deposit are recorded. Monthly, Caren does the bank reconciliation. Purchases As materials, supplies, and tools are needed, they are purchased from local suppliers. Since Frank deals with these suppliers on a regular basis, he mentally compares prices to obtain the best price for the quality products needed. If the purchase is a small dollar amount, Frank usually pays cash. (Note: When I asked Frank how he handled receipts for cash purchases, he told me he threw the cash register receipt on the dashboard of whichever truck he was driving, and at month-end he or Caren collected them so Caren could do the bookkeeping.) If the purchase is for a large dollar amount, Frank charges the purchase. He has a line of credit with all of his major suppliers who bill on a monthly basis. The charge slips are accumulated along with cash register receipts on the dashboards of the trucks and collected monthly for payment and recording purposes. When an invoice is received, Caren matches it with the charge slip(s). Occasionally, a special tool is needed to complete a job. In these instances, Frank rents the tool from a local tool rental company. The procedure for rentals is the same as that for credit purchases. If a piece of equipment is rented more than two or three times, it is purchased. Cash Disbursements F & C Construction has one cheque account that is separate from Frank and Caren's personal cheque account. Both Frank and Caren write cheques to pay bills, meet payroll, and pay tax obligations. When a cheque is written, the payee, date, amount, and explanation for the cheque are recorded on the cheque stub, which remains in the cheque register (Fig. 2). All bills are paid by the fifteenth of the month to avoid finance charges. At month-end, Caren updates the cheque register and ledger book. Cash disbursements are tracked (spread) by category.
Payroll When a subcontractor is used, jobs are awarded based on bids, and a standard subcontractor agreement (Fig. 3) is executed. Subcontractor fees are collected by F & C Construction according to the payment schedule above. Subcontractors are paid when they have completed their job, unless it is a big job and the subcontractor needs some money up front. Time cards (Fig. 4) are kept for each of the construction workers. Workers fill out the time card daily to track hours worked. Frank approves the time cards, and workers are paid by cheque on a weekly basis. Caren uses the time cards to update the payroll register (Fig. 5). Most of this information - name, address, IRD number, earnings, and deductions - is necessary for tax purposes. Frank pays himself a salary on a weekly basis. He writes a cheque and records the amount on the cheque stub. This information is then used by Caren to update the ledger book. Caren is not paid. Quarterly and Yearly Procedures On a quarterly basis, Caren brings the ledger book up to date and gives it to the accountant, who prepares quarterly financial statements that are used to prepare payroll tax forms. All forms and payments are filed on a timely basis by Caren. At year-end, a federal tax form 1099 is filed for each subcontractor and construction worker. This form reports the amount paid to subcontractors and workers by federal ID number or Social Security number. The information needed to file each 1099 is available on the subcontractor agreements or in the payroll account for the workers. The accountant also prepares the income tax return.