ACC350 excel project INSTRUCTIONS This is an individual assignment. You may discuss this assignment with others, including
Question:
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/06/666cdc5c13883_332666cdc5c10ba5.jpg)
ACC350 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 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 minimummonthly 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 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
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/06/666cdc5c3d058_332666cdc5c3a135.jpg)