Accounting Budget Project

profileWaqas Ahmed
angies_empanadasbudgetf16ais.xlsx
Home>Business & Finance homework help>Accounting homework help>Accounting Budget Project

Name & IDnumber

ID
Name: Last First 1 2 3 4 5 6 7 8 9
Alsolai Sarah 0 0 0 0 6 9 3 8 8 34 4

Questionsfigures

Cost_Volume_Profit Analysis Input your M number in the Name &Mnumber spreadsheet and use the figures
& the behavior of costs
Price per trayF $11.88 $11.88
Over the past six months Angie
has incurred the following costs
and made the sales revenues
related to her empanada business 0.29 0.38 0.27 100 $ 147.00 $ 721.00
Empanada Labor Total Empanada Labor
Sales Ingredients Costs Trays Rent Utilities Delivery Quantity Profit Cost Sales Ingredients Costs Trays Rent Utilities Delivery Q profit
APRIL $ 3,564.00 $ 1,033.56 $ 1,354.32 $ 81.00 $ 1,100.00 $ 147.00 $ 721.00 300 $ (872.88) $ 4,436.88 APRIL $ 3,564.00 $ 1,033.56 $ 1,354.32 $ 81.00 $ 1,100.00 $ 147.00 $ 721.00 300 $ (872.88) 300
MAY $ 4,158.00 $ 1,205.82 $ 1,580.04 $ 94.50 $ 1,100.00 $ 153.50 $ 776.00 350 $ (751.86) $ 4,909.86 MAY $ 4,158.00 $ 1,205.82 $ 1,580.04 $ 94.50 $ 1,100.00 $ 153.50 $ 776.00 350 $ (751.86) 350
JUNE $ 5,346.00 $ 1,550.34 $ 2,031.48 $ 121.50 $ 1,100.00 $ 166.50 $ 886.00 450 $ (509.82) $ 5,855.82 JUNE $ 5,346.00 $ 1,550.34 $ 2,031.48 $ 121.50 $ 1,100.00 $ 166.50 $ 886.00 450 $ (509.82) 450
JULY $ 5,940.00 $ 1,722.60 $ 2,257.20 $ 135.00 $ 1,100.00 $ 173.00 $ 941.00 500 $ (388.80) $ 6,328.80 JULY $ 5,940.00 $ 1,722.60 $ 2,257.20 $ 135.00 $ 1,100.00 $ 173.00 $ 941.00 500 $ (388.80) 500
AUGUST $ 7,722.00 $ 2,239.38 $ 2,934.36 $ 175.50 $ 1,100.00 $ 192.50 $ 1,106.00 650 $ (25.74) $ 7,747.74 AUGUST $ 7,722.00 $ 2,239.38 $ 2,934.36 $ 175.50 $ 1,100.00 $ 192.50 $ 1,106.00 650 $ (25.74) 650
SEPTEMBER $ 13,068.00 $ 3,789.72 $ 4,965.84 $ 297.00 $ 1,100.00 $ 251.00 $ 1,601.00 1,100 $ 1,063.44 $ 12,004.56 SEPTEMBER $ 13,068.00 $ 3,789.72 $ 4,965.84 $ 297.00 $ 1,100.00 $ 251.00 $ 1,601.00 1100 $ 1,063.44 1100
Total $ 39,798.00 $ 11,541.42 $ 15,123.24 $ 904.50 $ 6,600.00 $ 1,083.50 $ 6,031.00 3,350 $ (1,485.66) $ 41,283.66
Variable cost per unit $ 3.45 $ 4.51 $ 0.27 $ - 0 $ 0.13 $ 1.10 $ 9.46
Fixed cost per month $ (0.00) $ (0.00) $ (0.00) $ 1,100.00 $ 108.00 $ 391.00 $ 1,599
Answer the following questions: Using formulas and links Do not type in answers $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.44 $ 2.22
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.37 $ 1.97
Month Angie first earns a profit? $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.35 $ 1.88
Which months does Angie have a loss? $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.30 $ 1.70
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.23 $ 1.46
Which items are:
Variable cost items? Ingredients Labor Trays VCu $ 0.13 $ 1.10
Mixed Cost items? Utilities Delivery FC 108 391
Fixed Cost item? Rent
Contribution Margin per tray? $2.42
Contribution Margin percentage? 20.37%
Angie's CVP formula per month Profit = $11.88 Q - $ 9.46 Q - $ 1,599
Break even Quantity 661
Break even Sales $ 7,848.34 Empanada Labor
Sales Ingredients Costs Trays Rent Utilities Delivery Q
Sales for target profit of $4,000 mo 2,313.25 units $ 27,481.46 $ 4,000.00 $ 1,000.00 $ 1,200.00 $ 100.00 $ 1,000.00 $ 140.00 $ 600.00 336.7003367003
$ 4,000 5,000.00 1,250.00 1,500.00 125.00 1,000.00 150.00 700.00 420.8754208754
Taxes Income 30% 3,000.00 750.00 900.00 75.00 1,000.00 130.00 500.00 252.5252525253
2,500.00 625.00 750.00 62.50 1,000.00 125.00 450.00 210.4377104377
Break even Quantity 661 4,500.00 1,125.00 1,350.00 112.50 1,000.00 145.00 650.00 378.7878787879
10,000.00 2,500.00 3,000.00 250.00 1,000.00 200.00 1,200.00 841.7508417508
Sales for target profit of $4,000 mo 3,021.52 units $ 35,895.65
$ 5,714.29
Please provide, in good form, an itemized Variable-Costing Income Statement for the six month period from April through September $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.49 $ 2.40
(Do not show 6 income statements) $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.44 $ 2.22
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.37 $ 1.97
Sales $ 39,798.00 $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.35 $ 1.88
Minus Variable Costs $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.30 $ 1.70
Ingredients $ 11,541.42
Labor 15,123.24
Trays 904.50 Vcu 0.13 1.1
Utilities 435.50 FC 108.00 391
Delivery 3,685.00
Total variable costs 31,689.66
Contribution Margin 8,108.34
Minus Fixed Costs
Rent 6,600.00 $11.88 Price 100% 661 BEq
Utilities 648.00 $ 1,599.00 9.46 Vcu 80%
Delivery 2,346.00 $2.42 Cmu 20% $ 7,848.34 BE$
Total Fixed Costs 9,594.00
Operating Income $ (1,485.66)
What questions would you need to have answered to determine if Angie can reach her goal in the next six months?

Inputs

Assumptions 4th Quarter
Price
Sales $11.88
Quantity
October 1,920
November 2,304
December 2,995
January 2,995
February 2,995
Inputs
Direct Materials Assumptions Cash Flow Assumptions
Beginning Inventory Sales Cash collections
Ingrediants 0 Cash 35%
Trays 0 Credit 65% following month
Desired ending Inventory Uncollectable 0
Ingrediants 5%
Trays 50% Current cash balance September 30. $ 3,486.86
Account Receivable $ 8,494.20
Standard Costs (last 6 mos) per tray Cash Disbursements
Ingrediants $ 3.45 Ingrediants 100% following month
Trays $ 0.27 Trays 100% following month
Line of credit and desired cash balance
Direct Labor Assumptions Line of credit $ 30,000
Standard Costs (last 6 mos) per tray Desired Cash balance $ 10,000
$ 4.51 Interest rate/year 6%
Borrowing Increment $ 1,000
Overhead assumptions and rates Accounts Payable
Rates per tray Ingrediants $ 2,684.00
Utilities $ 0.13 Trays $ 363.00
Other accounts paid in the month
per month
Utilities $ 108
Rent $ 1,265 Finished Goods Inventory Quanitity
Beginning 0.00% 0
Selling & Administrative Costs Ending 6.00%
Rates per tray
Delivery $ 1.43
per month
Delivery $ 391 Included in monthly delivery costs
Advertising $ 500
Fixed Delivery Vehicle Depreciation
Donated Van (April 1) $ 9,400
years life 3
Salvage value $ 1,120
Accumulated depreciation $ 1,380
Value of invested capital
Cash $ 8,000
Van $ 9,400

Beginning Balance Sheet

Balance Sheet 30-Sep
Assets Liabilities & OE
Cash $ 3,487 AP $ 4,086.72
AR 8,494 Loan Payable
Inventory
Materials
FG -
Van 9,400 Invested capital 17,400
Accm Dep (1,380) Profit(loss) (1,486)
Total Assets $ 20,001.06 Total L & OE $ 20,001.06

Sales and Cash Collections

Sale Budget
Month October November December quarter
Budgeted Sales 1,920.00 2,304.00 2,995.20 $ 7,219.20
Price $11.88 $11.88 $11.88 $11.88
Total $22,809.60 $27,371.52 $35,582.98 $85,764.10
Schedule of Cash Collections
Collections October November December quarter
AR BB 8,494 8,494
Sales
October
35% $7,983.36 $7,983.36
65% 14826.24 $14,826.24
November $0.00
35% $9,580.03 $9,580.03
65% $17,791.49 $17,791.49
December $0.00
35% $12,454.04 $12,454.04
total CC 16,478 24,406 30,246 71,129

Production

production
October November December quarter January
Sales Budgeted 1,920.00 2,304.00 2,995.20 7,219.20 2,995
Desired Ending Inv
6% 138.24 179.712 179.71 180 179.71
Total needs 2,058.24 2,483.71 3,174.91 7,398.91 3,174.91
Less BI 0 138.24 179.712 0 180
Required Production 2,058.24 2,345.47 2,995.20 7,398.91 2,995.20

Materials ingredients & trays

Cash disbursements for material

Direct Labor

Manufacturing Overhead

Selling & administrative expens

Cash Budget

COGM & COGS

Proforma Income Statement (var)

Proforma Balance Sheet

QuestionsfiguresSource

Cost_Volume_Profit Analysis Input your M number in the Name &Mnumber spreadsheet and use the figures
& the behavior of costs
Price per trayF $11.88 $11.88
Over the past six months Angie
has incurred the following costs
and made the sales revenues
related to her empanada business 0.29 0.38 0.27 100 $ 147.00 $ 721.00
Empanada Labor Empanada Labor
Sales Ingredients Costs Trays Rent Utilities Delivery Sales Ingredients Costs Trays Rent Utilities Delivery Q profit
APRIL $ 3,564.00 $ 1,033.56 $ 1,354.32 $ 81.00 $ 1,100.00 $ 147.00 $ 721.00 APRIL $ 3,564.00 $ 1,033.56 $ 1,354.32 $ 81.00 $ 1,100.00 $ 147.00 $ 721.00 300 $ (872.88) 300
MAY $ 4,158.00 $ 1,205.82 $ 1,580.04 $ 94.50 $ 1,100.00 $ 153.50 $ 776.00 MAY $ 4,158.00 $ 1,205.82 $ 1,580.04 $ 94.50 $ 1,100.00 $ 153.50 $ 776.00 350 $ (751.86) 350
JUNE $ 5,346.00 $ 1,550.34 $ 2,031.48 $ 121.50 $ 1,100.00 $ 166.50 $ 886.00 JUNE $ 5,346.00 $ 1,550.34 $ 2,031.48 $ 121.50 $ 1,100.00 $ 166.50 $ 886.00 450 $ (509.82) 450
JULY $ 5,940.00 $ 1,722.60 $ 2,257.20 $ 135.00 $ 1,100.00 $ 173.00 $ 941.00 JULY $ 5,940.00 $ 1,722.60 $ 2,257.20 $ 135.00 $ 1,100.00 $ 173.00 $ 941.00 500 $ (388.80) 500
AUGUST $ 7,722.00 $ 2,239.38 $ 2,934.36 $ 175.50 $ 1,100.00 $ 192.50 $ 1,106.00 AUGUST $ 7,722.00 $ 2,239.38 $ 2,934.36 $ 175.50 $ 1,100.00 $ 192.50 $ 1,106.00 650 $ (25.74) 650
SEPTEMBER $ 13,068.00 $ 3,789.72 $ 4,965.84 $ 297.00 $ 1,100.00 $ 251.00 $ 1,601.00 SEPTEMBER $ 13,068.00 $ 3,789.72 $ 4,965.84 $ 297.00 $ 1,100.00 $ 251.00 $ 1,601.00 1100 $ 1,063.44 1100
Answer the following questions: Using formulas and links Do not type in answers $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.44 $ 2.22
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.37 $ 1.97
Month Angie first earns a profit? $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.35 $ 1.88
Which months does Angie have a loss? $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.30 $ 1.70
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.23 $ 1.46
Which items are: TVCu TFCM Cmu/% Beq/$
Variable cost items? VCu $ 0.13 $ 1.10 $ 9.46 $2.42 661
Mixed Cost items? FC 108 391 $ 1,599.00 20.37% $ 7,848.34
Fixed Cost item?
Contribution Margin per tray?
Contribution Margin percentage?
CVP formula per month
Break even Quantity
Break even Sales Empanada Labor
Sales Ingredients Costs Trays Rent Utilities Delivery Q
Sales for target profit of $4,000 mo $ 4,000.00 $ 1,000.00 $ 1,200.00 $ 100.00 $ 1,000.00 $ 140.00 $ 600.00 336.7003367003
5,000.00 1,250.00 1,500.00 125.00 1,000.00 150.00 700.00 420.8754208754
Taxes Income 30% 3,000.00 750.00 900.00 75.00 1,000.00 130.00 500.00 252.5252525253
2,500.00 625.00 750.00 62.50 1,000.00 125.00 450.00 210.4377104377
Break even Quantity 4,500.00 1,125.00 1,350.00 112.50 1,000.00 145.00 650.00 378.7878787879
10,000.00 2,500.00 3,000.00 250.00 1,000.00 200.00 1,200.00 841.7508417508
Sales for target profit of $4,000 mo
Please provide a detailed Variable-Costing Income Statement for the six month period from April through September $ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.49 $ 2.40
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.44 $ 2.22
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.37 $ 1.97
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.35 $ 1.88
$ 3.45 $ 4.51 $ 0.27 $ 1,100.00 $ 0.30 $ 1.70
Vcu 0.13 1.1
FC 108.00 391
$11.88 Price 100% 661 BEq
$ 1,599.00 9.46 Vcu 80%
$2.42 Cmu 20% $ 7,848.34 BE$
What questions would you need to have answered to determine if Angie can reach her goal in the next six months?