Find the solution attached herewith.
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
3) Watch the Annin Flagmaking video.
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
Input Data (all currency in US$)
Cash Collection Pattern
Month of sale
Cash Payments for Materials
Month of purchase
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
Selling & Administrative (S&A) Costs
Variable S&A cost per unit sold
Fixed S&A cost per month
Depreciation in fixed S&A cost
Other Cash Outflows
Cash dividends paid each month
Equipment purchases May
Equipment purchases June
Desired Ending Inventory
Beginning Account Balances as of March 31