McQueen Corporation is preparing the budgeted financial statements for the year 2015. The actual balance sheet as of

McQueen Corporation is preparing the budgeted financial statements for the year 2015. The actual balance sheet as of

12/31/14 follows:

Assets Liabilities & Shareholders Equity

Current assets Current liabilities

Cash and equivalents $48,230,500 Accounts payable $30,000,000

Short term investments $45,050,000 Warranties $2,764,000

Net receivables $11,456,000 Accrued expenses $2,210,000

Inventories $11,250,000 Revolving line of credit $0

Other current assets $6,843,000 Other current liabilities $1,348,000

Total current $122,829,500 Total current $36,322,000

Non-current liabilities

Non-current assets Long term debt $70,000,000

Long term investments $64,257,000 Total non-current $70,000,000

Property plant and equipment $46,124,000 Total liabilities $106,322,000

Other assets $7,985,000

Total non-current $118,366,000

Total assets $241,195,500 Shareholders equity

Common stock $9,266,000

Capital surplus $9,967,000

Treasury stock -$15,219,000

Retained earnings $130,859,500

Total shareholders equity $134,873,500

Total liabilities & shareholders equity $241,195,500

McQueen Corporation

Balance Sheet

Year end 12/31/2014

McQueen Corporation operates many divisions. One of their divisions, “Take Flight” sells three models of a luxury

automobile built for speed. (Assume all information, financials and data provided pertain to the Take Flight division.)

The Falcon is the largest version which boasts seating for four but still has the ability to reach top speeds in seconds.

The Swift is the top seller with its sleek design, modern technologies, and seating for two. The Gazelle is the smallest

model very similar to the Swift but slightly faster. McQueen’s automobiles are highly specialized and they are only sold

in Dallas, Texas. People travel from all over the world to see and purchase McQueen’s automobiles but the President is

unhappy with the current profit margins, or lack of, for the Take Flight division.

McQueen Corporation is exploring new branding and marketing ideas in an effort to increase business for the Take Flight

division. In doing so, the President has asked the Chief Financial Officer, the Chief Development Officer, the Chief

Marketing Officer, and you, the business consultant for input and ideas on how to increase profit margins.

If the company does nothing different for 2015, the President expects the following sales:

Model Sales Price Units Sold Total

Falcon $1,500,000 50 $75,000,000

Swift $1,125,000 150 $168,750,000

Gazelle $750,000 100 $75,000,000

Based upon historical data, 85% of sales revenue is collected in the year of the sale with 5% collected in the year

following the sale. 10% of sales is the estimated bad debt amount. In following GAAP, McQueen makes this estimate at

year end and records the expense in the year of sale. The net receivables balance on 12/31/2015 consists of the 5%

uncollected balance of 2015 sales.

On January 1, 2015, beginning inventory is 5 Falcons, 5 Swifts, and 5 Gazelles. Desired ending inventory for 2015 is 10

units of each model. McQueen outsources the manufacturing of their automobiles and purchases the finished product.

The Falcon cost is $1,000,000, the Swift cost is $750,000, and the Gazelle cost is $500,000. These costs apply to both

2014 and 2015. The current sales prices are based on cost plus a 50% markup. Based upon historical data, 85% of

inventory is paid for in the year of purchase with the remaining 15% paid for in the year following the purchase. The

accounts payable account is used specifically for inventory purchases.

The accounts payable balance on 12/31/14 consists of the 15% unpaid balance of 2014 inventory purchases.

Budgeted fixed administrative expenses for 2015 follow:

Utilities, maintenance, and facilities $850,000

Executive and administrative salaries $9,000,000

Depreciation $2,432,000

Budgeted variable administrative expenses for 2015 follow:

Marketing and promotions 10% of Sales

Bad debt expense 10% of Sales

Sales commission 3% of Sales

Warranties 3% of Sales

Shipping and handling costs 1% of Sales

Insurance 3% of Purchases

Of the above payable fixed and variable expenses everything except warranties will be paid in cash during 2015.

Remember, the depreciation and bad debt expense are non-cash expenses. Based upon historical data, 100% of

warranty claims are paid in the year following the sale. The warranty liability on the 12/31/2014 balance sheet is the

estimate for 2014.

The three other current liabilities (accounts payable, accrued expenses, and other current liabilities) on the 12/31/2014

balance sheet will be paid with cash during 2015.

5% of the 2015 beginning long-term debt balance will be paid with cash during 2015. At year end, interest is accrued at

an annual rate of 12% but is not paid until the following year.

McQueen has a $20,000,000 minimum cash balance policy. If the year-end cash balance falls below $20,000,000

McQueen will utilize their revolving line of credit with the bank and borrow the amount necessary to meet the

$20,000,000 cash requirement. (This requires an “if, then” formula in excel and is provided for you on the budgets tab,

cell B90.)

McQueen budgets an annual 3% return on short-term investments and an annual 7% return on long-term investments.

McQueen receives these returns in cash at year-end.

McQueen plans to pay out a dividend of 15% of net income on 12/31/2015. (“IF, THEN” formula provided in excel

template, budgets tab, cell B86.)

McQueen’s pretax income is budgeted to be taxed at a rate of 20% and will be paid in cash during 2015. If a net loss

occurs, assume 0 income tax. (“IF, THEN” formula provided in excel template, budgets tab, cell B87.)

Requirements:

Using the data above, prepare a master budget for McQueen Corporation including the following. Use the excel

spreadsheet provided.

a. Data sheet

b. Revenue budget for the year ending 12/31/2015

c. Purchases budget for the year ending 12/31/2015

d. Fixed administrative expense budget

e. Variable administrative expense budget

f. Interest expense budget

g. Schedule of cash collections on sales

h. Schedule of cash collections on investments

i. Schedule of cash payments for inventory

j. Schedule of cash payments for warranties

k. Cash budget for the year ending 12/31/2015

l. Budgeted income statement for 12/31/2015

m. Budgeted cost of goods sold schedule for the year ending 12/31/2015

n. Budgeted statement of retained earnings for the year ending 12/31/2015

o. Budgeted balance sheet for the year ending 12/31/2015

1. Input all of the above estimates proposed by the President if the company does nothing to change the Take

Flight division. Note: once you have your excel spreadsheet set up properly (see below), you will only need to

make adjustments to your input sheet and everything will flow through. Include the President’s numbers in the

first tab of your excel file and compute the ratios before moving to the CFO. Prepare a memo to the President

discussing the current operations of the Take Flight division based on the budgeted numbers. Is it profitable?

Why or why not? Discuss operating income vs. net income.

Organize the assignment as follows:

A. Use the first tab as your title page. The title page SHOULD NOT have any linked cells. Your budgets and financial

statements will change depending on your input sheet. Once you change your input values for each of the five

options, your budgets and financial statements will change accordingly after you link everything correctly. Take

the values generated and put the hard numbers on the title sheet.

B. The second tab is the financial statements for 12/31/2014 shown above and should not be changed. You will

use these numbers to link for beginning balances.

C. The third tab is your input sheet. All of the lines are provided for you, but you need to input the values in the

highlighted cells.

D. The fourth tab is your budgets sheet. The lines are provided for you, but it is your responsibility to link the cells

correctly. This includes b-k above. (No hard numbers*) Once your spreadsheet is linked correctly, you should

NOT need to adjust this tab. You will ONLY make changes to the “Data” tab and everything else should flow

through.

E. The fifth tab is your projected financial statements including linked cells to budgets sheet and input sheet.

Again, the lines are provided for you, but it is your responsibility to link the cells correctly. This will include l-o

above. (No hard numbers*) Again, once your spreadsheet is linked correctly, you should NOT need to adjust this

tab. You will ONLY make changes to the “Data” tab and everything else should flow through.

F. The sixth tab will be your ratio computations. Link to the other tabs in the spreadsheet to calculate the correct

ratios.

IMPORTANT NOTE: Once you set the spreadsheet up correctly, you will be able to change minor items on the data

sheet to see how it affects the budgeted financial statements, ratios, etc. This is a true method of “what-if” analysis.

You can ask yourself, “what if we increase mark up by x amount?” Or “what if we expand to this many locations?”

“What if we change these expenses?” Etc. With just a few changes to the data tab, you can see how this will affect

everything! Play with the spreadsheet once you have it set up and look at the big picture. This will help in your

analysis and recommendations to the President.

IMPORTANT NOTE 2: You will turn in your analysis Word/pdf document AND your Excel spreadsheet. I want the

spreadsheet that you turn in to have the President’s projected numbers for the Take Flight division assuming no

changes are made. You will make several changes to the data tab throughout the project and your analysis, but

change it back to the President’s suggestions before submitting it. I will provide check figures before the project is

due so you can ensure your spreadsheet is correct, and the check figures will be based on the President’s estimates.

G. A separate word document will be the discussion and your proposals/memos to the President. The word

document will include a memo to the President based on current budget and memos from the CFO, CDO, CMO,

and business consultant. You should have 5 separate memos in your word document.

*The budgets sheet and the financial statement sheet cannot have any hard numbers. Each cell should link to

another cell within the workbook or should have an equation.

When completing the suggestions below, you will include the numbers generated in the first tab of your excel

file and compute the ratios before moving on to the next suggestion.

2. The Chief Financial Officer (CFO) suggests that the long-term debt be paid down by an additional $30,000,000 by

liquidating some of the company’s interest bearing short-term investments. The CFO also recommends using an

additional $30,000,000 of cash to pay down the long-term debt leaving the company with only $6,500,000 of

long-term debt. The CFO also suggests that $10,000,000 of the short-term investments be moved to long-term

investments to earn a higher interest since the company’s current assets are quite high. This should improve the

profit margin and allow the Company the flexibility needed to explore new branding and marketing ideas.

Prepare a memo to the President from the CFO analyzing the results of his proposed changes. This should

include the ratio results and a discussion of cash position.

The President would like input from his CDO and CMO on possible expansion into new locations and/or an increase in

the markup on costs. The President has prepared estimates shown below if expanding to a new location were to take

place and/or if the markup on costs for the sales price of the products is raised from the current 50%.

Expansion per location will increase the number of cars sold in each category by 30%.

For each 10% increase in markup on costs, the number of cars sold in each category will decrease by 30%.

Budgeted fixed administrative expenses for future planning purposes:

Utilities, maintenance, and facilities $150,000 increase per location. **

Executive and administrative salaries $1,400,000 increase per new location. **

Depreciation $20,000 increase per new location. **

Budgeted variable administrative expenses follow for future planning purposes:

Marketing and promotions 0.2% increase of total sales per new location

and/or 0.4% increase of total sales for each 10%

increase in the markup on costs.

Bad debt expense No change.

Sales commission No change

Warranties No change.

Shipping and handling costs 0.1% increase of total sales per new location.**

Insurance No change.

**no change for increase in markup on costs.

3. Assume you are hired as the CDO. The Chief Development Officer (CDO) has a comprehensive knowledge of all

matters related to the business of the organization with an eye towards identifying new sales prospects and

driving business growth and expansion. The CDO is exploring expansion options. Prepare a proposal from the

CDO to the President. Play with the numbers above and determine whether or not they should expand. If they

should, how many new locations will be most beneficial?

4. Assume you are hired as the CMO. The Chief Marketing Office (CMO) is responsible for facilitating growth, sales

and marketing strategy. He or she must work towards objectives such as revenue generation, marketing

communications (including advertising and promotions), pricing, market research, and customer service. The

CMO is exploring a reduction in markup. Prepare a proposal from the CMO to the President. Play with the

numbers above and determine whether or not they should increase their markup on costs. If so, by how much?

Why?

Be sure to compute the following ratios for the President, CFO, CDO, and CMO: gross margin percentage, operating

income percentage, debt to equity, debt to assets, current ratio, inventory turnover, asset turnover, and net profit

margin. Include the ratio results in the first tab of your excel file AND discuss the ratios in your analysis.

5. Now assume you are the President’s business consultant. After you have reviewed the four options above,

prepare your recommendation to the President as the best proposal for the Company’s future performance.

This should be a combination of changes, not choosing one of the four above. As part of your proposal include a

brief discussion of the above ratio results and include them on the first tab of your excel file.