Assumptions: Prices and per unit variable costs are expected to remain the same

Question

Assumptions: Prices and per unit variable costs are expected to remain the same throughout the quarter.

Angie just received a standing order for an additional 400 trays per month starting in October. In addition, Angie expects Sales to increase (based on September’s sales) an additional 16% per month in October and November. In December, sales are expected to increase by 40% over November’s sales due to increased holiday restaurant demand (note the advertising expense below). Sales are expected to remain at the December level in the following quarter. The Empanada price per tray is expected to remain the unchanged in the quarter.

Direct Materials: Costs per unit of direct materials are expected to remain the same throughout the quarter. Because of problems procuring certain ingredients Angie would like to have 5% of her ingredients (spices, flour, shortening, etc) needed to prepare that month’s empanadas on hand at the beginning of each month. Angie also would like to have 50% of the trays she will need on hand at the beginning of each month. Assume that Angie has no inventory of ingredients or trays on hand at the end of September.

Direct Labor: Base the Labor budget on the current quarter’s information.

Manufacturing Overhead: (Rent, Utilities) Angie’s rent will increase 15% because she will need to rent additional space and equipment to meet the anticipated increase in demand. Utility rates are expected to remain the same for the coming quarter. Variable Manufacturing Overhead should be applied based on trays of empanadas produced.

S&A expense: (delivery, advertising) Angie is budgeting for $500 a month in advertising expense for the coming quarter.

Because of increased fuel prices in the coming quarter, variable delivery costs are expected to rise 20%.   Monthly Delivery expenses include depreciation on a van Angie invested into the business. Details on the van:

Value at the time of donation: $9,400.

Expected useful life: 3 years. Date of Donation April 1.

Salvage value: $1,120

Accumulated depreciation to September 30: $1,380

Finished Goods Inventory assumptions: Angie would like to have 6% (approximately two day’s sales) of expected demand on hand at the end of each month. Currently Finished Goods inventory is 0.

Cash flow assumptions: Sales are 40% cash and 60% credit. Credit receivables are expected to be paid in full the next month. No allowance is made for uncollectable accounts. Angie’s current cash balance is equal to the amount listed on the attached beginning balance sheet. Angie started the business with a cash investment of $8,000 and the donation of the delivery truck.

All Purchases of ingredients and trays are on credit. Angie pays for ingredients and trays in full the next month. Base current Accounts Payable on September’s purchases (see the balance sheet).

Because of cash flow problems encountered in the previous quarter Angie arranged for a $30,000 line of credit from a local bank at 6% annual rate of interest on the principle. In consultation with her accountant Angie decided she would like to have a $9,000 cash balance at the end of each month. Based on anticipated balances in the budget Angie borrows all money in $1,000 increments at the beginning of the month and if possible will repay all money plus any interest owed at the end of the Quarter.

Your Variable Costing Income Statement from the previous quarter can be found at the bottom of the questions and figures spreadsheet.

After completing the Static budget develop a Pro-forma Income Statement and Balance Sheet for Angie’s Empanadas for the next quarter (October-December).

Your Static Budget should include budgets for:

Sales and Cash collections from sales

Production

Materials: ingredients and trays

Cash Disbursements for materials

Direct labor

Manufacturing Overhead

Selling & administrative expenses

Cash Budget

Cost of Good Manufactured and Cost of Goods sold

Proforma Income Statement (use format included) Variable Costing

Proforma Balance Sheet (use format included)

Build the budgets by linking the worksheets together with formulas using the assumptions provided. You will need to determine inventory amounts (materials and Finished Goods, not work-in-process) for the Balance Sheet and to determine the COGS. Use variable cost of goods sold. Please note that delivery costs are S & A costs. Also please note that depreciation (a part of delivery costs) is a non cash cost.

make sure it is not just a typed document (has links and formulas).

However, since I couldn't uplaod Excel files nor word files that work.

I took screen shots of everything and I hope the answers will be in excel sheets

Input your M number in the Name &Mnumber; spreadsheet and use the figures Cost_Volume_Profit Analysis & the behavior of costs Price per tray $11.50 Over the past six months Angie s incurred the following costs and made the sales revenues elated to her empanada business Empanada Ingredients Labor Costs Sales Travs Rent Utilities Deliver y Profit Quantity April 3,450.00 $ 690.00 1,207.50$ 90.00 1,000.00 $ 112.00 700.00(349.50) May $ 4,025.00$ 805.00$1,408.75 $105.00 $1,000.00$ 114.00 $ 750.00(157.75) June $5,175.00$ 1,035.00 $1,811.25$ 135.00$1,000.00$118.00 $ 850.00 225.75 July 5,750.00 $1,150.002,012.50$150.00 $1,000.00$120.00 $900.00417.50 August $7,475.00 $ 1,495.00 $ 2,616.25 195.00 1,000.00 126.00 $1,050.00 992.75 September12,650.002,530.00$4,427.50 330.00$1,000.00$144.00 1,500.00 2,718.50 734.00$5,750.00$ 3,847.25 300 350 450 500 650 1,100 3,350 Totals $ 38,525.00$7,705.00 $13,483.75 $1,005.00$ 6,000.00 2.30 $ 4.03 $ 0.30 slope VCu Int FC Mo 0.04$ 1.00 Answer the following questions 100 400 Month Angie first earns a profit? Variable cost items? Mixed Cost items? Fixed Cost item? Ingredients Utilities Rent Labor Delivery Trays Contribution Margin per tray? $3.84 Contribution Margin percentage? 33.3%

Explanation / Answer

Remarks : Unable to upload Excel Linked file, so the solution is pasted here from Excel File

Interest expense

Depriciation expense

450

690

October November December 1276 1276 1786 Additional Order 400 400 400 Total Sale 1676 1676 2186 Price 11.50 11.50 11.50 Total Sale ($) 19274 19274 25144 Emphada Ingrediets 3854.8 3854.8 5028.72 Budget for Emphada 4047.54 3913.50 5028.72 Requirement for Trays 754.2 579.3 655.8 labour 6754.28 6754.28 8811.19 rent 1150 1150 1150 Utilities 167.04 167.04 187.46 Delivery 2178.8 2178.8 2842.32 Advertisement 500 500 500 Income Statement (for quarter ended 31 December) Sales 63691.6 Less Variable Cost -Ingredients 12989.76 -Tray 1989.3 -Labour 22319.75 - Rent 450 - Utilities 221.54 - delivery 7199.92 45170.26 Contribution 18521.34 Less Fixed Cost - Rent 3000 - Utilities 300 - Advertisement 1500 4800

Interest expense

Depriciation expense

450

690

Net Income 12581.34 Balance Sheet Items 1 Loan 30000 Intt @ 6% per annum Intt payment 150 150 150 (Recogniosed in P&l a/c) Closing Balance 29550 2 Cash Buget Oct Nov Dec Opening 7885 40899.28 44621.42 Loan received 30000 Cash from sale 16624 19274 22796 Payments 2860.00 4801.74 4492.80 10750.12 10750.12 13490.97 Closing balance 40899.28 44621.42 49433.42 Cash balance 9000 9000 9000 Entered in balance sheet Bank Balance 31899.28 35621.42 40433.42 3 debtors Opening 8223 Add : Sales 63692 Less cash Collected 58694 Closing Balance 13220 4 Creditors Opening 2860 Purchases 14979.06 paid For 12154.54 Closing Balance 5684.52 5 Depriciation Asset 9400 Accumulated dep till 30 sep 1380 Dep for quarter 690 WDV for Asset 7330

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.