Overview

• The purpose of this assignment is to:
  • Help you understand and learn budgeting and variance calculations from Chapters 8 and 9 and understand the power of Microsoft Excel for completing repetitive calculations,
  • Gain an understanding of basic to intermediate spreadsheet design and usage (e.g., formulas, functions, graphs, regression calculations, etc.)
  • In particular, formulas, relative copy and paste, input cells, etc.
• This exercise relies heavily on the structure of the solutions presented in the Excel solution to Problems 8.36 and 9.32.
• Instead of using the data from these problems, use the data presented on the "DATA" tab (Do not change the layout or format of this tab nor change any data on this tab).
• For a starting point, I left the "Skeletal" structure of the Excel solutions to problems 8.36 and 9.32 on the appropriate tabs. You will need to adjust these formulas and add columns for months April - December as appropriate/directed in the requirements.
• You have the necessary background from prior classes as well as the ability to use the internet to look up and figure out how to solve the requirements above. Most requirements involve using formulas and functions.
• Everything should be done using an Excel process, function, calculation or formula referencing other cells as necessary.
  • None of your budgets/schedules should involve "Hard Coding" (i.e., entering numbers in cells instead of referencing on cells and performing calculations). ALL NUMBERS IN THE BUDGETS SHOULD COME FROM CALCULATIONS.
  • Note that if you cannot figure out how to perform a certain step, you may need to "Hard Code" in values so that you can do the additional steps that are required (note that you will be knocked off points for this).
  • Use absolute (e.g., $ in referencing a cell) references and copy/paste in formulas where applicable.
  • Format (use $ when referencing monetary values, no $ when not a dollar value, appropriate number of decimal places, etc.) your budgets in an appropriate manner.

Requirements:

• You will be required to create a 12 month budget (except as noted) with monthly columns and one 2015 total column for each of the following items (on the following tabs listed in parenthesis):
• Sales Budget (Sales)
• Production Budget (Production)
• DM Purchases Budget (DM Purchases)
• DL Budget (DL)
• OH Budget (OH)
• Selling and Administrative Expense Budget (SellingAdmin)
• Ending FGI Budget (FGI; more of a calculation than a 12 month "Budget"; Base Costs on December costs)
• COGS Budget (COGS; more of a calculation than a 12 month "Budget"; you may assume the Beginning Inventory cost per unit is equal to the cost per unit calculated in the Ending FGI Budget)
• Budgeted Income Statement (IS)
• Cash Budget (Cash; difficult => see assumptions listed on data page; don't forget to subtract out Depreciation from the S&A and OH costs since they are non-cash)
• Direct Material Variances* (MatVar; Variances for Part #K298 only for the whole year, not monthly)
• Direct Labor Variances* (DLVar; Variances for whole year, not monthly; use annual weighted average budgeted rate)
• Variable MOH Variances* (VMOHVar; Variances for whole year, not monthly)
• Fixed MOH Variances* (FMOHVar; Variances for whole year, not monthly)
• When you are done with all of your budgets/assignment and before you submit, on the "Answers" tab of your solution spreadsheet in the highlighted (Yellow cells) reference (do not type or "hard-code") the numbers from the particular cell in the budget (i.e., start with "=" then click on the tab of the budget and the cell that contains the number) that is being requested.
  • Note that column "D" (F/U) must be filled out (cell references, not typing/hard-coding "F" or "U") for the Variance solutions (#'s 11-14).

* for variance calculations in #'s 11-14 make sure to calculate the "U" or "F" for each variance type (hint: use if statement to calculate whether a "U" or "F")

ASU ACC 350 - Internal Reporting Garverick - Spring 2016 Excel Assignment #2 Overview The purpose of this assignment is to: o Help you understand and learn budgeting and variance calculations from Chapters 8 and 9 and understand the power of Microsoft Excel for completing repetitive calculations, o Gain an understanding of basic to intermediate spreadsheet design and usage (e.g., formulas, functions, graphs, regression calculations, etc.) In particular, formulas, relative copy and paste, input cells, etc. This exercise relies heavily on the structure of the solutions presented in the Excel solution to Problems 8.36 and 9.32. Instead of using the data from these problems, use the data presented on the \"DATA\" tab (Do not change the layout or format of this tab nor change any data on this tab). For a starting point, I left the \"Skeletal\" structure of the Excel solutions to problems 8.36 and 9.32 on the appropriate tabs. You will need to adjust these formulas and add columns for months April - December as appropriate/directed in the requirements. You have the necessary background from prior classes as well as the ability to use the internet to look up and figure out how to solve the requirements above. Most requirements involve using formulas and functions. Everything should be done using an Excel process, function, calculation or formula referencing other cells as necessary. 1. None of your budgets/schedules should involve \"Hard Coding\" (i.e., entering numbers in cells instead of referencing on cells and performing calculations). ALL NUMBERS IN THE BUDGETS SHOULD COME FROM CALCULATIONS. 2. Note that if you cannot figure out how to perform a certain step, you may need to \"Hard Code\" in values so that you can do the additional steps that are required (note that you will be knocked off points for this). 3. Use absolute (e.g., $ in referencing a cell) references and copy/paste in formulas where applicable. 4. Format (use $ when referencing monetary values, no $ when not a dollar value, appropriate number of decimal places, etc.) your budgets in an appropriate manner. Requirements: You will be required to create a 12 month budget (except as noted) with monthly columns and one 2015 total column for each of the following items (on the following tabs listed in parenthesis): 1. Sales Budget (Sales) 2. Production Budget (Production) 3. DM Purchases Budget (DM Purchases) 4. DL Budget (DL) 5. OH Budget (OH) 6. Selling and Administrative Expense Budget (SellingAdmin) 7. Ending FGI Budget (FGI; more of a calculation than a 12 month \"Budget\"; Base Costs on December costs) ASU ACC 350 - Internal Reporting Garverick - Spring 2016 Excel Assignment #2 8. COGS Budget (COGS; more of a calculation than a 12 month \"Budget\"; you may assume the Beginning Inventory cost per unit is equal to the cost per unit calculated in the Ending FGI Budget) 9. Budgeted Income Statement (IS) 10. Cash Budget (Cash; difficult => see assumptions listed on data page; don't forget to subtract out Depreciation from the S&A and OH costs since they are non-cash) 11. Direct Material Variances* (MatVar; Variances for Part #K298 only for the whole year, not monthly) 12. Direct Labor Variances* (DLVar; Variances for whole year, not monthly; use annual weighted average budgeted rate) 13. Variable MOH Variances* (VMOHVar; Variances for whole year, not monthly) 14. Fixed MOH Variances* (FMOHVar; Variances for whole year, not monthly) 15. When you are done with all of your budgets/assignment and before you submit, on the \"Answers\" tab of your solution spreadsheet in the highlighted (Yellow cells) reference (do not type or \"hard-code\") the numbers from the particular cell in the budget (i.e., start with \"=\" then click on the tab of the budget and the cell that contains the number) that is being requested. Note that column \"D\" (F/U) must be filled out (cell references, not typing/hardcoding \"F\" or \"U\") for the Variance solutions (#'s 11-14). * for variance calculations in #'s 11-14 make sure to calculate the \"U\" or \"F\" for each variance type (hint: use if statement to calculate whether a \"U\" or \"F\") Budget/Schedule 1 Sales Budget 2 Production Budget 3 DM Purchases Budget 4 DL Budget 5 OH Budget 6 S&A Expense Budget 7 Ending FGI Budget 8 COGS Budget 9 Budgeted IS 10 Cash Budget 11 DM Variances 12 DL Variances 13 VMOH Variances 14 FMOH Variances Amount Description $22,832,500 Annual Total Sales 195,900 Annual Total Production Units 393,360 Total K298 Purchase Units $306,600 December C30 Purchase Cost $488,400 December DL Purchase Cost $248,380 December Total Budgeted OH $349,500 December Total Budgeted S&A $77.76 Total Unit Cost 12/31 $69,985 Add: Beg FGI $8,020,131 Gross Margin $761,435 May Ending Cash Balance $40,500 Favorable DM Price Variance $344,020 Unfavorable Total DL Variance $54,500 Unfavorable Total VMOH Variance $32,236 Favorable Total FMOH Variance Projected Sales Units Projected Selling Price Beg Inventory Units 01/01/15 Desired Ending Inventory % January February March 10,000 10,500 13,000 $110.00 $110.00 $110.00 900 20.00% of next month's sales units 2015 Budget Information June July 20,000 20,000 $115.00 $120.00 April 16,000 $110.00 May 18,500 $115.00 $20.00 $20.00 $20.00 $20.00 $0 $0 $0 $500,000 Direct Materials Part #K298: Part #K298 Per-Unit Usage Part #K298 cost Beg Inventory Qty 01/01/15 Desired Ending Inventory % Part #C30: Part #C30 Per-Unit Usage Part #C30 cost Beg Inventory Qty 01/01/15 Desired Ending Inventory % 1.50 per unit $20.00 $20.00 September 18,000 $120.00 October 17,000 $125.00 November 16,000 $125.00 December 15,000 $125.00 $22.00 $22.00 $22.00 $22.00 $22.00 $22.00 $0 $0 $0 $0 $0 $0 3.00 per unit $7.00 per unit of #C30 10,080 units of #C30 20.00% of next month's production needs Direct Labor: DL Hrs per Unit DL Avg Cost per DLH August 20,000 $120.00 2.00 per unit $4.00 per unit of #K298 6,720 units of #K298 30.00% of next month's production needs Overhead: Supplies Power Maintenance Supervision Depreciation Taxes Other Fixed (monthly) Var (per DLH) $0 $1.00 $0 $0.20 $12,500 $1.10 $14,000 $0.00 $45,000 $0.00 $4,300 $0.00 $86,000 $1.60 Selling & Admin: Salaries Commissions Depreciation Shipping Other Fixed (monthly) Var (per unit) $88,500 $0.00 $0 $1.40 $25,000 $0.00 $0 $3.60 $137,000 $1.60 Planned Capital Expenditures $0 $68,000 Cash: Beg Balance 01/01/15 Minimum Monthly Balance Minimum Cash Borrow Annual Interest Rate Montly Interest Rate Actuals (use in Variances): Annual Sales $$$ Annual Sales Units Annual Production Units $62,900 $25,000 $1,000 (minimum increment - see "rules" on p. 439, part 8.36 (h)) 12.00% 1.00% (use this) $24,000,000 198,000 200,000 Actual FMOH Actual VMOH Actual DL Hours Total DL Cost $1,950,000 Annual $1,224,500 Annual 310,000 Annual $6,665,000 Annual Part #K298 purchase units Part #K298 purchase cost Part #K298 usage units 405,000 Annual $1,579,500 Total 401,000 Annual Total 194,000 2016 January 14,000 $125.00 February 13,000 $125.00 Budget/Schedule 1 Sales Budget 2 Production Budget 3 DM Purchases Budget 4 DL Budget 5 OH Budget 6 S&A Expense Budget 7 Ending FGI Budget 8 COGS Budget 9 Budgeted IS 10 Cash Budget 11 DM Variances 12 DL Variances 13 VMOH Variances 14 FMOH Variances Amount F/U Description Annual Total Sales Annual Total Production Units Total Annual K298 Purchase Units Total Annual K298 Purchase Cost Total Annual C30 Purchase Units Total Annual C30 Purchase Cost Total Annual DL Cost Total Annual Budgeted OH Total Annual Budgeted S&A EB FGI 12/31 Budgeted COGS Total Budgeted NIBT June Ending Cash Balance DM Price Variance DM Usage Variance DL Rate Variance DL Efficiency Variance VMOH Spending Variance VMOH Efficiency Variance FMOH Spending Variance FMOH Volume Variance Schedule 1: Sales Budget Units Unit SP Sales January 10,000 $110 $1,100,000 February March Total 10,000 Weighted Avg SP $0 $0 $1,100,000 Schedule 2: Production Budget January Unit Sales (sched 1) Desired Ending Inventory Total Units Needed Less: Beginning Inventory Units to Produce February March #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! Needed for Purchases Budget Total Jan-16 Feb-16 - #REF! #REF! #REF! #REF! N/A Schedule 3: DM Purchases Budget Part K298 Units Produced (sched 2) x DM per Unit Production Needs Desired EI Total Needed Less: Beginning Inventory DM to Purchase x Cost per Unit Total K298 Purchase Cost January #REF! Part C30 Units Produced (sched 2) x DM per Unit Production Needs Desired EI Total Needed Less: Beginning Inventory DM to Purchase x Cost per Unit Total C30 Purchase Cost February #REF! March #REF! Total #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! Err:509 #REF! #REF! #REF! #REF! #REF! #REF! #REF! Err:509 #REF! #REF! #REF! #REF! #REF! #REF! #REF! January #REF! February #REF! March #REF! Total #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! Err:509 #REF! #REF! #REF! #REF! #REF! #REF! #REF! Err:509 #REF! #REF! #REF! #REF! #REF! #REF! #REF! Schedule 4: DL Budget Units Produced (sched 2) x DL per Unit Total Hours Needed x Wage Rate per Hour Total DL Cost January #REF! February #REF! March #REF! Total #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! Schedule 5: OH Budget Budget DL Hours (sched 4) x Variable OH Rate Budgeted Var OH Budgeted Fixed OH Total Budgeted OH January #REF! February #REF! March #REF! Total #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! Schedule 6: Selling & Admin Budget January Budget Sales Units (sched 1) x Variable S&A Rate Budgeted Var S&A Fixed S&A: Salaries Depr Other Total Fixed S&A Total Budgeted S&A February March Total $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Schedule 7: Ending FGI Budget Unit Cost Computation DM: Part K29 Part C30 DL OH: Var Fixed Total Unit Cost x Units in FGI @ 12/31 EB FGI 3/31 Qty Cost Amt $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Schedule 8: COGS Budget DM Used (sched 3): Part K29 Part C30 DL Used (sched 4) OH (sched 5) Bud Mnfg Costs Add: Beg FGI Goods Avail for Sale Less: FGI (sched 7) Bud COGS Qty x Cost $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Schedule 9: Budgeted IS 2015 Total Sales (sched 1) Less: COGS (sched 8) Gross Margin Less: S&A Expenses Total Budgeted NIBT $0 $0 Schedule 10: Cash Budget January Beginning Balance Plus: Cash Receipts (sched 1) Total Cash Available Disbursements: Purchases (sched 3) DL Payroll (sched 4) Overhead (sched 5) Marketing & Admin (sched 6) Land Total Disbursements Ending Balance Financing: Borrowed/repaid Interest paid Ending Cash Balance $1,100,000 $1,100,000 February March Total $0 $1,100,000 $0 $1,100,000 $0 $1,100,000 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $1,100,000 $0 $0 $1,100,000 $25,000 $0 $25,000 $25,000 $50,000 ($250) ($250) $24,750 $1,149,750 Schedule 11: DM Variances (Part #K298 ONLY) AQ Purch x 135,400 AP $5.36 $725,744.00 SP $5.40 $731,160.00 Material Purchase Price Variance -$5,416.00 F *** Note AQ Purchased & Used are different *** AQ Purch x 135,400 AQ Used x 145,400 SP $5.40 $785,160.00 SQ* x 144,200 Material Usage Variance $6,480.00 U SP $5.40 $778,680.00 Schedule 12: DL Variances AH x 36,500 AR $17.98 $656,270.00 AH x 36,500 SR $18.00 $657,000.00 Labor Rate Variance -$730.00 F Overall Labor Variance $7,370.00 U SH* x 36,050 Labor Efficiency Variance $8,100.00 U SR $18.00 $648,900.00 Schedule 13: VMOH Variances VOH Actual VOH (= AQ x AR) 36,500 $4.60 $168,000.00 AH x 36,500 SVOR $4.00 $146,000.00 Spending Variance $22,000.00 U Overall VOH Variance $23,800.00 U SH* x 36,050 Efficiency Variance $1,800.00 U SVOR $4.00 $144,200.00 Schedule 14: VMOH Variances FOH Actual FOH Budgeted FOH Applied FOH SH* x SFOR 36,050 $3.00 $110,000.00 $105,000.00 $108,150.00 Spending Variance Volume Variance $5,000.00 U -$3,150.00 F Overall FOH Variance $1,850.00 U