Complete the purchases budget worksheet

Assignment Help Finance Basics
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

Reference no: EM131394584

Questions Cloud

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.

Reviews

len1394584

2/15/2017 4:37:00 AM

I want to make sure you are good and clear on the formatting instructions. The last time I used the service this was an issue. When completing the worksheet the teacher gives you check figures. You must make sure those are aligned. Also, you have to use links in excel where possible. If numbers are hand typed versus referencing a cell 50% of the grade is lost.

Write a Review

 

Finance Basics Questions & Answers

  Given that you are rolling your services out in a foreign

given that you are rolling your services out in a foreign country there will be a need to learn from other companies

  How large would the annual net cash inflows from the

highpoint inc. is considering investing in automated equipment with a ten-year useful life. managers at highpoint have

  What is the accounting break-even level of production

Wexford Industrial Supply is considering a new project with estimated depreciation of $32,000, fixed costs of $36,000, and total sales of $73,500. The variable costs per unit are estimated at $5.00. What is the accounting break-even level of produ..

  Securities from any of the financial markets

Choose three (3) types of securities from any of the financial markets. Pick securities you would enjoy researching for this assignment.

  Different types of value in the valuation process

Valuation of a firm's financial assets is said to be based on what is expected in the future, in terms of the future performance of the firm, the industry, and the economy. What types of value would you consider when assigning "value" to a firm's..

  Degree of leverage of this company

A firm has a sales level target of $200,000. If the variable cost is $120,000 and the fixed cost is $40,000. Find the degree of leverage of this company

  What is the bond ratings model

According to an article in the New York Times,- What is the bond ratings model? What potential conflicts are embedded in it?

  Prepare a consolidated balance sheet for ormond co

Prepare a consolidated balance sheet for Ormond Co. and Daytona Co. on January 1, 2010. Ignore deferred tax effects. Exhibit 7.32 presents income statements and balance sheets taken from the separate-company books at the end of 2010. The following in..

  Change in working capital for the month

Compute Elliott's change in working capital for the month of January 20X3.

  When is a debt security considered impaired explain how to

when is a debt security considered impaired? explain how to account for the impairment of an available-for sale debt

  What must be the markets expectations of the growth rate

Computer stocks currently provide an expected rate of return of 16%. MBI, a large computer company, will pay a year-end dividend of $2 per share. If the stock is selling at $50 per share, what must be the market's expectations of the growth rate of M..

  A compute stock as beta round your answer to two decimal

suppose rrf 5 rm 8 and ra 14.a. calculate stock as beta. round your answer to two decimal places.b. if stock as beta

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd