course project overview

Marsolmio
ACCT346CourseProjectWorksheet.xlsx
Home>Business & Finance homework help>Accounting homework help>course project overview

Operating Budget

Prepare a Master Operating Budget for the First Quarter Buzz Pizza
Target Market: adult learners, timestarved, poor time management
Jan Feb Mar Total Qtr (A) Input the forecast sale volume
Sales : Average Sales Price/unit (B) Input the planned product mix
Units (A) - 0 (B) wgtd. (C) Input the number of pizza made per hour
Price/unit $ - 0 $ - 0 $ - 0 Sales Price (all 2 topping): % Sales average (D) Input the sales commission as a % of revenue
Total Sales Revenue $ - 0 $ - 0 $ - 0 $ - 0 Large $ 10.50 $ - 0 (E) Input the sales manager's salary
Medium $ 8.50 $ - 0 (F) Input the office manager's salary
Variable Costs (stated as per unit) 0% $ - 0 (G) Calculate the breakeven point in sales units
Production $ 2.55 $ 2.55 $ 2.55 Direct Labor: (H) Calculate the sales units needed to reach a
Selling - 0 - 0 - 0 (C) efficiency rate as minutes per pizza $200,000 net income target,
Total Variable Costs per unit $ 2.55 $ 2.55 $ 2.55 - 0 hourly labor cost plus fringe benefits $ 12.00
Contribution Margin $ - 0 $ - 0 $ - 0 $ - 0 Total direct labor per pizza $ - 0
CM per unit $ (2.55) $ (2.55) $ (2.55)
CM Ratio ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! Direct Materials:
Fixed Costs Dough $ 0.25
Selling (E) $ - 0 $ - 0 $ - 0 cheese 0.50
Administration (F) - 0 - 0 - 0 toppings 0.75
Total Fixed Costs $ - 0 $ - 0 $ - 0 $ - 0 caffinated tomato sauce 1.00
Boxes 0.05
Net Income $ - 0 $ - 0 $ - 0 $ - 0 Total direct material per pizza $ 2.55
Breakeven Point in sales units (G) Sales comissions (D)
Total Variable Selling Expenses per pizza $ - 0
Sales units for a Target Profit of $200,000 (H)

Cash Budget

Prepare a Cash Budget ACTUAL BUDGET
Nov Dec Jan Feb March Total Qtr
Cash Receipts
Sales Revenues $ 300,000 $ 200,000 $ - 0 $ - 0 $ - 0 $ - 0 (A) Calculate, based on the collection history, the cash receipts from customers
Cash Receipts from: (B) Calculate the cash disbursements for raw material purchases, assuming 1/2 of the
2 months ago (10%) $ - 0 - 0 previous month's purchases are paid in the current month
1 month ago (60% (A) - 0 - 0 Total Purchases
current month (25%) - 0 - 0 - 0 - 0 December January February March
total cash receipts $ - 0 $ - 0 $ - 0 $ - 0 ERROR:#DIV/0! $ - 0 $ - 0 $ - 0
Cash Disbursements
Raw Materials (B) - 0 - 0 - 0 (C) Decide in which month you will make a capital investment of $300,000
Selling Expenses - 0 - 0 - 0 - 0 (D) Determine the appropriate financing activities so as to keep at least the
Administrative Expenses - 0 - 0 - 0 - 0 required minimum cash balance of $ 75,000 and payoff any amount
total disbursements $ - 0 $ - 0 $ - 0 $ - 0 borrowed.
Net Operating Cash $ - 0 $ - 0 $ - 0 $ - 0
Investments:
Expand Business (C) - 0
Financing:
New Debt (D) - 0
Repay debt - 0
Net Cash Flow $ - 0 $ - 0 $ - 0 $ - 0
Beginning Cash Balance 75,000 75,000 75,000 75,000
Ending Cash Balance $ 75,000 $ 75,000 $ 75,000 $ 75,000
THE MINIMUM CASH BALANCE IS $ 75,000

Variances

Prepare a Flexible Budget
Conduct Variance Analysis
Master Budget Actual Flex Budget Variances (flex. vs. actual) (A) Verify that the net income is the same as the Op Budget
Sales : February per Unit February per Unit February per Unit Total per Unit (B) Input the flex budget sales units and the per unit price
(C) Input the flex budget production cost per unit and calculate
Units - 0 $ - 0 35,000 9.85 (B) (F) the total flex production costs.
Total Sales Revenue $ - 0 $ 344,750 $ - 0 (D) Input the flex budget variable selling costs per unit
(E) Input the flex budget fixed selling and administration costs
Variable Costs: (F) Calculate the variances between the actual and flex budget
Production $ - 0 $ 2.55 190,750 $ 5.45 (C) $ - 0 (F) (G) Input the spending variance
Selling - 0 - 0 3,850 0.11 (D) 0 (F) (H) Calculate the volume variance using the budgetted contribution margin
Total Variable Costs $ - 0 $ 2.55 $ 194,600 $ 5.56 $ - 0 $ - 0 $ - 0 $ - 0
Contribution Margin $ - 0 $ (2.55) $ 150,150 $ 4.29 $ - 0 $ - 0 $ - 0 $ - 0
Fixed Costs:
Selling - 0 60,000 - 0 (E) (F)
Administration - 0 58,500 - 0 (E) (F)
Total Fixed Costs - 0 118,500 - 0 - 0
Net Income $ - 0 (A) $ 31,650 $ - 0 $ - 0 (F)
Master Budget vs. Actual Net Income Variance $ 31,650
What is the volume variance (G)
What is the spending rate variance $ - 0 (H)
$ - 0

Decisions

Evaluate an outsourcing decision
Master Budget Outsource (A) Input the number of units to be produced (same as the sales for the quarter)
Relevant Costs Relevant Costs (B) From the master budget, input the variable costs per unit for production.
Units to Produce - 0 (A) (C) From the master budget, input the variable costs per unit for selling.
Variable Costs (stated as per unit) (D) Input the total fixed costs from the master budget
Production (B) (E) (E) Input the vendor's price $ 7.50
Selling - 0 (C) (F) (F) Input a $.05 incremental variable selling costs due to the outsourcing decision
Total Variable Costs per unit $ - 0 $ - 0 (G) Enter the total fixed costs that will NOT be avoided due to oursourcing
Selling: $50,000 Admin $40,000
Fixed Costs
Selling - 0 (D) (G)
Administration - 0 (D) (G)
Total Fixed Costs - 0 - 0
Total Costs $ - 0 $ - 0
Cost per unit produced ERROR:#DIV/0! ERROR:#DIV/0!

Capital

Prepare a discounted cash flow analysis
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Totals
Investment (300,000) (300,000)
Cash Flows
Sales 135,000 135,000 135,000 135,000 135,000 200,000 200,000 200,000 200,000 200,000 1,675,000
Salvage value (50,000) (50,000)
Operating Costs (95,000) (95,000) (95,000) (95,000) (95,000) (135,000) (135,000) (135,000) (135,000) (135,000) (1,150,000)
Major Maintenance (E) - 0
Net Cash Flow (300,000) 40,000 40,000 40,000 40,000 40,000 65,000 65,000 65,000 65,000 15,000 175,000
Cummulative CF (300,000) (260,000) (220,000) (180,000) (140,000) (100,000) (35,000) 30,000 95,000 160,000 175,000
Hurdle Rate 5%
Net Present Value (=NPV) (A) Use the Excel function =NPV in order to determine the value of the future cash flows, net of the initial investment
Internal Rate of Return (=IRR) (B) Use the IRR function in order to determine the "interest" earned on the cash flows from year 0 to year 10
Payback Period (C) Look at the cummulative cash flow. How long before the initial investment is paid off?
Profitability Index (D) Calculate the ratio of the project's PV of cashflows from years 1-10/ initial investment
Major Maintenance $ (85,000) (E) Decide in which year (5-7) this expenditure should be made. Add $5,000 for every year after year 5