Assignment:
Managerial Accounting Budgeting Project - Spring 2015
The following is a Comprehensive Budgeting Problem. You and your team need to prepare a partial Master Budget and then continue on to analyze your data. This is an Excel based project where you will create formula driven worksheets.
Part 1:
Required: Prepare the following elements of the master budget. Additional information needed to prepare the budgets follows below.Use the examples in your textbook as a guide.
1. Sales budget
2. Budgeted cash receipts
2. Production budget
3. Raw materials budget
4. Budgeted cash payments for raw material
5. Direct labor budget.
6. Manufacturing overhead budget
7. Cost of goods sold budget
8. Selling and administrative expense budget
9. Cash budget
10. Budgeted Income Statement
You must use Excel; and in particular use formulas and linked cells. You are to create an input page that will house ALL your data. Your input page should be the only tab where you actually manually input data. You are to create a separate tab for each required budget. Use cell references to transfer amounts from the input tab to the other respective tabs. You must ensure that your budgets print properly and have a professional appearance.
You must also include a written document where you discuss each budget's results.
Budgeting Information:
Friar Company is preparing budgets for the 2nd quarter, ( April, May & June) which ends on June 30. Make sure you include a "Total Q2" column.
• Budgeted sales of the company's only product for the next five months are:
|
April .........
|
2,400 units
|
|
May ...........
|
4,000 units
|
|
June ...........
|
3,500 units
|
|
July ...........
|
3,000 units
|
|
August ......
|
1,600 units
|
| |
|
• The selling price is $50 per unit.
• All sales are on account.
• The company collects 80% of these credit sales in the month of the sale; 15% are collected in the month following sale; and the remaining 5% are uncollectible.
• The accounts receivable balance on March 31 was $22,500. All of this balance was collectible.
• The company desires to have inventory on hand at the end of each month equal to 25% of the following month's budgeted unit sales.
• On March 31, 600 units were on hand.
• 5 pounds of material are required per unit of product.
• Management desires to have materials on hand at the end of each month equal to 10% of the following month's production needs.
• The beginning materials inventory was 1,400 pounds.
• The material costs $0.25 per pound.
• For June: 2,650 units to be produced in July
• 60% of a month's raw material purchases are paid for in the month of purchase; the other 40% is paid for in the following month.
• The accounts payable balance on March 31 was $10,000.
• Each unit produced requires 0.75 hour of direct labor time.
• Each hour of direct labor time costs the company $10.
• Variable manufacturing overhead is $15 per direct labor hour.
• Fixed manufacturing overhead is $35,000 per month. This includes $15,000 in depreciation, which is not a cash outflow.
• Manufacturing overhead is applied to units of product on the basis of direct labor hours.
• Variable selling and administrative expenses are $0.30 per unit sold.
• Fixed selling and administrative expenses are $40,000 per month and include $8,000 in depreciation.
• A line of credit is available at a local bank that allows the company to borrow up to $50,000.
o All borrowing occurs at the beginning of the month, and all repayments occur at the end of the month.
o The interest rate is 1.5% per month.
o The company does not have to make any payments until the end of the quarter but they are allowed to
• Friar Company desires a cash balance of at least $50,000 at the end of each month. The cash balance at the beginning of April was $60,000.
• Cash dividends of $40,000 are to be paid to stockholders in April.
• Equipment purchases of $30,000 are scheduled for June. This equipment will be installed and tested during the second quarter and will not become operational until July, when depreciation charges will commence.
Part 2:
After the end of the second quarter, Friar Company wants to evaluate their performance. In preparing their budgeted income statement, Friar Company used the following formulas to determine their budgeted operating income:
Revenue $50Q
Cost of Goods Sold $30.448Q
Selling & Administrative Expenses $0.30Q + $120,000
Interest Expense $1,301
During the quarter, Friar Company planned for 9,900 unit sales, but actually sold 10,000 units. The company reported the following actual results for the quarter ended on June 30 th :
Sales $507,500
Cost of Goods Sold $302,500
Gross Margin $205,000
Selling and Administrative Expenses $125,000
Net Operating Income $80,000
Interest Expense $1,500
Net Income $78,500
Required: Prepare the company's flexible budget performance report for the second quarter. Label each variance as favorable (F) or unfavorable (U). You are to create your performance report also in Excel, as a separate tab. Include any relative data on your input page and continue to use cell referencing.