2. You are required to design and write the data for the Data Section so that you can then generate the formulas necessary to complete the Calculation and Report sections. There should be no formulas in the data section (refer to lecture slides as to the reasons).

3. You are required to write the Documentation Section (Click on the Doc tab see Figure 2). Refer to the lecture slides on what is needed for the documentation section. Note: you will be marked also to the clarity and tidyiness of your design and layout in the Data and Documentation Section.

You are required to prepare a Budgeted Statement of Comprehensive Income for the year 20xx for a local community hospital. To do this, download the Assignment excel file from Moodle (see Figure 1). *** Financial Modelling Assignment *** Excel Assignment 578 1KB PDF document Excel Assignment Model L:5.5KB Excel 2007 spreadsheet Excel Assignment Figure 1: Moodle Assignment (Week 4) Assignment Requirements 1. You are required to fill in the formulas in the yellow highligted cells in the Report Section and the Calculation Section (see Figure 2). Do not change the structure or format of the Report and Calculation sections. You can only enter formulas in the yellow cells as the rest of the cells in this two tabs are password protected. All the formulas must follow the two design principles; formula flexibility and formula efficiency. All formulas must be able to be copied across. E Feb F Apr May 1 REPORT SECTION 1 CALCULATION SECTION 2 Projected patient admissions 2 Dw surgery Medical 5 Surgical 4 2 3 Budget 6 7 TOTAL Number of operations 10 No. ods occupied Jan 1 Feb 2 Mar 3 Month number: Revenge Bed Fees 10 Theatre Fees Medical Surgical Maternitynonces) Mary Caesarean TOTAL 94 5 8 ST Doc Data Calculation Report Dod Data Siculation Report Click on this tab Fill in the formula in this yellow highlighted cells Figure 2: Formulas in the yellow highlighted cells 2. You are required to design and write the data for the Data Section so that you can then generate the formulas necessary to complete the Calculation and Report sections. There should be no formulas in the data section (refer to lecture slides as to the reasons). 3. You are required to write the Documentation Section (Click on the Doc tab - see Figure 2). Refer to the lecture slides on what is needed for the documentation section. Note: you will be marked also to the clarity and tidyiness of your design and layout in the Data and Documentation Section. Information 400 1. Admission, average stay and maternity caesarean rate information The projected number of admissions for the year 20xx by patient type are: Day Surgery 600 Medical Surgical 900 Maternity 400 The distribution of these admissions over the 12 months is the same for each patient type: January 10% February 5% March 4% 12% May 8% June 6% July 7% August 8% September 7% October 8% November 10% December 15% April Factors varying with Patient Average Type Stay (days) Day Surgery 1 Medical Surgical 6 Maternity (non-caesarean) 3 Maternity (caesarean) On average a quarter of maternity patients will have a caesarean. 4 7 2. Revenue information Factors varying with Patient Require Operation Type operation Theatre Fees (per admission) Day Surgery Yes RM 450 Medical No 0 Surgical Yes RM 650 Maternity (non-caesarean) 0 Maternity (caesarean) Yes RM 550 Bed fees for each patient are RM325 per day, regardless of patient type. No 3. Expense information The following information on expenses is provided: . For the operating theatre, costs per operation are: Salaries RM 150.00 Medical Supplies RM 170.00 Laundry RM 12.65 Pharmacy RM 10.00 The following general costs are based on the number of beds occupied: Salaries RM 160.00 Medical Supplies RM 13.00 Laundry RM 10.35 Pharmacy RM 6.20 Administration cost is RM195 per patient. Maintenance costs are initially RM200 a month, and then increase by 0.5% each month. Power cossts are initially RM9 000 a month, and then increase by RM 50 each month. Staff training costs: it is important to have staff-up-to-date with the latest medical and surgical techniques and accordingly the hospital budget allows for RM 3 500 a month in training costs, with the exception that no staff member goes on courses during December and January. Catering costs per bed occupied are RM18.55. Other costs include such items as insurance, cleaning, printing, stationery and telephone, and the budget allows a total of RM 15 000 each month. You are required to prepare a Budgeted Statement of Comprehensive Income for the year 20xx for a local community hospital. To do this, download the Assignment excel file from Moodle (see Figure 1). *** Financial Modelling Assignment *** Excel Assignment 578 1KB PDF document Excel Assignment Model L:5.5KB Excel 2007 spreadsheet Excel Assignment Figure 1: Moodle Assignment (Week 4) Assignment Requirements 1. You are required to fill in the formulas in the yellow highligted cells in the Report Section and the Calculation Section (see Figure 2). Do not change the structure or format of the Report and Calculation sections. You can only enter formulas in the yellow cells as the rest of the cells in this two tabs are password protected. All the formulas must follow the two design principles; formula flexibility and formula efficiency. All formulas must be able to be copied across. E Feb F Apr May 1 REPORT SECTION 1 CALCULATION SECTION 2 Projected patient admissions 2 Dw surgery Medical 5 Surgical 4 2 3 Budget 6 7 TOTAL Number of operations 10 No. ods occupied Jan 1 Feb 2 Mar 3 Month number: Revenge Bed Fees 10 Theatre Fees Medical Surgical Maternitynonces) Mary Caesarean TOTAL 94 5 8 ST Doc Data Calculation Report Dod Data Siculation Report Click on this tab Fill in the formula in this yellow highlighted cells Figure 2: Formulas in the yellow highlighted cells 2. You are required to design and write the data for the Data Section so that you can then generate the formulas necessary to complete the Calculation and Report sections. There should be no formulas in the data section (refer to lecture slides as to the reasons). 3. You are required to write the Documentation Section (Click on the Doc tab - see Figure 2). Refer to the lecture slides on what is needed for the documentation section. Note: you will be marked also to the clarity and tidyiness of your design and layout in the Data and Documentation Section. Information 400 1. Admission, average stay and maternity caesarean rate information The projected number of admissions for the year 20xx by patient type are: Day Surgery 600 Medical Surgical 900 Maternity 400 The distribution of these admissions over the 12 months is the same for each patient type: January 10% February 5% March 4% 12% May 8% June 6% July 7% August 8% September 7% October 8% November 10% December 15% April Factors varying with Patient Average Type Stay (days) Day Surgery 1 Medical Surgical 6 Maternity (non-caesarean) 3 Maternity (caesarean) On average a quarter of maternity patients will have a caesarean. 4 7 2. Revenue information Factors varying with Patient Require Operation Type operation Theatre Fees (per admission) Day Surgery Yes RM 450 Medical No 0 Surgical Yes RM 650 Maternity (non-caesarean) 0 Maternity (caesarean) Yes RM 550 Bed fees for each patient are RM325 per day, regardless of patient type. No 3. Expense information The following information on expenses is provided: . For the operating theatre, costs per operation are: Salaries RM 150.00 Medical Supplies RM 170.00 Laundry RM 12.65 Pharmacy RM 10.00 The following general costs are based on the number of beds occupied: Salaries RM 160.00 Medical Supplies RM 13.00 Laundry RM 10.35 Pharmacy RM 6.20 Administration cost is RM195 per patient. Maintenance costs are initially RM200 a month, and then increase by 0.5% each month. Power cossts are initially RM9 000 a month, and then increase by RM 50 each month. Staff training costs: it is important to have staff-up-to-date with the latest medical and surgical techniques and accordingly the hospital budget allows for RM 3 500 a month in training costs, with the exception that no staff member goes on courses during December and January. Catering costs per bed occupied are RM18.55. Other costs include such items as insurance, cleaning, printing, stationery and telephone, and the budget allows a total of RM 15 000 each month