Use the following information to create the Quarterly budgets on Excel including the full year total for the year 2025 and include the following items:
1. Sales Budget
2. Production Budget (Including: Direct Material, Direct Labor, Manufacturing Overhead)
3. Operating Expenses Budget
4. Budgeted Income Statement (Include the calculation of the cost per unit)
5. Capital Budget Combined Cash (Including: Cash Receipts and Cash Disbursements)
6. Budget Balance Sheet.
Operating Expenses: NOTE: All items are expected to be paid in the quarter in which it was incurred except for Depreciation! Variable Expenses - Calculated based on units of sales Sales Commissions - $4.50 Freight Out - $2.00 Fixed Expenses per quarter: Advertising - $7,000 Sales Salaries - 25,000 Office Salaries - 12,500 Depreciation - 3,500 . Property Tax Insurance - 3,000 Income Taxes Estimate for full year is $18,000 and is expected to be paid evenly each quarter. Interest Expense for the full year is estimated to be $500 and used when repaying borrowed funds. Combined Cash Budget detail information: 1. Cash balance as of Jan 1, 2025 is expected to be $125,000 and Data's cash on hand policy requires them to have a minimum of $15,000 on hand at the end of each month. 2. Account receivable balance for Dec 31, 2024 is $110,000 and is expected to be collected in the first quarter of 2025. 3. Short-term investments are planned to be sold for $25,000 cash in the first quarter. Accounts payable balance of $35,500 at Dec 31, 2024 is planned to be paid in full in the first quarter of 2025. 5. Management plans to purchase a truck in the second quarter for $50,000. 6. Income tax payments are made quarterly by Data based on the estimated annual amount. 7. Loans are repaid with interest in the earliest quarter possible in which there is a sufficient amount of cash on hand after meeting the $15,000 minimum requirement. Loans are borrowed in $1,000 increments. Balance Sheet Data Needed: Balances as of Dec 31, 2024 Full year. Building and Equipment - $368,000 Accumulated depreciation - 55,100 Common Stock - 375,000 Retained Earnings - 271,503 Check Figures: *Total Assets = $1,050,550 *Cost per Unit = $100.00 *Net income for the full year = $313,904 * Ending Cash Balance = $42,508 NOTE: Budget Year = 2025 Prior Year = 2024 Following year = 2026 Make sure your spreadsheet is fully connected from section to section, I will check to make sure your income statement updates automatically when the sales data is changed the net income should automatically update. Spreadsheet Connectivity Test: I will test your spreadsheet by changing the unit-selling price to $150, when this change is made the Net Income should be $1,039,404 Hint: Be sure to include the impact to your cash budget to determine if you still need to borrow cash. All changes should update automatically with the price change to get full points.Introduction and Requirements: Data Computer Company manufactures hard drives for servers for all the major computer companies. Below you will find the information for their 2025 budget. Use the following information to create the Quarterly budgets including the full year total for the year 2025 and include the following items: Sales Budget . Production Budget (Including: Direct Material, Direct Labor, Manufacturing Overhead) Operating Expenses Budget Budgeted Income Statement (Include the calculation of the cost per unit) Capital Budget Combined Cash (Including: Cash Receipts and Cash Disbursements) Budget Balance Sheet Data Required to Complete the Requirements Above Quarter 1 Quarter 2 Quarter 3 Quarter 4 Full Year Sales Budget: Expected Unit Sales 5,000 6,000 8,000 10,000 29,000 Each unit sells for $125 . 65% collected in quarter sold, 35% collected in following quarter. Production Budget: Ending Inventory should be 20% of the next quarter's unit sales. The expected unit sales for Quarter 1 of 2026 are 11,000 units. Direct Material Budget: Ending materials Inventory should be 10% of the next quarter's production requirements. . The production of the drives requires 2.5 pounds of material costing $7 per pound The expected production requirements for Quarter 1 of 2026 is 28,050 pounds 50% of purchases are paid in quarter of purchase and the remaining 50% is paid the following quarter Direct Labor Budget: The required direct labor hours per unit are 3 hours. The average cost per direct labor hour is $20. . Paid 100% in the quarter it was incurred Manufacturing Overhead Budget: Variable Costs -Calculated based on the direct labor hours used, rates are as follows: NOTE: All items are expected to be paid in the quarter in which it was incurred except for Depreciation! Indirect Material - $2.00 per direct labor hour Indirect Labor - $1.30 per direct labor hour Utilities - $1.20 per direct labor hour Maintenance - $0.50 per direct labor hour Fixed Cost per quarter includes: Supervisor Salaries - $30,000 Depreciation - 4,500 (Assume this includes the New Asset Purchase. No Changes . Property Taxes & Insurance 15,000 Maintenance - 7,150 Ver. 2020-Revised 04/16/20
