Directions:
Adapt your schedules for the specific details outlined in the requirements above. Prepare your budgets using Excel. Use formulas and cell references so that any change you make in one budget is carried through to all the budgets. There should be no hard keyed numbers in your formulas. For example, if you change the ‘sales volume increase' from 15% to 12% you should see effects of that change throughout the other budgets. Likewise, if the budgeted selling price per book case changes from $280 to $285 your spreadsheet model should be able to quickly and easily accommodate this change, i.e., change the input cell for budgeted selling price and see the effect on income.
The spreadsheet will be graded on presentation, correctness, and quality of your spreadsheet model (i.e., does it update correctly for changes in input variables). See the grading rubric on Canvas.
You should approach this assignment as if you are the Management Accountant at the BookCases Company and you are going to present these budgets in a meeting to the CEO, CFO, and other management personnel.
Some general principles to follow in constructing your excel spreadsheet model:
1.Prepare an input area in which you enter all input variables - e.g., selling price, budgeted volume increase, feet per book case, ending inventory percentage, etc...
2.Each schedule should refer to the input area to source data (see sample spreadsheet file). If possible, keep all constant values together in one area of the worksheet. An important principle of good spreadsheet design is to keep just one copy of each constant value. That is, enter a constant value in only one location in the worksheet. Then if you use the value in another cell, use a cell reference that refers to the constant value's unique location.
Example (hypothetical): You enter the constant value of 6% for sales tax in cell E5. When you write a formula in your worksheet that requires sales tax, reference E5 in the formula instead of "hard coding" in the 6% value.
Do: =subtotal*E5
Don't: =subtotal*6%
3.There should be no hard-keyed numbers in your formulas - e.g., the formula to determine current period sales in units should reference last year's sales volume and a cell with the volume percentage increase.
4.Label and format appropriately - e.g., use $ to format dollar amounts, format cells for decimal places, etc...
Submit your Excel spreadsheet to Canvas under Spreadsheet Assignment