> Pre-opening (March)
You decide to go into the bicycle business and invest $100,000 of your own money.
You rent a building in a shopping center for $3,000 per month, beginning April 1. Rent is due on the first day of each month.
You pay $18,000 to modify the building and anticipate that you will occupy the building for 6 years.
You also purchase $6,000 of equipment for the repair shop. The equipment should last five years.
You negotiate a line of credit with the bank for up to $250,000 with an annual interest rate of 7%. Interest is payable on the first day of each month for the interest incurred during the previous month.
You hire two sales employees with an agreement to pay them $3,000 per month each plus 5% of the sales that they generate (based on selling price). They will start working on April 1. You hire one bicycle mechanic at $4,000 per month to start on April 1.
You get phone service and utilities hooked up. The fee was $100 and you will pay once its billed to you.
You buy $12,000 of supplies for repairing bicycles from a supply store on credit.
You order 400 bicycles at an average cost of $300 per bicycle and plan to draw down $120,000 of your line of credit to pay for them when they arrive on April 1.
> Open Store on April 1
On April 1, bicycles arrive and are paid for using cash obtained from line of credit.
Sell 200 bicycles in April at an average price of $500 per bicycle.
Pay $2,000 for radio advertising during the month.
Pay supplier $8,000 for supplies ordered in March.
Earn $6,000 from bicycle repairs.
Count supplies at the end of the month and find that you have $10,000 of supplies remaining.
Get phone bill for $200 and utilities bill for $300 but do not pay them before the end of the month.
You pay employees on the last day of the month.
You do not pay down the line of credit during the month.
Thank you for posting your question in Brainmass.
You will find in attachment a dyniamic Excel file ...
Solution is shown in a dyniamic Excel file including 3 sheets. Sheet 1 : Data (letters are added to identify transactions)
Sheet 2: T accounts (dynamic)
Sheet 3: Satatements (dynamic)
T-accounts are used to summarize activities. A classified income statement and a classified balance sheet were prepared.
The letters that identify transactions in the data Sheet will help you keep track of them in the T account sheet.
For computation see formula inside the calculated Excel cells.