• +44-190-022-0819 +44-190-022-0819
  • +1-248-268-9041 +1-248-268-9041
  • +61288800241 +61288800241

SEARCH SOLUTION

Search your solution from list of 1000+ questions

03-09-2018

Product code: Accounts-AW651

 

Jack and Jill are planning for the 3rd year of operations for their company named Big n Small, Inc. Which sells water buckets.  They want you to prepare a budget for all 12months of the new fiscal year which begins April 1 2014showing detailed monthly results for every item on the budget and a 12 month total as the last column of the spreadsheet. 

Jack & Jill forecast a Federal Income tax rate of35% for its next fiscal year.  No tax is paid in a month which does not have a profit.

 

The buckets will be sold by size and budgeted monthly for each size.  Small buckets currently sell for $28, medium buckets for $45.  A 5% sales price increase is expected for the first month of the new fiscal year and then the price will remain constant the rest of the year. 

They expect that for every Small Buckets sold they will sell three Medium Buckets. The number of small buckets sold will increase 10%over March’s sales in April.

 

There will be another, seasonal, 10% increase in the number of buckets sold  for the 4 months of May, June, July, and August and the number sold will then revert to the April sales number for the rest of the year.

 

The following are the projected expenses:

  1. Sales personnel are paid $12.00 per hour ( consider four forty hour weeks as a month).
  2. 5% of  total Sales Revenue  will be collected in a Bonus Pool for sales employees which will be shown as a separate expense, Bonus Expense, under Wage Expense each month and distributed by an agreed formula among the sales personnel.  They currently employ 5 sales personnel. Calculate and show the Wage Expense, Bonus Expense, and Total Wage Expense (which will include both wage and bonus expenses) for each month.
  3. One manager is employed at a base rate of $1,500 per month plus 4% of the month’s Total Revenue.

Calculate and show the manager’s Salary Expense for each month.

Calculate and show the Total Wage and Salary expense for each month.

  1. Health Insurance for all employees was budgeted at 4% of the total Salary and Wage Expense for each month last year but it is expected to increase to 10% under the Affordable Care Act.
  2. Cost of Goods Sold is contracted for the next fiscal year to be30%  for Small Buckets and  35% for Medium Buckets.
  3. The company  purchased a new building when it opened two years ago on January 1.  The building cost $375,000 and the finance terms were for 30 years at 5% interest with monthly payments. The company paid $75,000 down and financed the rest for 30 years. Prepare a mortgage Amortization Schedule for the building. Retrieve the monthly interest from the Mortgage Amortization Sheet and place the amounts in Monthly Mortgage Interest Expense under Expenses.
  4. The company will purchased a delivery truck on April 1 for $60,000 making a down payment of 10,000 and financing the rest for 48 months.  Prepare a loan amortization schedule and place the interest expense as Interest Expense on the Budgeted Income Statement.
  5. The truck will be depreciated over 5 years using the straight line depreciation method with no salvage value.  Prepare the Truck Depreciation Schedule in the correct spreadsheet and carry the monthly depreciation expense to the Budgeted Income Statement.
  6. Prepare a monthly quantity schedule for both sizes of buckets and use the calculated quantities in the Revenue and Cost of Goods Sold sections of the Budgeted Income Statement.
  7. Utilities are expected to be 4% of the previous month’s Total Sales Revenue.
  8. Advertising will be budgeted at 6% of the current month’s projected sales revenue.
  9. Telephone Expense is budgeted at 3.0% of April’s Cost of Goods sold and will remain constant the rest of the year.
  10. Shipping and Handling  is expected to be 8% of the total monthly Cost of Goods sold.

 

  1.  

Place the Month Identification at the top of each column after skipping 1 row under the proper Income Statement heading.

  1. Skip 1 column after the 12th month of the budget and place Yearly Total  as the right most column.
  2. Use a formula which will utilize the previous months total and will allow a change in the first month to carry forward without any other changes.
  3. Enter calculation formulas in all of the cells, including the first cell of the spreadsheet
  4. Display only whole bucket numbers and whole dollar amounts. Do not show any decimal amounts.
  5. Do not use any coloring of cells or any special formatting in the Income Statement.
  6. Format Total Revenue, Total Cost of Goods Sold and Total Expenses as dollar ($) figures.  Do not use the dollar sign($) in any other cells.
  7. Utilize relative references only in all calculations except for the amounts retrieved from other sheets or for cells that require a specific Function.
  8. Enter Expenses, in the EXPENSE section,  in the same order they are listed in the exam.
  9. Prepare the Budgeted Income Statement utilizing the format which you received via email for all Exams.
  10. For Tax Expense utilize an IF function to ensure that  no taxes are paid in a month with no positive income.
  11. Place the Amortization Schedules in the properly Titled Amortization Schedule spreadsheet
Download Questions

Product code: Accounts-AW651

Related Questions in (Accounting)

13-01-2018

Solution: The Journal of Helene Berr and Rue Ordener, Rue Labat 2 evidences that prove the difficulty and destructiveness that the people had to face in those four years. As mentioned by Berr (2009, p.23), in h ...

18-01-2018

Solution: Employee communication highlights the sharing of ideas and information. In this competitive business world, information exchange is essential among employees to develop team performance effectively. m ...

18-01-2018

Solution: As per Section 1 of the Thirteenth year plan describes about China’s two key objectives that will be accomplished if the National People’s Congress or the standing committee of this party passes t ...

18-01-2018

Solution: (Feldman 2005) reference information extraction to be one of the weightiest pre-processing method that escalates the text mining potential significantly. Pre-processing is an essential part in informa ...

18-01-2018

Solution: The pro forma income statement represents a trending statement that includes the probable net income value for the company considering the current growth and decline rates valid throughout the period ...

18-01-2018

Solution: Mode is defined as the value which occurs more frequently in the data set. The mode for non-business is 82 while for business is only 59. P value can be calculated from z table . As per z table p valu ...

18-01-2018

Solution: ABC assumes that there are different activities involved in different processes that cause costs andthe product, services, and customers are reasons for those activities. The UK customer segment is br ...

19-01-2018

Solution: Residual earnings valuation method is used to calculate the intrinsic value of the stock based on the expected residual income of the company in the coming years. The residual income is discounted bac ...