Part II - The Budget Project
Your first job assignment requires you to create a master budget. The details are presented below.
Background
Apple Inc. manufacturers I-phones. You have been hired as a cost accountant and your assignment to create a master budget using EXCEL.. You have been working with various departments to gather financial data to assemble a master budget. One reason you have chosen this format is that upper management will probably make some last-minute changes to sales and possible changes to some other cost forecasts. So, to simplify this problem you MUST link all the budgets together. For example, if you change the sales forecast, then all the remaining numbers in all other budgets would change without having to change numbers manually in each budget.
One quarter of the budget has been prepared for you but you need to input the other three quarters in order to prepare the income statement.
Here are the steps:
1. You must setup all eight budgets using Excel. Starting with the sales budget, manually calculate the sales budget forecast for all four quarters and calculate the total column
2. Each budget should be prepared on individual Excel spreadsheets, on separate tabs.
3. Each budget should professionally represent all the budget information; must be easy to understand so it can be presented to the CEO and the owners of the business.
4. Briefly discuss your assumptions and any challenges you encountered while preparing the budget.
3. Direct materials purchases budget: Apple Inc. Direct Materals Purchases Budget For the Year Ended December 31, 2021 Quarter 2 3 4 Year 3,500 3.500 3,500 3 3.500 Total production units x 3 ounces per un Total production needs in ounces direct materials inventory 10.500 2.100 12,600 3.600 2.100 2.340 Less desired Ounces of begirming direct material inventory+ Total ounces of direct materials to be purchased X Cost per ounce Total cost of direct material purchases 10,500 S.10 x S1.OSO Note Budgeted production needs in ounces for the first quarter of 2022=18,000 ounces. *Desired ounces of ending direct materials inventory = 20% of next quarter's budgeted production needs in ounces. + Desired ounces of beginning direct materials inventory = 20% of current quarter's budgeted production needs in ounces. 4. Direct labor budget: 3500 0.1 7 2450 5. Manufacturing overhead budget: 4 Year Apple Inc. Manufacturing Overhead Budget For the Year Ended December 31, 2021 Quarter 1 2 3 Variable overhead costs Factory supplies (S.05) $ 175.00 Employee benefits (S.25) 875.00 Inspection ($.10) 350.00 Maintenance and repair (s.15) 525.00 Utilities (9.05) 175.00 Total variable overhead costs $ 2,100.00 Fixed overhead costs Depreciation, machinery Depreciation, building Supervision Maintenance and repair Other overhead expenses Total fixed overhead costs $ 500.00 700.00 1,800.00 400.00 600.00 $ 4,000.00 Total manufacturing overhead costs $ 6,100.00 The figures in parentheses are variable costs per unit. 6. Selling and administrative expense budget: Apple Inc. Selling and Administrative Expense Budget For the Year Ended December 31, 2021 Quarter 2 1 3 4 Year Variable selling and administrative expenses Delivery expenses (S.10) Sales commisions (S.15) Accounting (S.05) Other administrative expenses ($.20) Total variable selling and administrative expenses $ 350.00 525.00 175.00 700.00 $1.750.00 Fixed selling and administrative expenses Sales salaries Depreciation office equipment Taxes and insurance Total fixed selling and administrative expenses Total selling and and administrative expense $5,000.00 900.00 1,700,00 $ 7,600.00 $9,350.00 Note: The figures in parentheses are variable costs per unit. 7. Cost of goods manufactured budget: Apple Inc. Cost of Goods Manufactured Budget For the Year Ended December 31, 2021 Direct materials used Direct materials inventory, December 31, 2021 Purchases for 2021 Cost of direct materials available for use less: direct materials invnetory, December 20 2021 Cost of direct materials used Direct labor costs Manufacturing overhead costs Total manufacturing costs Work in process inventory, December 31, 2021 Less work in process inventory, December 31, 2021 Cost of goods manufactured *It is the company's policy to have no units in process at the end of the year. 8. Budgeted Income Statement: Apple Inc. Budgeted Income Statement For the Year Ended December 31, 2021 Sales Cost of goods sold Finished goods inventory, December 31, 2020 Cost of goods manufactured Cost of goods avaiable for sale Less finished goods inventory, December 31, 2021 Cost of goods sold Gross Margin Selling and administrative expenses Income from operations Income taxes expense (30%)* Net income Apple Inc. would like to have an estimate of the company's net income for 2021. Calculate Apple's net income in 2020 by completing the operating budgets and budgeted income statement that follow. 1. Sales budget: Apple Inc. Sales Budget For the Year Ended December 31, 2021 Quarter 2 3 3500 3500 4 3500 Year 14,000 Sales in units X Selling price per unit Total Sales 3500 5x S 17,500.00 X X X 2. Production budget: Apple Inc. Production Budget For the Year Ended December 31, 2021 Quarter 2 3 4 Year 1 3.500 600 600 Sales in units Plus desired units of ending finished goods inventory Desired total units Less desired units of beginning finished goods inventory 350 3.850 350 400 Total production units 3.500 *Desired units of ending finished goods inventory = 10% of next quarter's budgeted sales. +Desired units of beginning finished goods inventory = 10% of current quarter's budgeted sales. =
