1. HiMitoshi-Wouldyoubeabletohelpwiththefollowingspreadsheet.
3. Create the Merchandise Purchases Budget from the information provided (25 pointsgraded for accuracy and format, including Excel spreadsheet links).
4. Create the Cash Budget based on your work completed in part (a) and the information provided (25 pointsgraded for accuracy and format, including Excel spreadsheet links).
5. Create the Budgeted Income Statement, based on your work completed in parts (a) and (b) and the information provided
6. Create the Budgeted Balance Sheet, based on your work completed in parts (a), (b), and (c), and the information provided

Module 6 Excel Workbook Assignment MBA 6315 A relatively new company, Wine To End Whining, Inc., has determined they need to engage in a budgeting process as part of their new strategic plan. The Company has asked you to provide the following items for the months of January, February, and March of 2017: A) a Purchases Budget; B) a Cash Budget; C) a Budgeted Income Statement; and D) a Budgeted Balance Sheet. They have provided you with the planning document below and also, with their December 31, 2016 Balance Sheet. Please complete the four items requested on Worksheet Tabs A, B, C, and D of this Workbook. Check Figures are provided for you on each Worksheet. WINE TO END WHINING, INC. PLANNING DATA FOR 2017 Expected Sales in Dollars January 2017 February 2017 March 2017 April 2017 All Sales are on Credit Collections of Accounts Receivable Month of Service Month following Service Actual December 2016 Sales $ $ $ $ 220,000 193,000 178,000 201,000 44% 56% $ 200,000 Cost of Goods Sold is 65% of selling price. 65% Planned ending inventory as a percentage of next month's requirement. 60% Purchases are are all on credit. Purchases paid in current month Purchases paid in next month Actual Purchases made in December 2016 30% 70% Sales commission as a percent of sales Sales commission are paid the month after they are incurred. Advertising expenses have a fixed and variable portion: Fixed portion The fixed portion of the advertising is paid one month in advance. Variable portion as a percentage of sales The variable portion is paid in the month incurred Insurance policy: Months of insurance coverage paid for The policy period is from February 1 to January 31 each year The premium to be paid on February 1, 2017 is 24,000 The premium will be charged to prepaid insurance $ 137,800 3% $6,400 2% 12 $24,000 Selling and Administrative expenses have a fixed and variable portion: Fixed portion Variable portion as a percentage of sales S&A expenses are paid in the month incurred $29,000 3% Depreciation expense per month $4,000 The interest rate on the long term debt is 4% APR 4% Interest is incurred on the principal balance outstanding each month. Interest is paid on a quarterly basis. Interest for December to February will be paid March 1. No principal payments are scheduled to be made on the long-term debt until September of 2017 Income Tax rate Accrued taxes are paid on the 15th of January, April, July and October 30% The Company's Board of Directors plans to declare a dividend on February 20 It will be paid on March 15. $17,000 Module 6 Excel Workbook Assignment MBA 6315 Use the information provided on the December 31, 2016 Balance Sheet shown below to help you create the budgeting documents requested. WINE TO END WHINING, INC. BALANCE SHEET December 31, 2016 ASSETS Current Assets Cash Accounts Receivable Inventory Prepaid Advertising Prepaid Insurance Total Current Assets $ Property Plant and Equipment Office Equipment Less Accumulated Depreciation Total Property Plant and Equipment Total Assets 103,200 112,000 85,800 6,400 2,000 309,400 240,000 (48,000) 192,000 $ 501,400 LIABILITIES AND SHAREHOLDERS EQUITY LIABILITIES Current Liabilities Accounts Payable - Purchases $ Sales Commission Payable Interest Payable Dividends Payable Income Taxes Payable Total Current Liabilities 96,460 6,000 600 8,970 112,030 Long Term Debt 180,000 Total Liabilities 292,030 SHAREHOLDERS' EQUITY Common Stock Retained Earnings 124,000 85,370 Total Shareholders' Equity 209,370 Total Liabilities and Shareholders Equity $ 501,400 Module 6 Excel Workbook Assignment MBA 6315 Based on the information available to you, complete the Purchases Budget worksheet below. Check figures are: January Budgeted Purchase for Next Month Sales = $132,470; March Inventory Needed to be Available during Current Month = $194,090. WINE TO END WHINING, INC. PURCHASES BUDGET PLANNING FOR 2017 Solution: Desired Ending Inventory Balance for Current Month Current Month's Cost of Sales Inventory Needed to be Available during Current Month Less: Beginning Inventory of Current Month Budgeted Purchase for Next Month Sales Actual Dec 2016 $ 85,800 130,000 215,800 78,000 $ 137,800 Budgeted January Budgeted February Budgeted March Module 6 Excel Workbook Assignment MBA 6315 Based on the information available to you, complete the Cash Budget worksheet below. Check figures are: January Cash Receipts = $208,800; February Cash Disbursements = $204,259; March Ending Cash Balance = $114,679. Solution: WINE TO END WHINING, INC. PLANNED CASH TRANSACTIONS and CASH BUDGET For the Month Ending, Jan 31, 2017 CASH IN 44% This Month's Sales 56% of Last Month's Sales Total Cash Receipts CASH OUT 70% Last Month's Purchases 30% This Month's Purchases Commissions Advertising Selling & Admin. Insurance Interest Dividends Income Tax Total Cash Disbursements CASH BUDGET Beginning Balance of Cash Cash Receipts Cash Available Cash Disbursements Ending Cash Balance Feb 28, 2017 Mar 31, 2017 Module 6 Excel Workbook Assignment MBA 6315 Based on the information available to you, complete the Budgeted Income Statement worksheet below. Check figures are: January Budgeted Gross Margin = $77,000; February Budgeted Operating Income = $10,710; March Budgeted Net Income = $4,242. Solution: WINE TO END WHINING, INC. BUDGETED INCOME STATEMENTS For the Month Ending, Jan 31, 2017 Sales Cost of Sales Gross Margin Other Operating Expenses Commission Expense Advertising Expense Insurance Expense Selling & Admin. Expense Depreciation Expense Total Other Operating Expenses Operating Income Other Income (Expense) Interest Expense Income Before Income Tax Income Tax Expense Net Income Feb 28,2017 Mar 31, 2017 Module 6 Excel Workbook Assignment MBA 6315 Based on the information available to you, complete the Budgeted Balance Sheet worksheet below. Check figures are: January Total Assets = $507,299; February Total Liabilities = $296,563; March Total Current Assets = $319,149. Solution: WINE TO END WHINING, INC. BUDGETED BALANCE SHEETS ASSETS Dec 31, 2016 Current Assets Cash Accounts Receivable Inventory Prepaid Advertising Prepaid Insurance $ 103,200 112,000 85,800 6,400 2,000 Total Current Assets 309,400 Property Plant and Equipment Office Equipment Less Accumulated Depreciation Total Property Plant and Equipment 240,000 (48,000) 192,000 Total Assets $ 501,400 Jan 31, 2017 Feb 28,2017 Mar 31, 2017 WINE TO END WHINING, INC. BUDGETED BALANCE SHEETS ASSETS LIABILITIES AND SHAREHOLDERS EQUITY LIABILITIES Current Liabilities Accounts Payable - Purchases Sales Commission Payable Interest Payable Dividends Payable Income Taxes Payable $ 96,460 6,000 600 8,970 Total Current Liabilities 112,030 Long Term Debt 180,000 Total Liabilities 292,030 SHAREHOLDERS' EQUITY Common Stock Retained Earnings 124,000 85,370 Total Shareholders' Equity 209,370 Total Liabilities and Shareholders Equity $ 501,400