Can some one help me do this master budget excel problem. I have the excel file and the question attached thank you

Budgeted Net Income Budgeted Cost of Goods Sold Budgeted Total Assets Budgeted Cash Balance President CFO CDO CMO President's Estimates CFO's Estimates CDO's Estimates CMO's Estimates Business Consultant's Estimates (You) Gross margin percentage Operating income percentage Debt to equity Debt to assets Current ratio Inventory turnover Asset turnover Net profit margin Budgeted Short Budgeted Long Term Investments Term Investments Business Consultant Budgeted Budgeted Long Revolving Line Term Debt of Credit McQueen Corporation Balance Sheet Year end 12/31/2014 Assets Current assets Cash and equivalents Short term investments Net receivables Inventories Other current assets Total current Non-current assets Long term investments Property plant and equipment Other assets Total non-current Total assets $48,230,500 $45,050,000 $11,456,000 $11,250,000 $6,843,000 $122,829,500 Liabilities & Shareholders Equity Current liabilities Accounts payable Warranties Accrued expenses Revolving line of credit Other current liabilities Total current Non-current liabilities Long term debt Total non-current Total liabilities $64,257,000 $46,124,000 $7,985,000 $118,366,000 $241,195,500 Shareholders equity Common stock Capital surplus Treasury stock Retained earnings Total shareholders equity Total liabilities & shareholders equity $30,000,000 $2,764,000 $2,210,000 $0 $1,348,000 $36,322,000 $70,000,000 $70,000,000 $106,322,000 $9,266,000 $9,967,000 -$15,219,000 $130,859,500 $134,873,500 $241,195,500 2015 Budget Information Falcon Swif Gazelle Estimated sales price Estimated sales volume Inventory unit cost Beginning inventory volume Budgeted ending inventory volume % of cash collections - current sales % of cash collections - previous year's sales Ending net A/R balance % of sales % of 12/31/2014 net receivables % of sales Cash payments for inventory - current purchases Cash payments for inventory - previous year's purchases Ending A/P balance % of cost % of 12/31/2014 accounts payable % of cost Budgeted Fixed Administrative Expenses: Utilities, maintenance, and facilities Executive and administrative salaries Depreciation Budgeted Variable Administrative Expenses: Marketing and promotions Bad debt expense Sales commission Warranties Shipping and handling costs Insurance % of sales % of sales % of sales % of sales % of sales % of inventory cost Sale of short-term investments Purchase of long-term investments Long-term debt payment Additional long-term debt payment Interest expense % of beginning of year balance % of beginning of year balance afer payment Previous year warranty estimate (2013) Cash payments for warranties Cash payments for accounts payable, accrued expenses, other current liabilities Cash payment for dividends Cash payment for income tax % of previous year % of balance % of net income % of income before tax Cash received for short-term investmensts Cash received for long-term investmensts % of balance % of balance Minimum cash balance Revenues Budget for the year ending 12/31/2015 ALL CELLS WILL LINK TO OTHER TABS OR WILL HAVE FORMULAS! Units Falcon Swif Gazelle Total Selling Price Total Revenues Swif Gazelle Purchases Budget for the year ending 12/31/2015 Falcon Sales units + Target ending inventory Total requirements - Beginning inventory Purchases to be made Unit Cost Purchases Fixed Administrative Expense Budget Utilities, maintenance, and facilities Executive and administrative salaries Depreciation Total fixed expenses Variable Administrative Expense Budget Marketing and promotions Bad debt expense Sales commission Warranties Shipping and handling costs Insurance Total variable administrative expenses Interest Expense Budget Beginning long-term debt balance Payments on long-term debt Ending long-term debt balance Interest expense Schedule of Cash Collections on Sales Beginning accounts receivable Current year collections Total cash collections on sales Schedule of Cash Collections on Investments Beginning balance short-term investments Liquidate short-term investments Ending balance short-term investments Beginning balance long-term investments Total Purchase of long-term investments Ending balance long-term investments Short-term investments Long-term investments Total cash collections on investments Schedule of Cash Payments for Inventory Beginning accounts payable Current year payments Total cash payments for inventory Schedule of Cash Payments for Warranties Cash payments for warranties - previous year's estimate Total cash payments for warranties Cash Budget for the year ending 12/31/2015 Cash balance, beginning Add receipts Collections from customers Sale of short-term investments Collections from investments Total cash available Deduct disbursements Cash payments for inventory Utilities, maintenance, and facilities Executive and administrative salaries Marketing and promotions Sales commission Warranties Shipping and handling costs Insurance Accrued expenses and other Long-term debt Purchase of long-term investments Dividends Income tax Total disbursements Projected ending cash balance Borrowing required Projected ending cash balance afer borrowing $0 $0 $20,000,000 $20,000,000 ALL CELLS WILL LINK TO OTHER TABS OR WILL HAVE FORMULAS! McQueen Company Budgeted Income Statement Year end 12/31/2015 Sales Cost of goods sold Gross margin Administrative expenses Operating income Interest expense Other income Income before taxes Income taxes Net income McQueen Company Budgeted Cost of Goods Sold Schedule Year end 12/31/2015 Beginning Inventory Add Purchases Cost of goods available for sale Less ending inventory Cost of goods sold McQueen Company Budgeted Statement of Retained Earnings Year end 12/31/2015 Beginning balance of retained earnings +/- Net income/loss - Dividends Ending balance of retained earnings McQueen Company Budgeted Balance Sheet 12/31/2015 Assets Current assets Cash and equivalents Short term investments Net receivables Inventories Other current assets Total current Non-current assets Long term investments Property plant and equipment Other assets Total non-current Total assets $0 $0 $0 Liabilities & Shareholders Equity Current liabilities Accounts payable Warranties Accrued expenses Revolving line of credit Other current liabilities Total current $0 Non-current liabilities Long term debt Total non-current Total liabilities $0 $0 Shareholders Equity Common stock Capital surplus Treasury stock Retained earnings Total shareholders equity Total liabilities & s/h equity $0 $0 LINK TO OTHER TABS FOR THE CORRECT RATIO COMPUTATION! Gross margin percentage Operating income percentage Debt to equity Debt to assets Current ratio Inventory turnover Asset turnover Net profit margin McQueen Corporation is preparing the budgeted financial statements for the year 2015. The actual balance sheet as of 12/31/14 follows: McQueen Corporation Balance Sheet Year end 12/31/2014 Assets Current assets Cash and equivalents Short term investments Net receivables Inventories Other current assets Total current Non-current assets Long term investments Property plant and equipment Other assets Total non-current Total assets $48,230,500 $45,050,000 $11,456,000 $11,250,000 $6,843,000 $122,829,500 Liabilities & Shareholders Equity Current liabilities Accounts payable Warranties Accrued expenses Revolving line of credit Other current liabilities Total current Non-current liabilities Long term debt Total non-current Total liabilities $64,257,000 $46,124,000 $7,985,000 $118,366,000 $241,195,500 Shareholders equity Common stock Capital surplus Treasury stock Retained earnings Total shareholders equity Total liabilities & shareholders equity $30,000,000 $2,764,000 $2,210,000 $0 $1,348,000 $36,322,000 $70,000,000 $70,000,000 $106,322,000 $9,266,000 $9,967,000 -$15,219,000 $130,859,500 $134,873,500 $241,195,500 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 Falcon Swift Gazelle Sales Price $1,500,000 $1,125,000 $750,000 Units Sold 50 150 100 Total $75,000,000 $168,750,000 $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 Executive and administrative salaries Depreciation $850,000 $9,000,000 $2,432,000 Budgeted variable administrative expenses for 2015 follow: Marketing and promotions Bad debt expense Sales commission Warranties Shipping and handling costs Insurance 10% of Sales 10% of Sales 3% of Sales 3% of Sales 1% of Sales 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. b. c. d. e. f. g. h. i. j. k. l. m. n. o. Data sheet Revenue budget for the year ending 12/31/2015 Purchases budget for the year ending 12/31/2015 Fixed administrative expense budget Variable administrative expense budget Interest expense budget Schedule of cash collections on sales Schedule of cash collections on investments Schedule of cash payments for inventory Schedule of cash payments for warranties Cash budget for the year ending 12/31/2015 Budgeted income statement for 12/31/2015 Budgeted cost of goods sold schedule for the year ending 12/31/2015 Budgeted statement of retained earnings for the year ending 12/31/2015 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 Executive and administrative salaries Depreciation $150,000 increase per location. ** $1,400,000 increase per new location. ** $20,000 increase per new location. ** Budgeted variable administrative expenses follow for future planning purposes: Marketing and promotions Bad debt expense Sales commission Warranties Shipping and handling costs Insurance **no change for increase in markup on costs. 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. No change. No change No change. 0.1% increase of total sales per new location.** No change. 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