Required: Part 1.
Read Part I of the case carefully and create a spreadsheet page for data input as you go. This is where you will find all the numbers necessary to create your budgets as you move through the case. It is important that you think through what numbers you willneedandlaythoseoutlogicallywithclearlabels. Thisservestwopurposes:first, it will make it easier to create the budgets, and second, when you turn this budget over to management, they may wish to change some of the assumptions and see what results flow through the budget. To this end, you must show all assumptions on the first worksheet.
You will need to create the following items:
Youmayalsoneedtocreatesupportingschedulesandtables. Pleasegroupthese logically on worksheets within your document and label the tabs so that the owners and managers can find whatever budget or schedule they need. Think about the logical order of budgets. Your data input sheet should be the first worksheet. What should be the second worksheet? Make the spreadsheet document flow logically.
Have the spreadsheet show most dollar amounts as whole dollars (in some cases, such as the price of an ingredient at $0.15, it won’t make sense to do so –use your judgment). Similarly, use your judgment on how many decimals to display and what format to use ($ or % for example) in each cell. Remember, that the calculations will still be accurate as Excel uses the decimal part of the number even if it isn’t showing. Also, a good use of bold, italics, colour, and borders will make the spreadsheet more visually appealing and, if used correctly, easier to understand and use.
As a check for you, the Profit after Tax for the budget year should equal $67,524, the closing balance of Accounts Receivable should be $951,766, the closing balance of Raw Materials Inventory should be $38,465, and the cash on hand at the end of the budget year should be $46.074.
Part 2
Read Part II of the case carefully. Using the Master Budget you created for Part I of the project, answer the following questions: