please i dont understand how to put it in Excel plus i really dont know how to solve this!!?? please i need help

please i dont understand how to put it in Excel plus i really dont know how to solve this!!?? please i need help

ATTACHMENT PREVIEW

Download attachment

MGA202
Spring 2013
Practice Set
Budgeting Spreadsheet
This assignment asks you to set up an Excel budget spreadsheet file that automatically prepares
the master budget for a company, given sales projections and information on beginning
balances, production requirements, desired ending inventories, etc.
Information on developing
the budgets appears in Chapter 7 of your text, and examples of budget worksheets appear in the
schedules throughout the chapter.
Data
Niagara Manufacturing Company produces and sells one product, Metro End Table. Below is
information on its activities for the next few months.
1.
Sales projections for the coming months are as follows:
Estimated Sales (in units)
July
August
September
October
Metro end tables
10,000
12,000
9,000
8,000
Actual sales in May were 10,000 units; actual sales in June were 14,000 units.
2.
Metro end table’s selling price is $500/unit. Estimated cash collections from sales of
each month (including June) of Metro end tables to customers are as follows: 65%
collected in the month of sale, 30% collected in the month following sale, and 5% can
not be collected.
3.
Desired ending inventory of Metro end table is 20% of the next month’s projected sales.
There are 4,000 units of end tables in inventory on June 30.
4.
Two materials are used in the production of Metro end table: Red Oak and Glass.
Materials requirements per unit of end table are as follows:
Direct Material
Units of direct materials
Cost per unit
per unit of Metro end table.
Red Oak:
45 pounds
$6.00/pound
Glass:
9 square feet
$1.00/square foot
Desired ending inventory of Red Oak is 30% of the following month’s production need
because Red Oak is sometimes in short supply; desired ending inventory of Glass is 5%
of the following month’s production need because Glass is easy to get.
Inventories of
materials as of June 30 are 26,000 pounds of Red Oak and 4,900 square feet of Glass.
Practice Set 1 Page 1

Unlock Solution Unlocking…

5.
The company pays for materials purchases as follows: 60% in the month of purchase,
and 40% in the month following purchase.
The accounts payable balance (due to
materials purchases) on June 30 was $1,100,000, which is all payable in July.
6.
Metro end tables have two departments to pass before it is completed.
Department
Direct labor hours per unit of end table
Cost per direct labor
hour
Shaping:
0.75 hour
$24
Finishing:
1.70 hours
$16
7.
Direct labor costs are paid in cash as incurred.
8.
Total variable manufacturing overhead is estimated at $2.5/direct labor hour incurred.
Total fixed manufacturing overhead is estimated at $500,000/month, of which $5,000 is
depreciation on factory buildings and equipment.
9.
Total variable selling and administrative costs are $2/unit of Metro end table sold.
Total fixed selling and administrative costs are estimated at $300,000/month, of which
$65,000 is depreciation on administrative buildings and equipment.
10.
Cash payments for overhead and selling and administrative costs are paid as the costs
are incurred.
Preparation of Spreadsheet File
Create one
Excel spreadsheet file consisting of the following five separate
worksheets:
Sheet 1:
Data
This worksheet contains the data necessary to do all the other worksheets.
List
on this worksheet all of the data shown above, clearly labeled.
None of the other
worksheets should contain any numbers; they should contain ONLY FORMULAS
– all cells on the other worksheets should be linked to cells in the data
worksheet, cells within the same worksheet, or cells in the other worksheets.
For example, the cell for direct labor cost for July should contain a formula that
multiplies the production in units for July (from the production schedule) times
the labor hours per unit (from the data worksheet) times the hourly wage rate
(from the data worksheet).
So if the sales estimate for end tables changes, you
should be able to make the change
only on the data worksheet
; all the other
worksheets should automatically adjust to the changes. The production units will
change; the manufacturing costs will change, etc.
The data worksheet can be in
any format; just be sure to label each data item clearly.
Practice Set 1 Page 2

Show entire document

Leave a Comment