General Information:
- This assignment requires you to integrate your knowledge of Financial Accounting (ACC 207), budgeting (from ACC 208) and Excel. It will take you several hours to complete. Do not start this assignment at the last minute.
- Only one grade per team will be awarded.
- No late submissions will be accepted.
Required format:
1. Electronic submission
a. You are expected to use formulas (formulae)/links/functions in completing this assignment. Fifteen points will be deducted if you simply enter the numbers in the correct space, even if your solution is correct!
b. Format:
i. Each budget must appear on a different sheet (tab). The title of each budget should appear as the name of the sheet (tab) on which it appears.
ii. You must use appropriate headers for each schedule.
iii. Use 11 point, Time-New Roman font
Prepare and present all reports in a neat and professional manner. Editing is a very important part of your workand requires a significant amount of time. Plan ahead so you have enough time to complete the assignment.
Budget Assignment Data
Coaches Company distributes women's purses in the Midwest. The following information was gathered to prepare the budget for the second quarter.
- Purses are budgeted to sell for an average price of $250. Unit sales are expected to be as follows:
March 3,900
April 4,200
May 4,700
June 4,300
July 4,100
- Sales are made for cash and on credit. The following collection pattern is used to estimate monthly cash collections:
Cash sales 40%
Credit sales - month of sale 32%
Credit sales - month after sale 24%
Uncollectible 4%
Total 100%
- The company maintains an inventory of 20 percent of the following month's sales. The company expects to have 840purses on hand on March 31. Coaches pays an average of $200per purse.
- The company pays for 75 percent of its purchases in the month of purchase and the remaining 25 percent in the month after purchase.
- The following monthly selling and administrative expenses are planned for the quarter, though salaries will have a one-time $10,000 increase in May and advertising will decrease by $10,000 in June.
Depreciation $ 8,000
Rent $ 38,000
Advertising $ 30,000
Salaries $ 80,000
- On June 30, the company plans to purchase $20,000 of new office equipment. However, no additional depreciation will be recorded in the second quarter.
- Coaches wants to maintain a minimum cash balance of $30,000. An open line of credit at a local bank allows the company to borrow up to $100,000 per quarter in $1,000 increments.
- All borrowing is done at the beginning of the month, and all repayments are made at the end of a month in $1,000 increments. Accrued interest is paid only when principal is repaid. The interest rate is 12 percent per year.
- Accrued expenses from the first quarter will be paid in April.
- Coaches' income tax rate is 20 percent and is accrued each month.
- The March 31 balance sheet is budgeted as follows:
March 31st
Cash $ 30,000
Accounts Receibable 234,000
Inventory 168,000
Plant & Equipment 450,000
Accumulated Depreciation (180,000)
Total Assets $ 702,000
Accounts Payable $ 198,000
Accrued Expenses 54,000
Common Stock 325,000
Retained Earnings 125,000
Total Liabilities and Owners Equity $ 702,000
Required
a) Prepare all components (sales budget, cash collections budget, purchases budget, cash disbursements for purchases budget, selling and administrative budget, and cash budget) of Coaches' master budget for the second quarter (1 April - 30 June); include calculations for each of the three months and for the quarter.
b) Prepare a pro-forma income statement for the second quarter.
c) Prepare a pro-forma balance sheet as of June 30.