CIS261: Advanced Microcomputer Applications – General Excel Assignment 4 MADRIGAL FEASTE

Posted by:Prof. Lexx Posted on:May 4,2016

As a student at USI and a member of the budget committee for the Madrigal Feaste, you have been given a partially completed workbook, MadrigalFeaste.xlsx, and asked to complete the project.  (Download and save the file as LastNameFirstNametudent_MadrigalFeaste.xlsx) (that’s YOUR  last and first name, not the words LastNameFirstName!). The Madrigal Feaste includes dinner and dancing. Your committee prepared a tentative budget outlining income and expenses. The primary sources of income are the University’s contributions and ticket prices. Expenses include the actual cost of the dinner, facilities, parking, and other costs at a luxurious hotel in the city. Your goal is to balance the income and expenses, decide on the most appropriate ticket price per student, and ensure your budget falls within the limitations you must work with.

Calculate the “Maximum number of Valet Parking Stalls.

Goal Seek

Currently the estimated budget has a deficit. The fastest way to try to reconcile the income and expenses is to use Goal Seek. The goal is to break even, that is, to have a zero balance. Your instinct is to adjust the ticket price per person to reach the goal.

  1. Use Goal Seek to achieve a $0 balance by changing the ticket price per person.

One-Variable Data Table

You believe that between 200 and 500 students will attend. Because the ticket revenue, chair setup, catering cost, and valet parking expenses are dependent on the number of students, you decide to create a one-variable data table to compare the budget effects (Total Income, Total Expenses and Balance) based on different numbers of students attending.

  1. Use increments of 20 to complete the series from 200 to 500 vertically in column E.
  2. Enter the appropriate references to the dependent variables in the correct location for a one-variable data table.
  3. Complete the one-variable data table, and then format the results with Accounting Number Format with two decimal places.
  4. Apply custom number formats to make the formula references appear as descriptive column headings. Format the headings and substitution values.

Two-Variable Data Table

The break-even point for the one-variable data table is identical to the current model because all other variables are held constant. You want to compare the balances of different combinations of attendees varying from 200 to 500 in increments of 20 and ticket prices varying from 50 to 100 in 10 dollar increments. This will require you to use a two-variable data table based on Balance..

  1. Use increments of 20 to complete the series from 200 to 500 vertically in column E.
  2. Use increments of 10 to complete the series from 50 to 100 horizontally in row 21.
  3. Enter the reference to the “based on” variable in the correct location for a two variable table

University of Southern Indiana

CIS261- Advanced Microcomputer Applications

General Excel Assignment 4


  1. Complete the two-variable data table, and then format the results with Accounting Number Format with two decimal places.
  2. Apply a fill color to the cells in the table that show a positive balance.
  3. Apply a custom number format to make the formula reference appear as a descriptive column heading. Format the headings and substitution values.


Scenario Manager

You negotiated different cost per meal and ballroom rental rates based on 500, 400, 300, or 200 attendees. You estimated tentative ticket prices per attendee. To help you decide the target number of attendees, you need to use Scenario Manager.

  1. Create four scenarios based on the following data and generate a summary report using the total revenue, total expenses and balance as the results:
Meal Ticket Ballroom
Attend Cost Price Rental
500 15.95 75 12500
400 17.95 85 12500
300 19.95 90 11995
200 22.95 95 11995
  1. Generate a scenario summary report using total revenue, total expenses, and balance as the results.
  2. Clean up the summary as discussed in the book.

Use Solver

You realize a perfect break-even point may be unrealistic, but if you maximize the ending balance, you will donate any remaining balance to charity. For this analysis, you will use Solver to keep the expenses constant while changing the number of attendees and ticket price per person.

  1. Maximize the Balance.
  2. Use the number of attendees and the ticket price per person as changing variable cells.
  3. Set a constraint for the number of attendees.
  4. Set constraints for the ticket price per person.
  5. Set an appropriate integer constraint.
  6. Set a constraint that ensures the valet parking expense is less than or equal to the number of valet parking spaces times the valet price per vehicle.
  7. Solve the problem, but keep the original values in the Budget worksheet.
  8. Generate the Answer Report and Name the Sheet “Solver Answer Report”.

Prof. Lexx

Overview I have a Bachelor of Arts, majoring in English and minoring in History. I have worked in various fields ranging from academic research to freelance translating to editing to customer support and data entry. These include editing my old university's newspaper as well as co-leading their creative writing team; serving as a junior member in the history department's research network; publishing music and film reviews for several different magazines and webzines; and translating papers and books for numerous researchers in various languages, including Russian, Spanish, and Romanian. I am also a skilled typist, with a rate of at least 70 words per minute, and have myself digitized dozens of books and essays for both private and commercial use. I have a very strong work ethic, and make sure to prioritize the task I am given so that it is completed as quickly as possible. I am organized and disciplined, ensuring a job done professionally and efficiently.

    I have a background of 8 years in writing profession and currently pursuing also as an editor and proofreader. I have a knack for writing and thus, it was obvious to enhance my skills and serve others. Currently joined ‘Member of Association of Professional Writers and Editors’ a

    Pro Alex