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.

      

Transcribed Image Text:

3. Direct materials purchases budget: Apple Inc. Direct Materials Purchases Budget For the Year Ended December 31, 2021 Quarter Total production units x 3 ounces per unit Total production needs in ounces direct materials inventory Less desired ounces of beginning direct materials inventory + Total ounces of direct materials to be purchased X Cost per ounce Total cost of direct material purchases X I 4. Direct labor budget: 3,500 3500 0.1 7 2450 3 x 10,500 2,100 12,600 2,100 10,500 $.10 x $1,050 2 3,500 x 3 3,500 x 4 3,500 3,600 X Year Note Budgeted production needs in ounces for the first quarter of 2022-18,000 ounces. 2.340 *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. 3. Direct materials purchases budget: Apple Inc. Direct Materials Purchases Budget For the Year Ended December 31, 2021 Quarter Total production units x 3 ounces per unit Total production needs in ounces direct materials inventory Less desired ounces of beginning direct materials inventory + Total ounces of direct materials to be purchased X Cost per ounce Total cost of direct material purchases X I 4. Direct labor budget: 3,500 3500 0.1 7 2450 3 x 10,500 2,100 12,600 2,100 10,500 $.10 x $1,050 2 3,500 x 3 3,500 x 4 3,500 3,600 X Year Note Budgeted production needs in ounces for the first quarter of 2022-18,000 ounces. 2.340 *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.

Answer rating: 100% (QA)

Creating a master budget involves several interconnected budgets and Excel is a great tool for this task Below are the steps for setting up the budgets and a brief discussion of the assumptions and potential challenges Step 1 Setup Individual Budgets Sales Budget Manually calculate the sales forecast for each quarter Total column should sum up the quarterly values Production Budget Linked to the Sales Budget Assume the production units based on sales and ending inventory requirements Direct Materials Purchases Budget Linked to the Production Budget Calculate the materials needed for production Direct Labor Budget Linked to the Production Budget Calculate the labor hours required for production Manufacturing Overhead Budget Linked to the Production Budget Include variable and fixed overhead costs Selling and Administrative Expenses Budget Estimate these costs Link to the Sales Budget for commissions or other variable costs Cash Budget Combine information from various budgets including collections and payments Budgeted Income Statement Summarize data from the above budgets adding taxes and interest Step 2 Separate Tabs Create individual tabs for each budget to keep the information organized Use Excel formulas and functions to link related cells between budgets Step 3 Professional Presentation Format each budget for a professional look using clear headings proper font and cell formatting Utilize Excel features for better presentation like conditional formatting and data validation Step 4 Assumptions and Challenges Assumptions Assume steady sales growth or consider historical data for sales forecasting Assume a constant production efficiency and cost structure unless provided with changes Estimate selling and administrative expenses based on historical data or industry benchmarks Assumptions may also include credit terms for customers inventory turnover and production lead times Challenges Changing assumptions especially in sales forecasts may affect other budgets Ensure formulas are set up correctly to capture these changes Balancing production with demand to avoid overproduction or stockouts Identifying and including all relevant costs in each budget Handling seasonality or market changes that may impact sales and production Conclusion Creating a master budget in Excel requires careful planning attention to detail and the ability to link various budgets seamlessly Regularly update and review the assumptions to reflect the dynamic nature of business environments The presentation of the budget ... View the full answer