Find the solution attached herewith.
Requirements:
1) Using the data input provided, prepare AF’s master budgets in Excel. Do not hard-code numbers into the spreadsh
except in the financing section of the cash budget.
2) Conduct a sensitivity analysis by decreasing sales 2%, and 5% for April through August. New sales levels are prov
below. Adjust the financing and cash needs at these new sales levels. Your spreadsheet should have new tabs with th
levels changed.
Decreased by
2%
5%
2,450
2,375
5,880
5,700
2,940
2,850
2,450
2,375
1,960
1,900
Budgeted sales
April (units)
May (units)
June (units)
July (units)
August (units)
Expected
2,500
6,000
3,000
2,500
2,000
3) Watch the Annin Flagmaking video.
www.youtube.com/watch?v=GPC1ZMOeAr8
Excel. Do not hard-code numbers into the spreadsheet,
or April through August. New sales levels are provided
ls. Your spreadsheet should have new tabs with the sales
?v=GPC1ZMOeAr8
Input Data (all currency in US$)
Budgeted Sales
April (units)
May (units)
June (units)
July (units)
August (units)
Selling Price/Unit
Expected
2,500
6,000
3,000
2,500
2,000
$120.00
Cash Collection Pattern
Month of sale
Following month
Uncollectible
40%
55%
5%
Cash Payments for Materials
Month of purchase
Following month
40%
60%
Production Requirements
Raw material per unit (lb.)
Raw material cost per lb.
Direct labor hours per unit
Direct labor rate per hour
Variable MOHD per direct labor hour
Fixed MOHD per month
Depreciation in fixed MOHD
5
$7.00
0.5
$40.00
$10.00
$57,950
$20,000
Selling & Administrative (S&A) Costs
Variable S&A cost per unit sold
Fixed S&A cost per month
Depreciation in fixed S&A cost
$1.25
$63,000
$10,000
Other Cash Outflows
Cash dividends paid each month
Equipment purchases May
Equipment purchases June
$15,000
$47,820
$154,600
Desired Ending Inventory
Finished goods
Raw materials
Cash
20%
40%
$30,000
Beginning Account Balances as of March 31
Cash
Accounts receiv…
