ACC350 excel project

The purpose of this assignment is to: o Help you understand and apply budgeting and variance calculations from Chapters 6, 7, and 8, and understand the usefulness of Excel for completing repetitive calculations o Gain an understanding of basic to intermediate Excel spreadsheet design and usage ? 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)

Arizona State University W.P. Carey School of Business ACC 350 - INTERNAL REPORTING Fall 2017 Excel Project INSTRUCTIONS This is an individual assignment. You may discuss this assignment with others, including the professor, but the work submitted should be your own work. This assignment is to be used with the spreadsheet downloadable from Blackboard called \"Lastname_Firstname_SectionXXXXX_ACC350_IndivProject.xlsx\" Rename the file by substituting your first and last name and section # Due date is Monday, November 6, 2017 at 11:30pm via upload to Blackboard site under Assignments > Excel Project (Note the changed due date, I'm giving you an extra weekend because this case is difficult. Waiting until the last day or two will likely end poorly). Overview The purpose of this assignment is to: o Help you understand and apply budgeting and variance calculations from Chapters 6, 7, and 8, and understand the usefulness of Excel for completing repetitive calculations o Gain an understanding of basic to intermediate Excel spreadsheet design and usage 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). You are asked to prepare an annual budget for the year 2016 and compute variances. You should fill in all cells highlighted in gray or pink. Everything should be done using an Excel process, function, calculation or formula referencing other cells as necessary. Sometimes, I inserted formulas for you (especially for the first month of January). Feel free to use these formulas as \"help,\" but use your own formulas if you prefer. Additional comments: 1. Almost 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 OR REFERENCES. 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 may lose points for this). 3. Format (use $ when referencing monetary values, no $ when not a dollar value, appropriate number of decimal places, etc.) your budgets in an appropriate manner. 4. See comments in the spreadsheet for additional guidance. 5. For the Cash budget, pay close attention to the following directions. Do not forget to subtract out Depreciation from the S&A and OH costs since they are non-cash. You must insert formulas in lines 16 and 17. The company must have a minimum monthly balance of cash of $25,000. Money can be borrowed and repaid on a monthly basis, by increments of $1,000, at an interest rate of 12% (annual rate). INTEREST IS PAID MONTHLY EVERY MONTH THERE IS A LOAN OUTSANDING STARTING ONE MONTH AFTER THE LOAN IS MADE. Requirements: You are required to create a 12-month budget (except as noted) with monthly columns and one 2016 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) 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) 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 OH Variances* (VOHVar; Variances for whole year, not monthly) 14. Fixed OH Variances* (FOHVar; Variances for whole year, not monthly) * for variance calculations in #'s 11-14 make sure to insert formulas to determine rules whether variances are \"U\" or \"F\" (using IF statements). Also, use formulas to check whether the sum of the components equal the total (for instance insert a formula that would check whether total DM variance equals the sum of MPV and MUV). Notes to Remember: DM Purchase Budget: - Less: Beginning Inventory. For the Month of January, disregard the comment and use the beginning inventory provided in the DATA tab (cell B12 for Part #K298 and cell B17 for Part #C30) Cash Budget - Formulas to determine amount of financing (borrowing, repayment, interest): - INTEREST IS PAID MONTHLY EVERY MONTH THERE IS A LOAN OUTSANDING STARTING ONE MONTH AFTER THE LOAN IS MADE. For instance, if a loan is made in September, interest is paid every month starting October and until the loan is repaid. Remember that interest has an annual rate of 12%. - You should use the Cash Ending Balance in Line 14 as well as the Interest Paid in Line (17 or 18) when determining loan/repayment decisions. - Do not type in \"25,000\" in your formula. You should be making a reference to the cell in the DATA tab that has the minimum monthly ending cash balance. - For the Repayments formulas, you need to take into account ALL of the borrowings made until that point less the repayments made, to determine how can/should be repaid. - In general, remember to use as few numbers as possible. Instead, use references to data in other tabs (other budgets or the DATA tab). - If you decide to use the ROUND function, think of whether you should use the ROUNDUP or ROUNDDOWN formula. - Remember that interest is a payment, therefore a negative number in the cash budget. - For January, you may type \"$0\" (i.e., not insert a formula) for the Repayments and the Interest paid. However, you still need to insert a formula to determine whether to take a loan. 2016 Budge Projected Sales Units Projected Selling Price Beg Inventory Units 01/01/16 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 April 16,000 $110.00 Direct Materials Part #K298: Part #K298 Per-Unit Usage Part #K298 cost Beg Inventory Qty 01/01/16 Desired Ending Inventory % Part #C30: Part #C30 Per-Unit Usage Part #C30 cost Beg Inventory Qty 01/01/16 Desired Ending Inventory % 3.00 per unit $7.00 per unit of #C30 10,080 units of #C30 30.00% of next month's production needs Direct Labor: DL Hrs per Unit DL Avg Cost per DLH 1.50 per unit $20.00 $20.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 Selling & Admin: Salaries Commissions Depreciation Shipping Other Fixed Var (per unit) $88,500 $0.00 $0 $1.40 $25,000 $0.00 $0 $3.60 $137,000 $1.60 Planned Capital Expenditures Cash: Beg Balance 01/01/16 Minimum Monthly Balance Minimum Cash Borrow Annual Interest Rate Montly Interest Rate May 18,500 $115.00 $20.00 $20.00 $20.00 $0 $0 $0 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 $0 $68,000 $62,900 $25,000 $1,000 12.00% (use below rate for monthly interest calculations) 1.00% (use this for monthly rate) Actuals (use in Variances): Annual Sales (in $) Annual Sales Units Annual Production Units $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 total cost 405,000 Annual $1,579,500 Total 2016 Budget Information June July August 20,000 20,000 20,000 $115.00 $120.00 $120.00 September 18,000 $120.00 October 17,000 $125.00 November 16,000 $125.00 December 15,000 $125.00 $20.00 $22.00 $22.00 $22.00 $22.00 $22.00 $22.00 $500,000 $0 $0 $0 $0 $0 $0 Total 194,000 2017 January 14,000 $125.00 February 13,000 $125.00 Schedule 1: Sales Budget Units Unit SP Sales January February 10,000 $110 ### March April May June July August September October November December Total Schedule 2: Production Budget January Unit Sales (sched 1) Desired Ending Inventory Total Units Needed Less: Beginning Inventory Units to Produce - February March April May June July August September October November December Total Needed for DM Purchases Budget January February 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 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 January $0 $0 February March April May June July August September October November December Total 2017 January January Schedule 4: DL Budget January Units Produced (sched 2) x DL per Unit Total Hours Needed x Wage Rate per Hour Total DL Cost $0 February March April May June July August September October November December Total Schedule 5: OH Budget January Budget DL Hours (sched 4) x Variable OH Rate Budgeted Var OH Budgeted Fixed OH Total Budgeted OH $0 $0 February March April May June July August September October November December Total 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 $0 $0 $0 February March April May June July August September October November December Total Schedule 7: Ending FGI Budget Unit Cost Computation DM: Part K298 Part C30 DL OH: Var Fixed Total Unit Cost x Units in FGI @ 12/31 EB FGI 12/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 K298 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 Cost Amt $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Schedule 9: Budgeted IS for the Year Ended 12/31/2016 2016 Total Sales (sched 1) Less: COGS (sched 8) Gross Margin Less: S&A Expenses Total Budgeted NIBT $0 $0 Schedule 10: Cash Budget January February $62,900 March April Beginning Balance Plus: Cash Receipts (sched 1) Total Cash Available $62,900 Disbursements: Purchases (sched 3) DL Payroll (sched 4) Overhead (sched 5) Marketing & Admin (sched 6) Land Total Disbursements $0 Ending Balance $62,900 Financing: Loans Insert Formula for Loans Repayments Insert Formula for Repayments Interest paid Insert Formula for Interest Paid Ending Cash Balance $62,900 May June July August September October November December Total Schedule 11: DM Variances (Part #K298 ONLY) AQ x AP AQ x SP SQ x SP $0.00 $0.00 $0.00 Material Purchase Price Variance Material Usage Variance $0.00 Insert Rule for U or F? $0.00 Insert Rule for U or F? Overall DM Variance $0.00 Insert Rule for U or F? Insert Rule to Check whether sum of components equal the total ents equal the total Schedule 12: DL Variances AH x AR AH x SR SH x SR $0.00 $0.00 $0.00 Labor Rate Variance Labor Efficiency Variance $0.00 Insert Rule for U or F $0.00 Insert Rule for U or F Overall DL Variance $0.00 Insert Rule for U or F Insert Rule to Check whether sum of components equal the total Schedule 13: VOH Variances AH x AVOR $0.00 AH x SVOR $0.00 SH x SVOR $0.00 Spending Variance Efficiency Variance $0.00 Insert Rule for U or F? $0.00 Insert Rule for U or F? Overall VOH Variance $0.00 Insert Rule for U or F? Insert Rule to Check whether sum of components equal the total ual the total Schedule 14: FOH Variances Actual FOH Budgeted FOH Applied FOH Spending Variance Volume Variance $0.00 Insert Rule for U or F $0.00 Insert Rule for U or F Overall FOH Variance $0.00 Insert Rule for U or F Insert Rule to Check whether sum of components equal the total