Reference no: EM131093336

1. assumption 2. 2014 income statement

Title;

Best Practices to Improve Performance through Strategic Planning, Budgeting and Forecasting Case 

Prepare a flexible and master budget for Ford Motor Company, a Fortune 100 manufacturing company, from Ford's SEC 10K report.

II. Introduction

In this Master and Flexible Budget Comprehensive Case assume you have worked as a corporate accountant and created financial statements as part of a SEC 10-K report for a Fortune 500 company.  From that experience and other opportunities as a corporate accountant, you gained the confidence to apply to and be hired by a Fortune 100 company.  Your current position is in the budgeting department where you will prepare flexible budgets for a based on a hypothetical case.

Throughout this case, you may share your progress with not only your professor, but also with your classmates. Your professor will play the role of the controller with whom you discuss various issues and submit draft documents for approval in advance of submitting your final documents. Only the final submission will be graded.

To successfully complete this Case, you must demonstrate your knowledge of and ability to complete the following goals and competencies:

Goal 1:  Communication: Learners demonstrate ability to communicate clearly both orally and in writing.

Competencies:

1.1 Organize document or presentation clearly in a manner that promotes understanding

1.2 Develop coherent paragraphs or points so that each is internally unified and so that each functions as part of the whole document or presentation

1.3 Provide sufficient, correctly cited support that substantiates the writer's ideas

1.4 Tailor communications to the audience

1.5 Use sentence structure appropriate to the task, message and audience

1.6 Follow conventions of Standard Written English

Goal 2: Critical Thinking: Learners demonstrate ability to apply logical, systematic decision-making processes to formulate clear, defensible ideas and to draw ethical conclusions.

Competencies:

2.1 Articulate and frame the issue

2.2 Collect and evaluate information

2.3 Evaluate the underlying causes or conditions of elements contributing to an issue

2.4 Use systems thinking to arrive at a decision in the context of an issue

2.5 Apply ethical principles when determining actions.

Goal 3: Quantitative Reasoning: Learners demonstrate the ability to use mathematical operations and analytical concepts and operations to address problems and to inform decision-making

Competency

3.1 Construct models that represent real-world problems or processes
3.2 Develop visible representation of data 
3.3 Analyze data using mathematical/algebraic operations
3.4 Use calculated results to inform the problem or process

Goal 4: Leadership, Facilitation, and Collaboration:  Learners lead, facilitate, and collaborate with a variety of individuals and diverse teams to achieve organizational objectives.     

Competency

4.1 Demonstrate an ability to plan a particular objective or goal

Goal 5: Functional Competencies in Cost and Managerial Accounting

Competencies:

5.1 Measurement and reporting: Assign costs to firm's activities

5.2 Analyze and interpret business information: evaluate cost behaviors

5.3 Measurement and reporting: Prepare a master budget for a service or manufacturing company

5.4 Decision Making: Assist organizational leaders in understanding financial information, budgets, managerial reports and other analytical tools to support management control and decisions making

5.5 Effectively communicate relevant information and analyses based on specific reporting requirements of management. Specifically, you will demonstrate your competencies to:

Apply cost-volume profit analysis

Prepare a master and a flexible budget

Identify key underlying assumptions for a cash budget and budgeted balance sheet

Build a model using Excel to efficiently change the results when assumptions are changed.

Use publicly available industry information to evaluate the reasonableness of those assumptions

Defend the basic assumptions used in the master budget based on publicly available economic and industry reports

Identify characteristics of an effective budget system

Address an ethical dilemma

Graduate Accounting Program Goal 7: Technical Competencies in Management Accounting, Planning, Reporting, and Analysis:  Graduate Accounting Program Goal 7: Technical Competencies in Cost and Managerial Accounting.  Learners demonstrate an applied understanding of cost accounting, cost management, planning, forecasting, budgeting, capital expenditure, and incremental analysis to measure, monitor, and report organizational performance using various techniques including variance analysis and benchmarking.  Learners apply competencies via contextualized learning demonstrations to perform authentic professional tasks in cost and managerial accounting.

Competencies:

7.2 Analyzing and interpreting: learners demonstrate an applied understanding of how to analyze, plan, manage, and control costs to further support organizational strategies.

7.3 Measurement and reporting: learners demonstrate an applied understanding of business planning, budgeting, and costs forecasting to prepare a business plan congruent with an organization's strategic plans.

7.6 Benchmarking: learners demonstrate an applied understanding of how to compare  organizational processes, procedures, and performance to competitors and industry standards as a means to improve performance and adopt best practices.

After proving your competencies in financial accounting at Retail. com your passion for high fashion shifts from clothes to cars.  In your new role as a corporate accountant in the budget department, you are tasked with preparing a master and flexible budget for Ford Motor Company using Microsoft Excel.  The controller recommends starting with publicly available financial information from Ford's SEC 10-K in addition to information on industry trends to assess the degree to which management's assumptions are reasonable.

Ford Motor Company submits its annual SEC 10-K form toward the end of February or early March for the prior calendar year. Obtain the 2014 Ford SEC 10-K from the SEC Edgar website. The SEC Standard Industrial Classification (SIC) Code for Ford Motor Company is 3711 and its stock symbol is F.  You decide your first step is to read Ford's SEC 10-K to become familiar with its form, structure, and content.  (You will be able to compare you results with the 2015 actual results at the conclusion of the assignment) Given that you are only tasked with preparing a budget for the automotive segment, you'll take copious notes while reading that section of the 10-K.  Luckily, you find a resource on the SEC Website called How to Read a 10-K.  To stay organized, you decide to print an outline of a 10-K to keep by your side while preparing the budget.  Knowing the basic structure of a 10-K will expedite the process of finding information as you prepare the flexible budget.

The controller informs you that the budget process likely takes several months to complete and incorporates the input of many key individuals. Currently, the controller is only interested in the automotive division. The controller knows that external financial analysts will use the prior year-end data to make their own forecasts for the following year, and wants to prepare responses in anticipation of the next conference call with analysts. The controller asks the budget department, to prepare an income statement based on a separation of variable and fixed costs.  You may need to review your basic understanding of the concepts of fixed and variable costs,  See the lecture notes for resources. Related to cost-volume profit analysis and breakeven analysis.

In Milestone 1, the controller asks you to prepare a model of the annual master budget for the current year. To do so, you decide to use financial relationships from the prior year and add key assumptions (found below in the section entitled "steps to completion") underlying the following year.  Share the model with the controller for feedback. 

In Milestone 2, once the controller approves your budget model, change the assumption related to the increase in sales and then create a flexible budget. Be sure to identify and communicate the key assumptions underlying the following year's cash budget and budgeted balance sheet.

In Milestone 3, to understand better the budget process at Ford, first you reviewed the prior year's Statement of Cash Flow and Balance Sheet. You identify major changes that might occur during the current year.

Then you interviewed several mid-level managers. During conversations with your staff, you discover that several managers routinely submit unrealistic budget expectations to top management. As a result, you are now faced with a dilemma. As a professional accountant, and member of the Institute of Management Accountants, you must decide whether or not to communicate this information to the controller either before or at the same time you submit your report.

You must now compare your results to the 2015 actual for Ford's automotive division. 

You research the advantages and disadvantages of benchmarking with major competitors.

Your final report to the controller identifies the following items:

The key characteristics of an effective budget system.

The steps to prepare a master budget.

How a flexible budget will assist management in evaluating performance at the end of an accounting year.

III Steps to completion

NOTE; THERE IS A MODEL SPREADSHEET YOU MAY USE FOR THIS CASE. YOU MAY PREFER TO CREATE YOUR OWN SPREADSHEET.

Step 1 Milestone 1

Review the basics of budgeting. There are several resources provided in the lecture notes.

Step 2 Milestone 1- the Master (static) budget

Apply your understanding of cost-volume-profit analysis to prepare a 2015 forecasted income statement for the automotive division based on the model: sales - variable costs = contribution margin and contribution margin - fixed costs = operating profit

Use the EXCEL file identified a s Best Practices to Improve Performance through Strategic Planning, Budgeting and Forecasting Case Model

The master (static) budget consists of the following components:

a Sales budget

b. Production budget

c. Direct materials budget

d. Direct labor budget

e. Manufacturing overhead budget - separated into fixed and variable components

f. Cost of goods sold in a variable cost format (Variable cost of goods sold and fixed cost of goods sold)

g. Selling and administrative expense budget

h. The master (static) budget should be presented in the form of a contribution format.

Basic assumptions underlying the master (static) budget are as follows:

The estimate for sales in volume will increase by 10%.  The number of cars sold in 2014 is reported in the past year annual report found at www.ford.com. This will be the master budget volume for sales revenues. Assume all revenue in the automotive division is associated with cars.

Wholesale prices of cars are expected to increase by 0.5%.

The average production cost of an automobile is $21,000. Production costs are expected to increase by 1% in all categories.

There will be no change in inventory.

The Cost of Goods Sold (Cost of Sales) is comprised of direct materials 70%, direct labor 10% and variable manufacturing overhead of 10%, plus fixed manufacturing costs of 10%.  Fixed manufacturing costs will remain the same.

Selling, general, and administrative costs are 30% variable (based on sales volume) and 70% fixed. It is expected these costs will increase by 4%

Enter the assumptions in the first sheet of the EXCEL file.

Step 3 Milestone 1 - The Master (Static) Budget

You should determine the units sold in the prior year as well as the average selling price of the units sold. Record this information in your list of key assumptions. Identify any other assumptions you required to complete the Master budget. Record this information in the first sheet "assumptions" Use the Automotive Segment statements, which can be found in the 2014 annual report. Enter the data from the 2014 form 10K in the second sheet "2014 income statement)

You should determine the units sold in the prior year as well as the average selling price of the units sold. Record this information in your list of key assumptions. Identify any other assumptions you required to complete the master budget. Label this sheet "assumptions"

The second sheet should contain the operating income statement for the automotive division's current year that follows (1) the publicly available income statement in the traditional format and (2) reformatted income statement n the contribution format showing the sales -variable costs = contribution margin -fixed costs model = operating income.  Enter this information in the second sheet "2014 income statement".

The third sheet, prepare the master budget along with the supporting schedules for the six schedules in step 2. Be sure to insert formulas in the EXCEL cells. Your model should be created so that if any of the assumptions listed change, your results will automatically change. Use formulas. Be sure to let EXCEL perform all of the math functions. Remember, you are building a model than which will automatically change the results when new assumptions are entered. Label your as "Master Budget"

You may need to review or enhance your EXCEL skills.  The tutorial for building a model in EXCEL is very useful.

Step 4- The Flexible Budget - Milestone 2

The controller would also like you to prepare a flexible budget. This will be sheet #4. Label the sheet "flexible budget". .  Use the same format as you did for the master budget for each of the requirements. Use cell references and formulas. Remember, if any assumption changes, your spreadsheet should change automatically.

In sheet #5, copy all of your analysis so that the Controller can compare your master budget calculations with the revised flexible budget figures. Again use cell references.

Use the following assumptions for the flexible budget:

Sales volume will increase by 10%

Sales prices will increase by 5%

Average unit cost of an automobile will decrease by 2%

Selling and administrative variable costs will increase by 5%

Selling and administrative fixed costs will decrease by 10%

Production will increase by 10%

There will still be no change in inventory.

List the flexible budget assumptions on the in sheet #1 "assumptions".

In sheet #5, copy all of your analysis so that the Controller can compare your master budget calculations with the revised flexible budget figures. Again use cell references. Provide a comparison of the master budget operating income statement (variable cost format) with the flexible budget operating income statement (on a variable cost format)

Steps 5 through 7  - Submit using one WORD document - Milestone 3

Step 5 -  Prepare memo #1 - Evaluate the Original AssumptionsI n the Budget to Publicly Available information-

In this part of the project, you will critically assess the assumptions the controller gave you to develop the master budget. To prepare for this step, you should review the basics of budgeting. 

Further, review an overview of the master budgeting process in the following resource provided in the lecture notes.

. Review publicly available data related to the assumptions concerning sales volume, sales prices, cost of sales and selling, general and administrative costs for the automobile industry in general and for Ford in particular.  You may want to search the Web for automotive industry reports that were relevant at the beginning of the forecast period. Your analysis will be presented as a memo to the Controller.

Step 6 - Prepare Memo #2 - Memo to Management on an Ethical Issue - Milestone 2 -continue with the WORD document in Step 5

To prepare for this step, the controller advises you to review the IMA's Statement of Ethical Professional Practice.  Assume in researching the sales forecast for the current year, you determine the master budget the controller asked you to use assumptions are significantly flawed. The likely outcome of correcting these flawed assumptions would be significantly lower profits than shown in the master budget. You know the controller wants to present the forecast of higher earnings to external financial analysts. Prepare a memo to the Controller indicating how you would address this issue.

Step 7-  Prepare Memo #3 - Use the EXCEL worksheet to present the comparison of actual to the budget that you created and a WORD document for the narrative discussion.

Obtain the actual 2015 operating income for Ford's Automotive Division.

In your EXCEL file, compare the actual 2015 results to the Master Budget you created.  Label the sheet "Comparison of MB to Actual." (sheet #6)

After comparing your Master Budget results to the 2015 actual for Ford's automotive division. discuss in memo #3, why the actual results differ from expected. Be specific about the impact of the economy and competition.

Steps 8, 9 and 10 - Continue with your Word Document- Milestone 4

Step 8 Prepare Memo # 4 Review of the Cash Budget -Milestone 4
Review the 2014 Statement of Cash Flow budget. Based on your research, write a report that indicates whether you believe the cash from operations will increase or decrease. In addition, predict the increase or decrease in cash from investing and financing activities. Identify specific accounts that are likely to increase or decrease and explain why you believe there will be differences from the prior year. Provide the basis for your conclusions in a memo to management. Include your references.

Step 9 Prepare  Memo #5 - Review of the Balance Sheet - Milestone 4

Review the 2014 balance sheet. Based on your research, include in your report whether or not you believe inventory, accounts receivable, and accounts payable will increase or decrease. Explain why. Predict whether long-term debt will increase or decrease.  Also, predict whether Ford will increase, decrease, or maintain current dividends to shareholders as in the past year. Provide the basis for your conclusions. Include your references. Your analysis will be part of your final report.

Step 10 - Prepare Memo # 6 Competitive Analysis and Benchmarking: - Milestone 4

Review the advantages of benchmarking. Ford competes with both domestic and foreign automobile companies. Review the annual reports of Toyota and Honda. Select three (3) key performance measures. At least one performance measure is a financial measure. Assess how Ford automotive performs compared to these two international companies.  Consider whether or not Toyota and Honda use international accounting standards to prepare their financial statements.  Determine if the data you used is comparable. Write a memo to management that (1) explaining how benchmarking assists management and (2) summarizes your analysis and conclusion. Include your assessment of whether or not the international financial statements you used allowed you to make meaningful comparisons. Identify the limitations

Step 11 - Prepare Memo #7 An Effective Budget System,  Your final memo to the controller identifying the following items:

A The key characteristics of an effective budget system.

B The steps to prepare a master budget.

C How a flexible budget will assist management in evaluating performance at the end of an accounting period.

Submit your final documents to the dropbox by the due date.

IV. Deliverables:

A. Worksheet  You will submit one Excel file including the following six sheets:

Sheet 1 - Basic Assumptions;

Basic assumptions underlying the master (static) budget are as follows:

The estimate for sales in volume will increase by 10%.  The number of cars sold in 2014 is reported in the past year annual report found at www.ford.com. This will be the master budget volume for sales revenues. Assume all revenue in the automotive division is associated with cars. Wholesale prices of cars are expected to increase by 0.5%.

The average production cost of an automobile is $21,000. Production costs are expected to increase by 1% in all categories. There will be no change in inventory.

The Cost of Goods Sold (Cost of Sales) is comprised of direct materials 70%, direct labor 10% and variable manufacturing overhead of 10%, plus fixed manufacturing costs of 10%.  Fixed manufacturing costs will remain the same.

Selling, general, and administrative costs are 30% variable (based on sales volume) and 70% fixed. It is expected these costs will increase by 4% Sheet 2 - The 2014 income statement for the automotive division in (a) the traditional format and (b) in the contribution format