ASSIGNMENT - COMPLETION OF A WORKBOOK OF BUDGET SPREADSHEETS
Completing the Assignment
Entering Data
You will answer the assignment in the templates contained in the Excel file downloaded from Stream. As an Excel program will automatically mark your assignment, care must be taken in how you prepare your answer. If the following instructions are not followed, you may lose marks.
The data sheet is where you are given the input data. The other sheets contain the output of financial and numeric data. On the output sheets you should only use formulae as this creates a workbook which is truly flexible. (Refer to Budget Spreadsheet Completion below.)
It is important to realise that the program operates on named cells contained within the template. The names of these cells must not be altered or relocated. To avoid this DO NOT use the cut and paste function, or drag and drop cells from one location to another. It is also not advisable to insert or delete rows or columns. Copying, pasting, and deleting cell contents is acceptable.
Budget Spreadsheet Completion
Marks will be awarded for the functionality and flexibility of your spreadsheet. The aim of this assignment is to produce a workbook of spreadsheets in which any changes in financial and numeric data in the Data Sheet will result in an update of the whole workbook. Therefore you must link the output with the Data Sheet and you should not enter numbers directly into the output areas on the other worksheets.
To help you, certain cells have been shaded pink and other blue. Entries of numbers or names from the information given should be entered in pink cells. Entries of formulae should be made in blue cells. Not all blue or pink shaded cells require entries.
Required:
(i) On the Data Input Sheet, enter the given data which has not yet been entered.
(ii) On Sales, Purchases & NCA sheet, complete the revenue, cash collections, purchases, payment to suppliers and depreciation calculations for the quarter 1 October to 31December 2017.
(iii) On the Cash Flow Sheet, complete the Projected Cash Flow from Operations for each of the three months October, November and December and the totals for the quarter 1 October to 31 December 2017. Note some cash flow row have been named, but you will need to enter the remaining cash flows yourself.
(iv) On the Income Statement Sheet, complete the Projected Income Statement for the quarter ended 31 December 2017. NB A month by month Income Statement is not required. Note that some expenses have been identified but you need to enter the remaining expenses yourself.
(v) Check the flexibility of your workbook by changing the selling price to $9 and then type the resulting Net Profit and ending Cash Balance in the boxes provided on the Income Statement Sheet (remember to change the selling price back to the original before submitting your assignment).
Attachment:- Assignment Files.rar