i need someone to do the below:
BELOW IS THE INFO, QUESTIONS THAT NEEDS ANSWERED IS IN THE BOTTOM!
Preparation of Master Budgets, Budgeted Contribution Margin Income Statemnet, and Budgeted Blanace Sheet
You have just been hired as a budget analyst for Polo Wears Company, a nationwide distributor of polo shirts. As a budget analyst, who reports to the CFO, you are responsible for all planning and budgeting. Your first assignment is to prepare a master budget for the next three months, beginning in April. You have been provided with the following information:
1. The company desires a minimum cash balance each month of $12,000.
2. The shirts are sold to retailers for $10 each.
3. Sales forecasts in units are as follows:
Month Sales volume
January (actual sales) 40,000
February (actual sales) 48,000
March (actual sales) 56,000
4. Ending inventories are supposed to equal 90% of next month's sales.
5. The shirt costs the company $5 to make.
6. Purchases are paid as follows: 50% in the month of sales and remaining 50% in the following month.
7. All sales are on credit, with no discount, and payable within 15 days. The company experience has been that only 25% of a month's sales are collected by month-end. An additional 50% is collected in the following month, and remaining 25% is collected in the second month following sales. Bad debts have been negligible. The company's monthly selling and administrative expenses are given below:
Amount Sales Commissions $2 per shirt
Fixed: Wages and Salaries $44,000
Other fixed costs $6,000
Total fixed expenses $83,400
8. All selling and administrative expenses are paid during the month, in cash, with exception of the depreciation and insurance expired. Land will be purchased during May for $50,000 in cash. The company declares dividends of $24,000 each quarter, payable in the first month of the following quarter. The company balance sheet at March 31, is given below:
Accounts Receivables ($120,000 Feb Sales; $420,000 March sales) $540,000
Prepaid Insurance $28,800
Fixed assets, net of depreciation $345,400
Total assets $1,257,200
Liabilities and Stockholder's Equity
Accounts Payable $279,500
Dividends Payable $24,000
Common Stock $600,000
Retained Earnings $353,700
Total liabilities and SE $1,257,200
9. The company has an agreement with a bank that allows it to borrow in increments of $2,000 at the beginning of each month, up to a total loan balance of $280,000. The interest rate on these loans is 2% per month and is not a compound interest (simple interest). At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much as of the loan as possible, while still retaining at least $12, 000 in cash balance. Required: Using well-formatted and labeled spreadsheets, prepare a master budget for the three-month period ending June 30. Include the following detailed budgets:
QUESTION TO BE ANSWERED:::
(1) a. A sales budget by month and in total.
b. A schedule of expected cash collections from sales, by month and in total.
c. A merchandise purchases budget in units and in dollars. Show budget by month and in total.
d. A schedule of expected cash disbursements for merchandise purchases, by month and in total.
(2) Prepare a cash budget, showing budget by month and in total.
(3) Prepare a budgeted income statements for the three-month period ending June 30. Use the contribution margin approach.
(4) Prepare a budgeted balance sheet as of June 30.
(5) Using MS-Document, write a one page memo to the Chief Financial Officer (CFO) of Polo Wears Corporation in which you summary the master budget results and make any recommendation as to whether the company should continue with it is current borrowing arrangements. In your memo comment on the potential large accounts receivable balance in June (will this be a problem for the company, why or why not?). Other ideas and suggestions you may have to improve the operations.
(6) Provide information on the company's strengths or weakness based on pro forma income statement and balance sheet.