master budget
Complete a master budget using Excel in a group of 3. Start with a blank Excel workbook. Using someone else's spreadsheet is a violation of the Academic Integrity Policy. All members must have the Excel spreadsheet available in every class period. Use input boxes for all % and proper cell referencing. Format all numbers as accounting with the last line of each budget including dollar signs where applicable. Do not round given numbers but round all answers to the nearest dollar (no decimals). Make the quarterly total for each budget bold and a larger font. Include grid lines as needed and at least one color to focus your reader's attention. Submit one Excel file per group named "Group #" in Blackboard at Content/ Assignments/ Master budget project by the due date. Include a tab that lists what each person contributed and what % of the project each completed. 1. Sales budget Create a sales budget for the 1st quarter that shows expected sales revenue for January, February, and March and total sales revenue for the quarter. Sales in units are expected to be: November: 9,000 March: 11,000 December: 11,000 April: 10,000 January: 12,500 May: 8,500 February: 9,000 Selling price/unit is expected to be $8/unit 2. Cash collections budget Cash sales are generally 5% and credit sales are generally 95%. Credit sales are typically collected: 20% in the month of sale, 60% in the following month after the month of sale, 15% 2 months after the month of sale and 5% are never collected. What is the cash collections budget for January, February, and March and for the quarter? 3. Production budget The company plans to have ending inventory each month of 15% of the following month's expected sales. What is the production budget for January, February, and March and for the 1st quarter? 4. DM budget 2 pounds of DM is needed per unit at a cost of $3 per pound. Ending inventory of DM should be 12% of next month's production needs. What is the total cost of DM for January, February, and March and for the 1st quarter? 5. DL budget DL hours per unit is.10. The DL rate per hour is $10. What is the DL budget for January, February, and March and for the 1st quarter? 6. MOH budget The company's variable MOH rate is $8.25 per DL hour and the company's fixed MOH is $3,000 per month. How much MOH will be budgeted for January, February, and March and for the 1st quarter? 7. Capital Expenditures Budget The company expects to make the below purchases of PPE in the 1st quarter. Complete a capital expenditures budget for January, February, and March and for the 1st quarter. January Warehouse $28,000 8. Combined cash budget Prepare a combined cash budget for January, February, and March and for the 1st quarter that includes cash collections and payments for the cost of DM, DL, MOH and capital expenditures found in prior budgets. Beginning cash balance on Jan. 1: $65,000
