Budgeted Net Income Budgeted Cost of Goods Sold Budgeted


Question Description:

35

I was hoping to get a tutor to help me with this assignment. It’s kind of lengthy, but would appreciate all the help I can get. I’ve never used this site for tutoring, so I’m not sure if this is something that I can get help on. Please let me know at your earliest convenience. Excel Project Assignment Excel template SP16.xlsx Budgeted Net Income Budgeted Cost of Goods Sold Budgeted Total Assets Budgeted Cash Balance Budgeted Short Budgeted Long Term Investments Term Investments President CFO CDO CMO Business Consultant 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 Budgeted Long Revolving Line Term Debt of Credit McQueen Corporation Balance Sheet Year end 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 $52,730,500 $45,050,000 $11,456,000 $6,750,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 2016 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/2015 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/2015 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 (2015) 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/2016 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/2016 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

Answer

35