Accounts-AW651

Accounts-AW651 Online Services

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 WageExpense 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.
 
4. 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.
 
5. Cost of Goods Sold is contracted for the next fiscal year to be30% for Small Buckets and 35% for Medium Buckets.
 
6. 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.
 
7. 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.
 
8. 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.
 
9. 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.
 
10. Utilities are expected to be 4% of the previous month’s Total Sales Revenue.
 
11. Advertising will be budgeted at 6% of the current month’s projected sales revenue.
12. Telephone Expense is budgeted at 3.0% of April’s Cost of Goods sold and will remain constant the rest of the year.
 
13. Shipping and Handling is expected to be 8% of the total monthly Cost of Goods sold.
 
You can read more about our case study assignment help services here.
 

Required
 
1. Place the Month Identification at the top of each column after skipping 1 row under the proper Income Statement heading.
2. Skip 1 column after the 12th month of the budget and place Yearly Total as the right most column.
 
3. 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.
4. Enter calculation formulas in all of the cells, including the first cell of the spreadsheet
 
5. Display only whole bucket numbers and whole dollar amounts. Do not show any decimal amounts.
6. Do not use any coloring of cells or any special formatting in the Income Statement.
 
7. Format Total Revenue, Total Cost of Goods Sold and Total Expenses as dollar ($) figures. Do not use the dollar sign($) in any other cells.
8. Utilize relative references only in all calculations except for the amounts retrieved from other sheets or for cells that require a specific Function.
 
9. Enter Expenses, in the EXPENSE section, in the same order they are listed in the exam.
10. Prepare the Budgeted Income Statement utilizing the format which you received via email for all Exams.
 
11. For Tax Expense utilize an IF function to ensure that no taxes are paid in a month with no positive income.
12. Place the Amortization Schedules in the properly Titled Amortization Schedule spreadsheet
 
product code: Accounts-AW651
 
Looking for best Accounts-AW651 online ,please click here