Reference no: EM131394584
Excel Workbook Assignment
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
|
|
|
|
All Sales are on Credit
|
|
|
Collections of Accounts Receivable:
|
|
|
Month of Service
|
48.00%
|
|
Month following Service
|
52.00%
|
|
|
|
Actual December 2016 Sales
|
$ 200,000
|
|
Expected Sales in Dollars
|
|
|
January
|
$ 240,000
|
|
February
|
$ 195,000
|
|
March
|
$ 175,000
|
|
April
|
$ 210,000
|
|
|
|
Cost of Goods Sold as a percentage of selling price.
|
62.00%
|
|
|
|
Planned ending inventory as a percentage of next month's required inventory.
|
70.00%
|
|
|
|
Purchases are are all on credit.
|
|
|
Purchases paid in current month
|
25.00%
|
|
Purchases paid in next month
|
75.00%
|
|
Actual Purchases made in December 2016
|
$ 137,800
|
|
|
|
Sales commission as a percent of sales
|
2.50%
|
|
Sales commission are paid one month after the month incurred.
|
|
|
|
|
Advertising expenses have a fixed and variable portion:
|
|
|
Fixed portion
|
$4,400
|
|
The fixed portion of the advertising is paid in the current month.
|
|
|
Variable portion as a percentage of sales
|
1.00%
|
|
The variable portion is paid one month after the month incurred.
|
|
|
|
|
Insurance policy:
|
|
|
Months of insurance coverage paid for
|
12
|
|
The policy period is from March 1 to February 28 each year
|
|
|
The premium to be paid on March 1, 2017 is
|
$2,100
|
|
The premium will be charged to prepaid insurance
|
|
|
|
|
Selling and Administrative expenses have a fixed and variable portion:
|
|
|
Fixed portion is paid in the month incurred
|
$26,000
|
|
Variable portion as a percentage of sales
|
3.00%
|
|
S&A expenses are paid one month after the month incurred
|
|
|
|
|
Depreciation expense per month
|
$4,600
|
|
|
|
The annual interest rate on the long term debt is
|
4.50%
|
|
Interest is incurred on the principal balance outstanding each month.
|
|
|
Interest is paid on a quarterly basis. Interest for November to January will be paid February 1.
|
|
|
No principal payments are scheduled to be made on the long-term debt until August of 2017
|
|
|
|
|
Income Tax rate
|
30%
|
|
Accrued taxes are paid on the 15th of January, April, July and October
|
|
|
|
|
The Company's Board of Directors plans to declare a dividend on February 20
|
$11,000
|
|
It will be paid on March 15.
|
|
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
|
$108,200
|
|
Accounts Receivable
|
104,000
|
|
Inventory
|
104,160
|
|
Prepaid Insurance
|
350
|
|
Total Current Assets
|
316,710
|
|
|
|
Property Plant and Equipment
|
|
|
Office Equipment
|
276,000
|
|
Accumulated Depreciation
|
(55,200)
|
|
Total Property Plant and Equipment
|
220,800
|
|
|
|
Total Assets
|
$537,510
|
|
LIABILITIES AND SHAREHOLDERS EQUITY
|
|
LIABILITIES
|
|
Current Liabilities
|
|
|
Accounts Payable - Purchases
|
$103,350
|
|
Sales Commission Payable
|
5,000
|
|
Selling and Admin Payable
|
6,000
|
|
Advertising Payable
|
2,000
|
|
Interest Payable
|
1,313
|
|
Dividends Payable
|
-
|
|
Income Taxes Payable
|
15,530
|
|
Total Current Liabilities
|
133,193
|
|
|
|
Long Term Debt
|
175,000
|
|
|
|
Total Liabilities
|
308,193
|
|
|
|
SHAREHOLDERS' EQUITY
|
|
Common Stock
|
140,000
|
|
Retained Earnings
|
89,318
|
|
|
|
Total Shareholders' Equity
|
229,318
|
|
|
|
Total Liabilities and Shareholders Equity
|
$537,510
|
Based on the information available to you, complete the Purchases Budget worksheet.
Based on the information available to you, complete the Cash Budget worksheet.
Based on the information available to you, complete the Budgeted Income Statement worksheet.
Based on the information available to you, complete the Budgeted Balance Sheet worksheet.
Attachment:- Assignment Worksheet.rar
|
Describe the distributions of the two variables
: What are the variables in this study? For each, indicate whether it is quantitative or categorical and what the units are.- Here are histograms of calcium concentration and mortality. Describe the distributions of the two variables.
|
|
What are some checks that could be made on the data entered
: Why do you think successful programs contain extensive data-input validity checks?
|
|
Identify the key ways that apples progress report has change
: Identify the key ways that Apple's Progress Report has changed since last year. Examine the manner in which Apple's Supplier Code of Conduct helps the organization operate as a socially responsible organization.
|
|
Describe the business research process
: Describe the business research process followed in the study - Identify the research problem, the research design approach, and research method used.
|
|
Complete the purchases budget worksheet
: 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. Based on the information available to you, complete the Purchases Budget worksheet
|
|
Create an appropriate graph of given data
: Create an appropriate graph of these data.- Do these data indicate that driving near home is particularly dangerous? Explain.
|
|
Link between strategic management and leadership
: Leadership is a key organizational intuitive that plays a key role in the success or failure of the organizations. The competition era has made for all organization to stay in the competition with following some crucial steps in terms of national and..
|
|
Identify an image from popular culture and fine art
: Identify an image from popular culture, fine art, advertising, television, or the internet that exhibits characteristics of propaganda as defined in the lecture.
|
|
Draw a supply chain map showing the various parties
: Draw a supply chain map showing the various parties in McDonald's supply chain: food growers, manufacturers, distributors, corporate headquarters, and restaurants.
|