Question

In: Accounting

The facts for this problem are presented on the Facts tab of this workbook. Instructions -...

The facts for this problem are presented on the Facts tab of this workbook.
Instructions - Your solutions should be clearly labeled on the Solutions tab of this workbook.
For the first quarter of 2017, do the following.
(a) Prepare a sales budget. This is similar to Illustration 21-3 on page 1088 of your textbook.
(b) Prepare a production budget. This is similar to Illustration 21-5 on page 1089 of your textbook.
(c) Prepare a direct materials budget. (Round to nearest dollar) This is similar to Illustration 21-7 on page 1091 of your textbook.
(d) Prepare a direct labor budget. (For calculations, round to the nearest hour.) This is similar to Illustration 21-9 on page 1094 of your textbook.
(e) Prepare a manufacturing overhead budget. (Round intermediate amounts to the nearest dollar.) This is similar to Illustration 21-10 on page 1094 of your textbook.
(f) Prepare a selling and administrative budget. This is similar to Illustration 21-11 on page 1095 of your textbook.
(g) Prepare a budgeted income statement. (Round intermediate calculations to the nearest dollar.) This is similar to Illustration 21-13 on page 1096 of your textbook.
(h) Prepare a cash budget. This is similar to Illustration 21-17 on page 1100 of your textbook.
     (You will need to prepare schedules for expected collections from customers and expected payments to vendors first. See Illustrations 21-15 and 21-16 on page 1099 of your textbook for guidance.)
Rules:
* Use Excel's functionality to your benefit. Points are lost for lack of formula.
* Use proper formats for schedules, following the referenced textbook examples.
* Use dollar-signs and underscores where appropriate.
* Double-check your work! Verify your formula and logic!
Grading Guidelines:
Effective Use of Excel 40%
Facts, Logic 20%
Completeness 30%
Spelling, Punctuation, Value Format 10%
Serious Business, Inc.
The company is preparing its budget for the coming year, 2017. The first step is to plan for the first quarter of that coming year. The following information has been gathered from their managers.
Sales Information
Period Units
November                   113,000 Actual Grading guidelines are on the instructions tab.
December                   101,000 Actual
January                   111,000 Planned
February                   112,000 Planned
March                   114,000 Planned
April                   124,000 Planned
May                   136,000 Planned
Unit selling price $                   12.00
Finished Goods Inventory Planning
The company likes to keep 10% of the next month’s unit sales in finished goods ending inventory.
Accounts Receivable & Collections
Sales on Account 100%
Collections Activity
Month of Sale 85%
Month after Sale 15%
Balance at 12/31/16 $         185,000.00
Materials Inventory Costs & Planning
Direct Materials Amount Used per Unit Cost
Metal                               2 lb $        1.00 lb
The company likes to keep 5% of the material needed for the next month's production in raw materials ending inventory.
Accounts Payable & Disbursements
Purchases on Account 100%
Payment Activity
Month of Purchase 50%
Month after Purchase 50%
Balance at 12/31/16 $              120,000
Direct Labor & Costs
Time per Unit Production                               9 minutes
Pay Rate/Hour $                     7.00
Manufacturing Overhead Costs
Variable costs per direct labor hour
Indirect materials $                     0.30
Indirect labor                         0.45
Utilities                         0.45
Maintenance                         0.25
Fixed costs per month
Salaries $                 42,000
Depreciation                     16,800
Property taxes                       2,675
Insurance                       1,200
Janitorial                       1,300
Selling and Administrative Costs
Variable costs per unit sold $                     1.45
Fixed costs per month
Advertising $                 15,000
Insurance                     14,000
Salaries                     72,000
Depreciation                     25,000
Other fixed costs                       3,000
Income Taxes
Accrued on Monthly Net Income 35% rounded to nearest dollar
Amounts Accrued Q4 2016 paid January 2017 $              200,000
Cash and Financing Matters
Cash Balance, 12/31/2016 $                 90,000
2017 Minimum Balance Required                   715,000
Monthly Dividends $                     2.25 per share
Outstanding Shares                       5,000
Line of Credit
Limit None
Borrowing Increment Required $                   1,000
Interest Rate 9%
Draws First of Month
Repayments Last of Month
Interest accumulates to the loan balance and is paid in full with each repayment.
Additional Item
Fixed Asset Purchase $              445,000
Month February

Solutions

Expert Solution


Related Solutions

The facts for this problem are presented on the Facts tab of this workbook. Instructions -...
The facts for this problem are presented on the Facts tab of this workbook. Instructions - Your solutions should be clearly labeled on the Solutions tab of this workbook. For the first quarter of 2017, do the following. (a) Prepare a sales budget. This is similar to Illustration 21-3 on page 1088 of your textbook. (b) Prepare a production budget. This is similar to Illustration 21-5 on page 1089 of your textbook. (c) Prepare a direct materials budget. (Round to...
Part I We’ll use the “Debt and Taxes” tab in the Lab 5 Excel Workbook The...
Part I We’ll use the “Debt and Taxes” tab in the Lab 5 Excel Workbook The Economic Data Runs from 1946 (1st year post WW2) to 2016 Note: This issue is tremendously more complicated than the two variables presented here. This is only a partial look at the issue and there is ample room for debate as causes of the issues at hand. 1) Examining the Relationships               Create and copy in the following Charts                              1) Line Chart with...
Complete the “July Journal Entries” tab in your workbook using the Step One data in the...
Complete the “July Journal Entries” tab in your workbook using the Step One data in the appendix. The following events occur in July, 2018: July 1: You take $10,000 from your personal savings account and buy common stock in Peyton Approved. July 1: Purchase $6,500 in baking supplies from vendor, on account. July 3: Your parents lend the company $10,000 cash in exchange for a two-year, 6% note payable. Interest and the principal are repayable at maturity. July 7: Enter...
Step 2: Complete the following transactions in the August Journal Entries tab in your workbook August...
Step 2: Complete the following transactions in the August Journal Entries tab in your workbook August 5- paid employee for period ending 7/31 August 8-Receive payments from customers towards accounts receivable in amount of $3200. August 10 – paid July telephone bill August 15- Purchase additional baking supplies in amount of $5000 from vendor, on account. August 15 – Accrue wages earned for employee from period of 1st through 15th of August                      (Wage calculations table provided below) August 15-Pay...
Guidelines: Create an excel workbook with two tabs: Statement of Operations (1st tab) Balance Sheet (2nd...
Guidelines: Create an excel workbook with two tabs: Statement of Operations (1st tab) Balance Sheet (2nd tab) Use the word bank provided to create the rows of your statements. For the statement of operations, create columns for 2017 (current year) -2020. The ASC is projected to open in January 2018. Use the financial statement exhibits from your textbook reading as a guideline. Once you have sorted out the word list into financial statements in Excel, insert formulas to show how...
B. Step Six: Complete the “Adjusting Entries” tab in your workbook using the Step Six data...
B. Step Six: Complete the “Adjusting Entries” tab in your workbook using the Step Six data in the appendix. Note that you should take the adjusting entries from this worksheet and enter them into the “Trial Balance” tab in your workbook. C. Step Seven: Apply adjusting entries to create the adjusted trial balance. Note that the adjusting entries from Step Six will apply to affected accounts in the unadjusted trial balance to arrive at the adjusted trial balance. September 24:...
Presented below are several facts related to ABC Company. Assume that no mention of these facts...
Presented below are several facts related to ABC Company. Assume that no mention of these facts was made in the financial statements and the related notes. Your job is to determine the appropriate accounting treatment and disclosure to the notes to the financial statements. You must be specific on what details should be included to the notes of the financial statements. It is probable the contingency will result in a $100,00 loss, but it is reasonably possible the loss could...
Following the instructions on the Data tab, complete the spreadsheet for your Chief Financial Officer. Estimates...
Following the instructions on the Data tab, complete the spreadsheet for your Chief Financial Officer. Estimates at the beginning of the year Assembly Production Total Manufacturing Overhead Costs $       480,000 $            528,000 $          108,000 Direct Labor-hours              12,000                     7,200                 19,200 Machine hours                4,800                   24,000                 28,800 Job 142 Direct labor-hours 25 17 42 Machine hours 17 20 37 1.   Calculate the amount of manufacturing overhead applied to Job 142. Converse uses a plantwide predetermined overhead rate...
In this project, you are presented with a list of facts that you will use to...
In this project, you are presented with a list of facts that you will use to draft a contract. In doing so, ensure that all elements of a contract have been met, and that you have drafted a valid contract. You should use the information you have learned in Modules 5, 6, and 7. Listed below are facts you will use to draft a valid contract. List of Facts Your daughter, Susan, would like to take guitar lessons. Gary's Guitars...
Based on the data presented in the Spreadsheet below (Variances tab), complete the following calculations. Calculate...
Based on the data presented in the Spreadsheet below (Variances tab), complete the following calculations. Calculate the material variances. Calculate the labor variances. Complete your calculations by filling the in highlighted cells. Discuss your observations about the variances and ways to plan to improve any of the variances. Cookie Business Actual Cost of Direct Materials $          225,000 Standard Cost of Direct Materials $          224,800 Actual Materials Used                         30 Standard Materials Used                         31 Actual Direct Labor Rate $              15.50...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT