AFs Master Budget Managerial Accounting Excel Project Number 4

User Generated

Senaxr

Business Finance

Description

need help to complete an excel project

cells should have formulas

please take a look at the attached excel file for instructions and requirement

Unformatted Attachment Preview

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 receivable $37.745 $132.000 Finished goods inventory Finished goods cost per unit Finished goods inventory (units) Raw materials inventory Raw materials (lb.) Accounts payable Land Buildings and equipment Accumulated depreciation Common stock Retained earnings $30.750 $75,00 410 $32.200 4.600 $55.000 $520.000 $1.800.000 ($750.000) $500.000 $1.247.695 Cost of Goods Sold (FIFO) Beginning finished goods inventory Add: Cost of goods manufactured Good available for sale Less: Ending finished goods inventory Cost of Good Sold Sell Budgeted sales in units Variable S&A per unit Total variable S&A Total fixed S&A Total S&A expense Less: Depreciation Cash Disbursements for S&A Beginning Cash Balance Add: Receipts Cash collections Total Cash Available Less disbursements Direct materials Direct labor Manufacturing overhead Selling and administrative Dividends Equipment purchases Total Disbursements Excess (deficiency) of cash available Financing Borrowing Repayments Interest Total Financing Ending Cash Balance Net sales Less: Cost of goods sold Gross margin Less: S&A expenses Net operating income Less: Interest expense Net income Computation of Net Sales Sales Less uncollectible amounts Net Sales Current assets Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment Land Buildings and equipment Accumulated depreciation Total Assets Liabilities Accounts payable Stockholder's equity Common stock Retained earnings Yellow-use only cell references Blue-may type numbers here Annin Flagmakers Sales Budget (US$) April May June Budgeted sales (units) Selling price per unit Total Sales Annin Flagmakers Schedule of Expected Cash Collections (US$) April May June Accounts receivable Beginning balance April sales May sales June sales Total Cash Collections Accounts Receivable as of June 30 Annin Flagmakers Production Budget April May June Budgeted sales Add: Desired ending inventory Total needs Less: Beginning inventory Required Production Annin Flagmakers Direct Materials Budget (US$) April May Required production in units Raw materials per unit (lbs.) Production needs (lbs.) Add: Desired ending inventory Total needs Less: Beginning inventory Raw materials to be purchased Cost of raw materials Total Cost of Raw Materials June Annin Flagmakers Schedule of Expected Cash Disbursements for Material (US$) April May June Accounts payable Beginning balance April purchases May purchases June purchases Total Cash Disbursements for Materials Accounts Payable as of June 30 Annin Flagmakers Direct Labor Budget (US$) April May June Units to be produced Direct labor hours per unit Total direct labor hours needed Direct labor cost per hour Total Direct Labor Cost Budgeted direct labor hours Variable MOHD rate Total variable MOHD Fixed MOHD expense Total MOHD expense Less: Depreciation Cash Disbursements for MOHD MOHD rate Absorption cost per unit Direct materials Direct labor Manufacturing overhead Unit Product Cost Annin Flagmakers Manufacturing Overhead Budget (US$) April May June 0 0 0 $10 $10 $10 $ $ $ $ 57.950 $ 57.950 $ 57.950 $ 57.950 $ 57.950 $ 57.950 $ (20.000) $ (20.000) $ (20.000) $ 37.950 $ 37.950 $ 37.950 #DIV/0! /direct labor hour Annin Flagmakers Unit Product Cost (US$) Quantity Cost 5 $7,00 0,50 $40,00 0,50 #DIV/0! Cost/unit $35,00 $20,00 #DIV/0! #DIV/0! Cost of Goods Sold (FIFO) Beginning finished goods inventory Add: Cost of goods manufactured Good available for sale Less: Ending finished goods inventory Cost of Good Sold Annin Flagmakers Cost of Goods Sold Budget (USD) Units Cost/unit Total Cost 410 $ 75,00 $ 30.750 0 #DIV/0! #DIV/0! #DIV/0! 0 #DIV/0! #DIV/0! #DIV/0! Annin Flagmakers Selling and Administrative Expense Budget (US$) April May June Budgeted sales in units Variable S&A per unit Total variable S&A Total fixed S&A Total S&A expense Less: Depreciation Cash Disbursements for S&A Annin Flagmakers Cash Budget (US$) April May Beginning Cash Balance Add: Receipts Cash collections Total Cash Available Less disbursements Direct materials Direct labor Manufacturing overhead Selling and administrative Dividends Equipment purchases Total Disbursements Excess (deficiency) of cash available Financing Borrowing Repayments Interest Total Financing Ending Cash Balance June Little Annin Flagmakers Budgeted Income Statement (US$) Quarter Ending June 30 Net sales Less: Cost of goods sold Gross margin Less: S&A expenses Net operating income Less: Interest expense Net income Computation of Net Sales Sales Less uncollectible amounts Net Sales $ $ $ Little Annin Flagmakers Budgeted Balance Sheet (US$) Ending March 31 Current assets Cash Accounts receivable Raw materials inventory Finished goods inventory Plant and equipment Land Buildings and equipment Accumulated depreciation Total Assets Liabilities Accounts payable Stockholder's equity Common stock Retained earnings Total Liabilities and Stockholder's Equity $ $ $ $ 37.745 132.000 32.200 30.750 $ $ $ $ 232.695 $ $ $ 520.000 1.800.000 (750.000) $ $ $ 55.000 $ 55.000 $ $ 500.000 1.247.695 $ 1.747.695 $ 1.802.695 1.570.000 1.802.695 $ $ $ $ $ #DIV/0! 520.000 2.002.420 (810.000) 500.000 #DIV/0! Quarter Quarter Quarter Quarter July August r Material (US$) Quarter Quarter $ $ $ $ $ Quarter 0 $10 173.850 173.850 (60.000) 113.850 dget (US$) Quarter Quarter $ #DIV/0! #DIV/0! #DIV/0! #DIV/0! $ $ nin Flagmakers lance Sheet (US$) Ending June 30 #DIV/0! $ $ 1.712.420 #DIV/0! - #DIV/0! #DIV/0! Excel Spreadsheet Grading Rubric 20 points 15 points 10 points 0 points Overall completeness Fully Mostly Poorly Correctness of budgets Fully Mostly Poorly Hard-coded numbers None Few (1-2) Many (3+) Sales adjusted Financing section adjusted All hardcoded All Most Some Not done completed completed completed Fully correct Few errors Many errors Not done Total points out of 100 Points
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

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...


Anonymous
Goes above and beyond expectations!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags