Number 3 and 4

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 1* quarter? 1. Budgeted sales in units Multiplied: budgeted selling price/unit Budgeted sales revenue SALES BUDGET January 12500 8 100000 February March Quarter Total 9000 11000 8 8 72000 88000 260000 2. 5% 95% TYPE OF SALE January February March Quarter Total Budgeted sales revenue 100000 72000 88000 Cash sales 5000 3600 4400 Credit sales 95000 68400 83600 CASH RECEIPTS BUDGET Cash sales 5000 3600 4400 Collections on credit sales this month 19000 13680 16720 Collections on credit sales made one month ago 57000 41040 50160 Collections on credit sales made two months ago 14250 10260 12540 Budgeted cash receipts or collections 95250 685801 83820 2476501 20% 60% 15% 3. PRODUCTION BUDGET January February March 12500 9000 11000 Quarter Total 15% Budgeted sales in units Plus: Desired ending FG inventory Less: Beginning FG inventory Number of units to produce 0 4 Quarter Total March 0 2 0 0 2 0 0 12% DIRECT MATERIALS BUDGET January February Number of units to produce 0 Multiplied by: quantity of DM per unit 2 Equals total DM needed for production Plus: desired ending DM inventory Less: beginning inventory of DM Equals total quantity of DM to purchase Multiplied by: cost per unit of DM Equals total cost of DM purchases 0