Copy BA Format Painter Undo Clipboard G Font 615 B 1 Function: SUM; Formulas: Subtract, Multiply; Cell Referencing D E of Wrap Beat Merge & Center $ Alignment Accent Accun Conditional Format as Formatting- Table Accento Comma Number E Styles 2 BE6.3-Using Excel to Create a Production Budget 4 PROBLEM 5 Filling in for his supervisor, who suddenly left the company, Harvey. 6 quickly evaluated the situation to wrap up his department's 7 production budget. The department currently holds enough inventory 8 to be in compliance with Its standard which is based on the following 9 month's budgeted sales. From the sales and previous budgets, he 10 learned the budgeted unit sales for the next 4 months and the 11 standard for holding inventory at month end which are both presented Student Work Area Required: Provide input into cells shaded in yellow in this template. Input the required mathematical formulas or functions with cell references to the Problem area or work area as indicated. Show the production budget that Harvey would assemble for the next quarter. Month 1 12 Budgeted sales volume 5,000 4,500 Month 2 Month 3 6,000 Quarter 15.500 13 Unit sales per month Target ending FG Inventory 500 450 600 1,550 14 Month 1 5,000 Total units needed 5,500 4,950 6.600 17,050 15 Month 2 4,500 Beginning FG Inventory 16 Month 3 6,000 Budgeted units to be produced 17 Month 4 6,500 18 Ending Inventury standard 19 Materials held at month and 10% 20 21 22 23 24 Undo Uppcard Angement 4 41 the Tunctions: RB, SLN, Formulas: Subtract, Divide, Add, Multiply; Call Referencing Functions: IRR, SLN; Formulas: Subtract, Divide, Add, Multiply; Cell Referencing BE7.3-Using Excel to Calculate the Simple Payback Period and IRR PROBLEM Ava just graduated from college. Since she is starting her own b business, it's time to upgrade from her chunker to a reliable vehicle 7 Ava has the option to purchase a new rar for her business at a cost, Bestimating that it would help her bring in additional annual net operating cash flows. Information pertaining to the propoved 10 Investment is presented here. 11 Cost of new car 25,000 7 H Student Work Area Required: Provide input into cells shaded in yellow in this template Input the requised mathematical formulas or functions with cell references to the Problem area or work area as indicated Determine the simple payback period, Simple payback period years 12 Estimated useful life in years 13 Estimated salvage value Estimated additional net operating cash 15 flows over the life of the car 5 8,000 16 Expected tax rate 30% 17 18 19 20 21 22 22 24 95 26 27 28 09 30 31 32 BA 34 35 Enter Answer + Real 1ncessinuity food to gu Unde Termat Painter Clipooand E Al Find Determine the annual depreciation expense using the SLN tumetion. Annual depreciation Determine the after-tax annual cash flows. After tax cash Hows List the cash flows by year and determine the IR for this investment Year Cast How Year B Year Year 5 Menge Cente Abarment Ex Functions: SLOPE INTERCEPT Cell Referencing Functions: SLOPE, INTERCEPT; Cell Referencing E3.9 Using Excel to Determine High and Low Points PROBLEM 5 For the past 2 years, Teagan has winked in a machining 6 facility, helping the company track and recurd its wide variety af custs. She has organized the data quarterly, to correspond 8 with the quarterly budget/actual meetings, with the following data obtained for power costs. Quarter Machine Hours Power Cost G Student Work Area Required: Provide input into cells shaded in yellow in this template. Input the required mathematical formulas or functions with cell references to the Problem area or work area as indicated. Calculate the variable and fixed components of power costs using the SLOPE and INTERCEPT functions in Excel 10 11 12 28,000 $ 36,400 Slope. 23 35.000 53,200 14 3 42,000 59,500 intercept 15 A 30,800 51,800 15 29,400 47,600 Complete the cost equation. 27 6 25,200 40,600 18 y 33.600 50,400 Total cost X 39 39,200 53,400 20 9 $7,000 $2,000 21 10 31.900 48,500 22 23 l'ower costs used to be a mystery to the company before 24 the new production supervise came un board, just 6 months 25 alter Teagan started. Tragan heard though the grapevine 26 that employees and management in the factory used to leave 27 equipement running even when there was not an active. 28 production run in process. In utner cases, they shut everything. 20 down right in the middle of a shift to celebrate employe 30 milestones will cake. These are no such activities now, as the 31 new supervisor is running a tight ship. 32 34 35 Ready Enter Answer Access by instigate O S N O + Shyte Canna 19 D 9