Excel Question (show all the formulas)

Shooter, Inc.began operations on September 1, 2020

The following balances are theACTUAL TOTALS during 2020 (please be aware that 99.99% of the time projected balances are different from what turns out to be actual balances.)

Sales - September, 23,000 units, October 21,000 units, November, 22,000 units, December 26,000 units

Production - September 28,250 units, October 20,500 units, Nov 22,500 units, December 26,000 units

Purchases of Raw Material - Sept 172,000 pounds; Oct 105,500 pounds; Nov 117,750 pounds;

December 125,125 pounds

Ending Inventory (December 31, 2020), Finished Goods - 6,000 units

Ending Inventory (December 31, 2020), Raw Material - 34,125 pounds

Accounts Receivable as of December 31, 2020 - $2,035,600

Allowance for Doubtful Accounts as of December 31, 2020 - $128,800

Accounts Payable for raw material purchases, December 31, 2020 - $225,225

Cash Balance on December 31, 2020 - $483,750

Now management for Shooter, Inc. is planning for 2021 and you have been tasked to prepare the master budget for the company for January through June 30^th

Each unit will sell for $140

JanuaryFebruaryMarchAprilMayJuneJulyAug

Forecasted Sales (in units) 24,00019,00026,00025,00022,00023,00021,00017,000

Each unit requires 5 pounds of material

Each unit requires 3 hours of Direct Labor

Overhead is applied based on machine hours, each unit requires 2.5 hours of machine time

Each pound of material costs $6

Each machine hour costs $10 of overhead of which $1 is depreciation

Each Direct Labor hour costs $20

Your desired Finished Goods inventory is 25% of next month's sales

Your desired Raw Material ending inventory is 30% of next month's usage

Your cash collections are 65% during the month of sale, 18% the next month and 10% two months later, 6% three months later, you anticipate never collecting 1%, management policy is to show Bad Debt Expense in the month the sale occurred and to write off any uncollected account 1 year after the sale occurred. Until that time, the amount estimated to never to be collected will appear in Allowance for Doubtful Accounts (go back and look at your 211 stuff)

Your cash payments of raw material are 70% the month of purchase and 30% the next month

DecemberJanuaryFebruaryMarchAprilMayJune

Admin expenses - $460,000$410,000$370,000$480,000 $470,000 $390,000$400,000

$25,000 of administrative expenses are depreciation, 80% of the remaining administrative expenses are paid in the month incurred, 20% the following month.

Taxes are 25% of pre-tax income, all taxes are paid the same month they are incurred.

Required - using Excel and looking at the schedules in your book on pages 370-, a master monthly budget for Shooter, Inc. for January through June 30^th. Your monthly master budget should include the following:

1)Sales Budget in units and dollars for each month

2)Production Budget in units for each month (somewhere near your budget you should calculate cost per unit)

3)Direct Labor Budget for each month

4)Overhead Budget (remember, some of this is depreciation)

5)Purchases Budget for Raw Material for each month

6)Administrative Expenses Budget (remember some of this is depreciation)

7)Cash Collections Budget for each month

8)Cash Disbursements Budget for each month

9)Cash Budget for each month

10)Income Statement for each month

Bonus -

Using your budgets, what are the balances on June 30, 2021 for each of the following accounts?

1)Accounts Receivable

2)Allowance for Doubtful Accounts

3)Accounts Payable