I need help with the following Accounting problem. Do not know how to go about completing this milestone 1 in a excel file. I have an excel file I can send. Not sure how to go about starting this assignment. Any help will be appreciated.

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

II. Introduction

For the past few years, you have worked as a corporate accountant at Retail.com and created financial statements as part of a SEC 10-K report for this Fortune 500 company.After proving your competencies in financial accounting at Retail.com, your passion for high fashion shifts from clothes to cars.From the experience at Retail.com, you gained the confidence to apply to and be hired by a Fortune 100 company, Ford Motor Company.

You're thrilled to have been hired as the corporate accountant for Ford.Your new boss, Violet Andover (your professor) is highly respected in the Budget and Planning Department.She is demanding yet always willing to answer questions and teach new employees "the Ford way" of preparing various form and reports.While standing at the water cooler, Anna reminded you that she has an open-door policy and to stop in for help whenever needed.

In last week's monthly meeting, Violet tasked you with preparing a master and flexible budget using Microsoft Excel.She provided you with an Excel spreadsheet, titledBest Practices to Improve Performance Through Strategic Planning,hereafter referred to as EXCEL.She expects you to use this EXCEL spreadsheet to complete this task. Further, Violet informs you that the budget process likely takes several months to complete and incorporates the input of many key individuals.Currently, she is only interested in theautomotive division.Since this is such a big process, Violet has broken down the deliverables she expects into 6 milestones.

Over the course of creating the deliverables, Violet asked that youshare your progress with her and other colleagues (classmates) in the Budget and Planning Department.Knowing that your deliverables will be one part of a larger report the CFO will receive, gives you a real sense of the importance of doing your best.Violet explains;the entire budgeting team (all classmates) will discuss various issues and submit draft documents for approval in advance of submitting their individual final documents to me.Violet reassures you by saying;you are not alone.This is a big project but you will have plenty of support along the way.

Your colleague Rich offers this wise advice:be conscious of due dates, even for drafts because Violet is always on a tight deadline and takes due dates very seriously.Further,Rich warns that if you do not submit the deliverables (including drafts) by Violet's due dates, your performance evaluation will be negatively impacted, which will impact any potential salary increase. Knowing how much you really need a new car, you vow to meet every deadline Violet sets. Rich recalls a time when he submitted budgets to Violet one day late and she reduced the score for timeliness on his annual performance review by 10%.When he was two days late, she reduced his timeliness score to 0!

III. Steps to completion

Step 1:

The controller recommends starting with publicly available financial information from Ford's SEC 10-K in addition to information on industry trends and the economy to assess the degree to which management's assumptions are reasonable.

Obtain and read the 2017 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 will need to become familiar with its form, structure, and content.(You will be able to compare you results with the 2018 actual results at the conclusion of the project).

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.Knowing the basic structure of a 10-K will expedite the process of finding information as you prepare the flexible budget.

Step 2:

You will be applying your understanding of cost-volume-profit analysis to prepare a 2018 forecasted income statement for the automotive division based on the model: sales - variable costs = contribution margin and contribution margin - fixed costs = operating profit.

Appendix A - Milestone 1 (worth 10%)

In Milestone 1, the controller asks you to prepare a model of the annual master budget for the year 2018.The goal of Milestone 1 is to prepare a master budget for 2018 in the contribution format, which you will find in sheet #3.As you complete the sheet for the master budget, do not enter any numbers directly. Rather, use cell references and formulas.

A note about obtaining information about the automobile's segment income:

It certainly would have been easier for an analyst or investor if Ford continued its prior practice to provide a segment income statement.When the data is not in the format one needs for financial analysis, there are a few assumptions that need to be made.

In the form 10K, item 7, you will find the FY Key Metrics - units produced, revenues and the operating margin. You will also find the consolidated income statement - the cost of sales only refers to the auto division. However, just as the 2017 information was presented, the SGA expenses refer to both the auto and finance segments.

An example of how the 2017 SGA for automotive was determined in sheet #6 of the EXCEL is provided for you.. You are expected to apply the same analysis to determine the 2018 SGA for automotive.

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

a.Schedule 1:Sales budget

b.Schedule 2:Production budget

c.Schedule 3:Direct materials budget

d.Schedule 4:Direct labor budget

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

f.Schedule 6:Selling and administrative expense budget

g.Schedule 7:Cost of goods sold in a variable cost format (Variable cost of goods sold and fixed cost of goods sold)

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

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

1.In 2017, 6.607 million units were sold. We will assume these are all cars. The estimate for sales in volume will increase by 10%. This will be the master budget volume for sales revenues. Assume all revenue in the automotive division is associated with cars.

2.Assume the Cost of Goods Sold (Cost of Sales) is 90% of the total CGS comprised of direct materials 70%, direct labor 10% and variable manufacturing overhead of 10%, In addition, fixed manufacturing costs are 10% of the total CGS.Remember, variable costs increase as a result of volume as well as price and cost changes.

3.Assume the average selling price is $23,500.

4.Assume selling, general, and administrative costs (SG&A) are 30% variable (based on sales volume) and 70% fixed. The SG&A expenses associated with the auto segment is $10,936.

Additional assumptions for the master budget developed by the Controller::

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

6.Variable production costs per unit are expected to increase by 1%. Fixed production costs will increase by 2%.

7.There will be no change in inventory. Assume the beginning inventory consisted of 400,000 units and the ending inventory includes the same costs as the beginning inventory. You will need to included beginning and ending inventory in the Cost of Goods Sold schedule.

8.Selling general and administrative variable per unit costs will increase by 6%. Fixed selling, general and administrative costs will decrease by 5%. Selling, General and administrative variable costs vary by the units sold.

Begin with the actual 2017 results found at www.sec.gov form 10K for Ford (F). Use the interactive data for the year 2017 filed in February 2018. Selected segment income information begins on page 38 of the form 10K. However, the information does not provide a complete segment margin income statement.This data is provided to you in the EXCEL on sheet #1.

Some of this information found on sheet #1 may be slightly different from the data in the form 10K. You will need to record the basic assumptions in the case relating to the percentages associated with direct labor, direct materials, overhead, change in sales, variable and fixed costs for manufacturing and selling, general and administrative expenses. Enter this information in to sheet #1 labeled assumptions. Again, you must use this EXCEL file. Be sure to use cell references and formulas wherever possible.

The number of automotive units sold in the prior year was 6,651 million. Note that the SGA is less than the amount recorded in the financial statements. Only the amount estimated for the auto segment is included in the EXCEL.

Create the 2017 income statement for the automotive division in sheet 2 (2017 income statement) You will need to recast the income statement into the contribution format.Note:The given information is in the traditional format so you must reformat the income statement in the contribution format showing the sales -variable costs = contribution margin -fixed costs model = operating income. Enter this information in the second sheet "2017 income statement- contribution format".

Identify any other assumptions you require to complete the Master Budget. Record this information in the first sheet "assumptions".On the 3^rd sheet, prepare the master budget along with the supporting schedules for the six schedules listed above. 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. Be sure to let EXCEL perform all of the math functions. Remember, you are building a model which will automatically change the results when new assumptions are entered.

You will submit Milestone 1 as a draft first to the assignment folder. The Controller will provide feedback and allow you to revise Milestone 1 the following week.

After you revise the worksheet, (or if your worksheet needs no revisions) MARK YOUR MILESTONE 1 FINAL SUBMISSION to be evaluated. Milestone 1 is graded one week after it is due.